how to concatenate varying stored procedure parameters

Question

please help me with writing this search sql stored procedure procedure may have different number of parameters at different time so could any body help me with writing this query. I don't know how to concatenate parameters. i am new to stored procedure

CREATE PROCEDURE searchStudent
-- Add the parameters for the stored procedure here
@course int=null,
@branch int=null,
@admissionYear varchar(max)=null,
@passingYear varchar(max)=null,
@userName varchar(max)=null,
@sex varchar(max)=null,
@studyGap varchar(max)=null,
@firstName varchar(max)=null,
@lastName varchar(max)=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE query STR DEFAULT null

IF @course IS NOT NULL
THEN query=
SELECT * FROM [tbl_students] WHERE 

END
GO

please complete the query so that it can have parameters which are having values and can search from database on the basis of parameters value. But parameter may vary every time depends on search criteria.

Answer 1

Sorry, I am having trouble understanding what you are asking. Do you mean the consumer of the sproc may specify some arbitrary subset of the parameters and you want to filter on those?

Assuming the above you have 2 options.

1. use a where clause something like this:

WHERE ([tbl_students].firstName = ISNULL(@firstname,firstName)
  AND ([tbl_students].lastName = ISNULL(@lastName ,lastName )

etc. What this does is check if your parameter has a value, and, if so, it will compare it to the column. If the param is null, then it will compare the column to itself, which will never filter anything out.

1. use dynamic sql in your sproc and just include the line of the where clause you want if the param is not null.

Answer 2

You would probably need to use Dynamic SQL to achieve this. First of all I would highly recommend reading this excellent article. http://www.sommarskog.se/dynamic_sql.html

You're dynamic sql would be something like this;

   Declare @query varchar(max)

   Set @query = 'Select * From dbo.MyTable Where '

   If @Course Is Not Null
   Begin
    Set @query = @query + 'Course = ' + Convert(varchar(10), @Course)
   end

   If @Branch Is Not Null
   Begin
    Set @query = @query + ' and Branch = ' + Convert(varchar(10), @Branch )
   end

This is only an example! You will need to build in some checks to ensure that you have one (and only one) Where clause, you must ensure that the integer values are converted to string values correctly. You must also check that the parameters don't have any special characters that could break the dynamic sql - like an apostrophe (')

Using dynamic SQL can be painful and very difficult to get right.

Good luck!

Answer 3

The key with a dynamic search conditions is to make sure an index is used, instead of how can I easily reuse code, eliminate duplications in a query, or try to do everything with the same query. Here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

It covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History