Bool/Bit - C#-TSQL(SQL Server)

When passing a parameter to a SQL Server stored procedure using C# you cannot pass 0 or 1 like you would manually calling an EXEC on he procedure.

I passed a parameter as a zero and it acted like I had passed a 1 or true. I switched it to false and it ran as it was supposed to.

TSQL Drop Table If Exists

Using SQL Server I have needed to drop functions and stored procedures before creating them in a script. Today I had to drop a table. After searching around a bit I found that I could test if the drop was necessary using much the same syntax as the others.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
DROP TABLE [dbo].[TableName]

The type of the object is U for USER_TABLE.

Assigning Variables Values in SQL Server TSQL

This trips me up every now and again as I switch between coding SQL and coding other things.

The keyword SET is required before a variable assignment. I see to recall that BASIC use to have this requirement as well.

Example:
DECLARE @myInteger INT

SET @myInteger = 7

Using Autoincrement field Value After Insert

I had the need to insert one record into a SQL Server table that held a unique code and a description of what that code represents. That unique code was automatically generated by the database. Right after that insert I needed to insert several records in another table that used that newly inserted value from the previous table. I found two approaches to do this which I will share below.

Approach 1 - Use @@Identity
DECLARE @AutoIncCode int

INSERT INTO LookupTable (Description)
Values('DescriptionOfItem')

//The @@identity variable holds the last auto incremented value. Store this value or you will lose it after the next insert you do.
SET @@AutoIncCode = @@identity

INSERT INTO DependantTable
(Field1, Field2, LookupTableId)
Values('Val1', 'Val2', @AutoIncCode)

Approach 2 - Fetch from the table you just inserted to
DECLARE @AutoIncCode int

INSERT INTO LookupTable (Description)
Values('DescriptionOfItem')

SET @AutoIncCode = (SELECT LookupTableId
from LookupTable where Description = 'DescriptionOfItem')

INSERT INTO DependantTable
(Field1, Field2, LookupTableId)
Values('Val1', 'Val2', @AutoIncCode)