Geeks With Blogs

News This would be my repository for my research on encountered programming problems/errors and you will probably find that some of my upcoming topics would be redundant / copied from other sites and most of the solutions/New topics is not made by me and will not say that its my idea. This blog will serve as my future reference if ever I stumble upon the same scenario again. Hopefully it will also help fellow programmers too.
Michael Alison Alviar

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.#

Posted on Thursday, August 29, 2013 2:32 PM SQL Server , T-SQL , Transact SQL , SQL FUNCTIONS , UDF | Back to top


Comments on this post: SQL SERVER - Creating A Stored Procedure for a Select statement with Optional Parameters

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © michaelalisonalviar | Powered by: GeeksWithBlogs.net | Join free