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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-17 : 07:29:04
|
| Thai writes "I'm green to stored proc and I'm stuck at this: I have many criteria for users to search for a record, depending on how many criteria they check. E.g.: If they check on By ID, then search by ID, but if they also check by Date and by Type then the SP has to be changed. Please help me how to create such a dynamic SP.Thanks a lot." |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-17 : 09:03:51
|
| You have a fairly open-ended question, and with a little more information we could give you a better answer...One way is to use dynamic sql, which is definitely not recommended for speed and security reasons unless you have no other choice. The other one is to build the conditions by combining logical operators appropriately. For example:CREATE PROCEDURE SearchCustomer @CustomerID INT, @SearchID BIT, @CustomerCity INT, @SearchCity BITASBEGIN SELECT * FROM Customers WHERE (@SearchID = 1 AND CustomerID = @CustomerID)AND (@SearchCity = 1 AND CustomerCity = @CustomerCity)ENDyou would call the proc like this:--Search by IDEXEC SearchCustomer 1000, 1, 0, 0--Search by CityEXEC SearchCustomer 0, 0, 85, 1Hope this helpsOwais |
 |
|
|
|
|
|
|
|
Multi-criteria search using stored procedure