Handling Database NULL in .Net while using SqlParameter (.Net 2.0)

If you want to set the NULL for database column while inserting or updating, normal “null” OR “DBNULL.value” is not suitable.

C# code:

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = null;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = DBNULL.value;


If you are using Sqlparameter for passing the parameter value to stored procedure, the above null types does not support. It will throw an error like “Parameter @MiddleName not specified.”.

C# code:

using System.Data.SqlTypes;

param[0] = new SqlParameter("@MiddleName", SqlDbType.VarChar , 50);
param[0].Value = SqlString.Null;

The above code will execute properly without any error.

The same way you have to use for other data types like
DataTime --> SqlDateTime.Null
int32 --> SqlInt32.Null, etc

Performance Difference between Parameterized and Non-Parameterized call to Stored Procedure

You can call the sql stored procedures either with parameter ( using SqlParameter) or normal sql string ( using EXEC dbo.spName). There will be significant performance difference between two.

Non – Parameterized Stored Procedure Call:
If you call the stored procedure using EXEC ( like normal sql query) with command type as TEXT, execution plan is not reused. SQL Server caches the execution plan for SP to reuse. If you used EXEC for calling SP, new execution plan will create for every different string of EXEC statement. It will not reuse the execution plan from the SQL Server cache. SQL Server normally doing the parsing, optimizing, compiling process while creating the new execution plan.


Parameterized Stored Procedure Call:

If you call Stored procedure with Parameters ( example: SqlParameter) with Command Type as Stored Procedure, execution plan of the SP is reused again instead of creating the new one with different parameters.

Conclusion:

Use Parameterized method of calling when you call the stored procedure instead of EXEC method. You can’t feel the performance when few call to stored procedure. You can feel this performance difference when more calls to stored procedure (10000 calls frequently).

OUTPUT Command in SQL Server 2005

The output parameter that can be used in stored procedures. This is about returning effected data on a table with a few feature in SQL Server 2005.
SQL Server 2000

A simple question for you. If you want to retrieve last inserted identity value what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

CREATE TABLE TempTable
(
ID INT IDENTITY(1 , 1)
, Code VARCHAR(25)
, Name VARCHAR(50)
, Salary Numeric(10 , 2)
)
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
VALUES ( 'A002' , 'Ricky' , 200 )

SELECT SCOPE_IDENTITY() AS LastInsertID

However, this will only valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

more...

Features in Microsoft SQL Server 2008 CTP

Microsoft is making a series of announcements at Tech*Ed related to SQL Server 2008 -- previously codenamed "Katmai". I've got some details on some of the new features including the MERGE statement, Table Valued Parameters, Change Data Capture and the Declarative Management Framework. There should also be a download of the June CTP available inside Connect.
more...

Other Links:
Four Pillar