SQL SERVER - Creating A Stored Procedure for a Select statement with Optional Parameters

Meanwhile while I'm on bench (yep, mapabasketball man o sa trabaho suki ata ako dito hahaha.) I managed to entertain myself and do some self studying. I fancied having my own Class library for CRUD operations (Im still not jumping into the EF bandwagon as Im not sure if the project that hopefully I will be in will be using one. If ever that's the case then I will still have a treasure waiting for me if ever i'll be moved to another project). My research (Online Research) led to me learning interfaces to impose a blue print for the class



Interface ICrud(Of T)

Function Insert(obj As T) As Boolean

Function [Select](obj As T) As DataTable

Function Update(obj As T) As Boolean

Function Delete(obj As T) As Boolean

End Interface 


(got the idea from here)


then one thing led to the other and I decided to learn more about SQL Stored Procedures (SP). I was thinking of using them for fetching data or my select statement,delete, insert and update.

After finishing the SP for Insert, update and Delete I had my problems with my SP for Select (I used the Company Table for studying, c/o Humprey Cogay form our previous MVC Tutorial)


A. Flexibility of the Parameters (Optional Parameters)

B. Flexibility of Query (Less TSQL (If statements, Case) the better)


For Problem A what I did was :


USE [Testing]

GO

Create Procedure [dbo].[sp_S_CompanyRecord]

(

@ID int = null,

@CompanyName Varchar(100) = null,

@ContactNo Varchar(100)=null,

@ContactPerson Varchar(100)=null,

@Description Varchar(100) = null

)


For Problem B at first I thought of catching the null values with if and my code will look like this :

if @id = null

if @ContactNo= null

begin

if @ContactPerson = null

begin

if @Description= null

begin

select * from company;

end

end

end

and the if statement will nest again if the params are not null.


Then after several revisions/tinkering of my code I was able to produce a friendlier T-SQL

select * from testing.dbo.company

where id = isnull(@id,id)

and CompanyName = isnull(@CompanyName,CompanyName)

and contactno = isnull(@contactNo,ContactNo)

and ContactPerson = isnull(@ContactPerson,ContactPerson)

and Description= isnull(@Description,Description);

Explanation: field = isnull(@Param,field) tells you that if the the Parameter has a null value it will assign the field vale be used instead of parameter value.



The whole SP here:



Create Procedure [dbo].[sp_S_CompanyRecord]

(

@ID int = null,

@CompanyName Varchar(100) = null,

@ContactNo Varchar(100)=null,

@ContactPerson Varchar(100)=null,

@Description Varchar(100) = null

)

as

select * from testing.dbo.company

where id = isnull(@id,id)

and CompanyName = isnull(@CompanyName,CompanyName)

and contactno = isnull(@contactNo,ContactNo)

and ContactPerson = isnull(@ContactPerson,ContactPerson)

and Description= isnull(@Description,Description);



I will also try this trick for my SP on Update.#

Print | posted on Thursday, August 29, 2013 2:32 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)