Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ninel
Posting Yak Master
141 Posts |
 Posted - 2005-03-07 : 23:28:18
|
| I need to write a stored proceed that has 15 parameters that returns a recordset. Any one of these parameters may contain values. EX: @Lname = ''@Phone = '1234567890'@Fname = 'JANE'@City = 'LA'@State = ''The main part of the proc is a dynamically created SELECT statement where the parameters are used in the WHERE clause. EX: @SQL = 'SELECT * FROM Table WHERE '. Only parameters with values must be included in the WHERE clause. And any parameter after the first one should have 'AND'. So the query should look like this:@SQL = 'SELECT * FROM Table WHERE ' @SQL = @SQL + ' phone = ' + @phone@SQL = @SQL + ' AND Fname = ' + @FnameHow can I figure out which is the first parameter that contains a value so not to include an AND condition and then add the AND for the rest of the parameters?Thanks,Ninel |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-03-07 : 23:53:23
|
Take a look at the following paper. It shows a method that is generally regarded to perform better than executing dynamicly built SQL strings.http://sqlteam.com/item.asp?ItemID=2077I like to code my WHEREs a little differently though. In your case:WHERE (@Phone IS NULL OR Phone = @Phone)AND (@Fname IS NULL OR Fname = @Fname)etc... |
 |
|
|
|
|
|
Dynamic query