Stored Procedures with Optional Parameters in MS SQL Server 


CREATE PROCEDURE dbo.SearchClients
(
  @ClientFirstName varchar(20) = null, 
  @ClientLastName varchar(20) = null,
  @ClientHasTattoo bit = null,
  @JobFinished bit = null,
  @JobTypeID smallint = null,
  @ArtistID Smallint = null,
  @JobTotalHoursMinimum float = null,
  @JobTotalHoursMaximum float = null,
  @AdvertisingID smallint = null,
  @ClientDOBMinimum datetime = null,
  @ClientDOBMaximum datetime = null,
  @ClientStreetAddress varchar(75) = null,
  @ClientCity varchar(20) = null,
  @ClientState varchar(2) = null,
  @ClientZip varchar(10) = null,
  @ClientHomePhone varchar(14) = null,
  @ClientWorkPhone varchar(14) = null,
  @ClientEmail varchar(50) = null,
  @ClientSex bit = null,
  @JobDateMinimum datetime = null,
  @JobDateMaximum datetime = null,
  @JobTitle varchar(50) = null
)
AS 
IF @JobFinished IS NULL AND 
   @JobTypeID IS NULL AND
   @ArtistID IS NULL AND 
   @JobTotalHoursMaximum IS NULL AND 
   @JobTotalHoursMinimum IS NULL AND 
   @JobDateMinimum IS NULL AND 
   @JobDateMaximum IS NULL AND
   @JobTitle IS NULL
BEGIN 
  SELECT DISTINCT 
    ClientID, ClientFirstName, ClientLastName, ClientStreetAddress, 
    ClientCity, ClientState, ClientZip, ClientHomePhone, 
    ClientWorkPhone, ClientEmail, ClientDOB, ClientSex, ClientHasTattoo, AdvertisingID
  FROM         Clients
  WHERE     
    (ClientFirstName LIKE ISNULL(@ClientFirstName, ClientFirstName) + '%') AND 
    (ClientLastName LIKE ISNULL(@ClientLastName, ClientLastName) + '%') AND 
    (ClientHasTattoo = ISNULL(@ClientHasTattoo, ClientHasTattoo)) AND 
    (AdvertisingID = ISNULL(@AdvertisingID, AdvertisingID)) AND 
    (ClientDOB >= ISNULL(@ClientDOBMinimum, ClientDOB)) AND 
    (ClientDOB <= ISNULL(@ClientDOBMaximum, ClientDOB)) AND 
    (ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, ClientStreetAddress) + '%') AND 
    (ClientCity LIKE ISNULL(@ClientCity, ClientCity) + '%') AND 
    (ClientState LIKE ISNULL(@ClientState, ClientState) + '%') AND   
    (ClientZip LIKE ISNULL(@ClientZip, ClientZip) + '%') AND 
    (ClientHomePhone LIKE ISNULL(@ClientHomePhone, ClientHomePhone) + '%') AND 
    (ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, ClientWorkPhone) + '%') AND 
    (ClientEmail LIKE ISNULL(@ClientEmail, ClientEmail) + '%') AND 
    (ClientSex = ISNULL(@ClientSex, ClientSex))
  ORDER BY ClientID, ClientLastName, ClientFirstName
END
ELSE
BEGIN
  SELECT DISTINCT 
    Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName, 
    Clients.ClientStreetAddress, Clients.ClientCity, 
    Clients.ClientState, Clients.ClientZip, Clients.ClientHomePhone, 
    Clients.ClientWorkPhone, Clients.ClientEmail, 
    Clients.ClientDOB, Clients.ClientSex, 
    Clients.ClientHasTattoo, Clients.AdvertisingID
  FROM         Job RIGHT OUTER JOIN
                      Clients ON Job.ClientID = Clients.ClientID
  WHERE     
    (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + '%') AND 
    (Clients.ClientLastName LIKE ISNULL(@ClientLastName, Clients.ClientLastName) + '%') AND 
    (Clients.ClientHasTattoo = ISNULL(@ClientHasTattoo, Clients.ClientHasTattoo)) AND 
    (Job.JobFinished = ISNULL(@JobFinished, Job.JobFinished)) AND
    (Job.ArtistID = ISNULL(@ArtistID, Job.ArtistID)) AND 
    (Job.JobTotalHours >= ISNULL(@JobTotalHoursMinimum, Job.JobTotalHours)) AND 
    (Job.JobTotalHours <= ISNULL(@JobTotalHoursMaximum, Job.JobTotalHours)) AND 
    (Job.JobTypeID = ISNULL(@JobTypeID, Job.JobTypeID)) AND 
    (Clients.AdvertisingID = ISNULL(@AdvertisingID, Clients.AdvertisingID)) AND 
    (Clients.ClientDOB >= ISNULL(@ClientDOBMinimum, Clients.ClientDOB)) AND 
    (Clients.ClientDOB <= ISNULL(@ClientDOBMaximum, Clients.ClientDOB)) AND 
    (Clients.ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, 
             Clients.ClientStreetAddress) + '%') AND 
    (Clients.ClientCity LIKE ISNULL(@ClientCity, Clients.ClientCity) + '%') AND
    (Clients.ClientState LIKE ISNULL(@ClientState, Clients.ClientState) + '%') AND 
    (Clients.ClientZip LIKE ISNULL(@ClientZip, Clients.ClientZip) + '%') AND
    (Clients.ClientHomePhone LIKE ISNULL(@ClientHomePhone, Clients.ClientHomePhone) + '%') AND
    (Clients.ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, Clients.ClientWorkPhone) + '%') AND
    (Clients.ClientEmail LIKE ISNULL(@ClientEmail, Clients.ClientEmail) + '%') AND 
    (Clients.ClientSex = ISNULL(@ClientSex, Clients.ClientSex)) AND
    (Job.JobDate >= ISNULL(@JobDateMinimum, Job.JobDate)) AND
    (Job.JobDate <= ISNULL(@JobDateMaximum, Job.JobDate)) AND 
    (Job.JobTitle LIKE ISNULL(@JobTitle, Job.JobTitle) + '%')
  ORDER BY Clients.ClientID, Clients.ClientLastName, Clients.ClientFirstName
END






























Location:
Nashik, Maharashtra, India
















Comments











Post a Comment



















Popular posts from this blog









GROUP BY, CUBE, ROLLUP and SQL SERVER 2005




















Image







   I learn about CUBE and ROLLUP function today and I found it really  usefull for generating reports. Let me give you one example where we can  use ROLLUP and CUBE. Lets say you are developing the E-Commerce  application and administrator wants the report which shows products  purchased by all user group by product and buyer. You will say that’s  really easy and can write the query as shown below,       1:  SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem)    2:  FROM Orders GROUP BY CustomerName,CustomerName Fig – (1) Group By clause.       Which will returns the result as shown below,   Fig – (2) Result of GROUP BY clause      However what if you want result as display below,    Fig – (3) Desire result         Here ROLLUP and CUBE comes into the picture and help us. The  above result is generated using ROLLUP.  ROLLUP adds new row for each  column used in GROUP BY clause. First have a look ...








Read more










How to get content of Ckeditor & Fckeditor using Javascript





















  CKEDITOR:  To get content of ckeditor we will create object of ckeditor and then we  will provide id of textarea that is replaced by ckeditor.  Syntax:  CKEDITOR.instances.textarea_id.getData();getData();  Where CKEDITOR.instances   :  gives all the instances of CKEDITOR;  textarea_id   : it is ID of textarea that is containing data ;  getData()       : return content of Ckeditor current instance.  FCKEDITOR:  To get content of Fckeditor we will create Instance of Fckeditor for textarea and then we will get data using GetHTML method.  Syntax :  FCKeditorAPI.GetInstance('textarea_id'). GetHTML();  Where textarea_id     :  it is ID of textarea that is containing data ;  GetHTML()             :  This function returns content of textarea with id textarea_id.    








Read more










How to Fix Error- Sys.WebForms.PageRequestManagerTimeoutException - The server request timed out





















 To  solve this problem we can increase the timeout. You can change the  timeout time by adding a new property to the script manager control.  This property is called AsyncPostBackTimeOut  and it can help us to define the number of seconds before the request will throw a request timeout exception*.  For example if you want that the timeout will take maximum 10 minutes your code should be look like this: < asp : ScriptManager  ID ="ScriptManager1"  runat ="server"  AsyncPostBackTimeOut ="600"  > </ asp : ScriptManager >        * The default value of the AsyncPostBackTimeOut  property is 90 seconds.








Read more