SQL Server Performance Forum – Threads Archive

Dynamic Stored Procedures?

Hello, I’ve currently got an application that sends query criteria via .asp into a .dll file that dynamically generates an SQL statement based on that criteria. For example, if I send the GetTech function in the .dll a TechName, it will generage "SELECT * FROM [Tech] WHERE TechName = lTechName", but if you send it an ID number it will generate "SELECT * FROM [Tech] WHERE ID = lID". This is so I can use one function in the .dll to query a Tech based on any combination of search criteria.
Now… I’m trying to transfer the SQL statements from the .dll to stored procedures within MS SQL Server 7. But I’m not sure I can get a single procedure to create a dynamic WHERE statment. Is this possible? Thanks,
James

If you can pass the column name along with the value, then you can generate a dynamic sql within the stored procedure, that replaces the column name with the one being passed. Or you can have a stored procedure with multiple input variables and then have several IF clauses with several select statements so that a specific select statement will run when a specific variable is passed.

The 2 articles for your reference:
http://www.sqlteam.com/item.asp?ItemID=1487
http://www.4guysfromrolla.com/webtech/020600-1.shtml HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

http://www.sommarskog.se/dynamic_sql.html
–Frank
http://www.insidesql.de

Thanks guys. This was exactly what I needed.

]]>