Tuesday 30 June 2020

SQL Server - MSSQL - Create table if it doesn't exist

Watch this example on YouTube




if OBJECT_ID(N'Test3', N'U') Is Null
Begin
  CREATE TABLE Test3(
    ID [int] Identity(1,1) Not Null,
    SomeName [varchar](10) null
  )
End

SQL Server - check if table exists

View this example on YouTube



IF OBJECT_ID(N'Test1', N'U') IS NOT NULL
Select 'Exists' Else Select 'Does not exist'

Wednesday 18 March 2020

MSSQL - Fix Error - Incorrect syntax near the keyword 'Tran'

Watch this example on YouTube




To fix it replace

ALTER PROCEDURE spTest
    @Name Varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN Tran
        IF @Name is Null
        BEGIN
            Rollback
            Return
        END
        Select * FROM Test1 Where Name = @Name;
    END Tran
   
END
GO

With

ALTER PROCEDURE spTest
    @Name Varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN Tran
        IF @Name is Null
        BEGIN
            Rollback
            Return
        END
        Select * FROM Test1 Where Name = @Name;
    COMMIT Tran
   
END
GO


MSSQL - Fix Error - Operand data type bit is invalid for sum operator.


Watch this example on YouTube





To fix it replace

Select SUM(IsValid) as NumberOfValidRecords from Test1

with

Select SUM(Cast (IsValid) as NumberOfValidRecords from Test1

MSSQL - Fix Error - Argument data type datetime is invalid for argument 2 of dateadd function.

Watch this example on YouTube



1. To Fix it replace
select DATEADD(DAY, GETDATE(), -1)

with

select DATEADD(DAY, -1, GETDATE())

MSSQL - Delete all stored Procedures in table at once in seconds


Watch this example on YouTube








1. To delete all tables in db - in object explorer click desired database and press F7 (or Function Key + F7)<br />
<br />
2. This will open Object Explorer Details - here you can select all tables right click and delete them

MSSQL - Delete all tables in database in seconds

Watch this example on YouTube



1. To delete all tables in db - in object explorer click desired database and press F7 (or Function Key + F7)

2. This will open Object Explorer Details - here you can select all tables right click and delete them

MSSQL - Fix Error - The file MDF is compressed but does not reside in a read-only database or filegroup. The file must be decompressed

Watch solution on YouTube


To fix it
1. Right click the folder where database files are being saved (for instance C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS01\MSSQL\DATA)
2. Go to properties
3. Click Advanced
4. Uncheck "compress contents to save disk space"