1

I have a general question on designing a stored procedure in SQL Server:

I have a website and a user can do a search for something using up to say 30 attributes (meaning he may use all 30 criteria or he may use only 5).

How would you design a stored procedure for this? What would you keep in mind while designing this procedure?

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Infravision
  • 129
  • 1
  • 6

2 Answers2

1

You will likely need to look into dynamic sql.

Here is some valuable information i think: http://www.sommarskog.se/dynamic_sql.html

Essentially, you will build your sql queries dynamically, based on the user's criteria.

TheITGuy
  • 722
  • 4
  • 15
1

You could do something like this:

create procedure foo
@param1 int = null,
@param2 int = null,
@param3 int = null,
...

Then in your where clause you could do this:

where (@param1 = null or @param1 = somevalue)
    and (@param2 = null or @param2 = somevalue)
    ....

By having nullable parameters you can easily pass in any combination of parameters to your stored procedure. Just be careful to watch you execution plans and make sure that no parameter sniffing occurs.