Need to execute a statement that performs an action validating against a list of values, but the list changes from one run to the other? Or perhaps the list is built dynamically within a stored procedure? Here's how:
DECLARE @ProdList as varchar(200) SET @ProdList = "'PROD1','PROD2','PROD3'" EXECUTE ('DELETE FROM tblProducts WHERE ProductID IN (' + @ProdList + ')')
Yuriy M. writes: Instead of using a dynamic SQL statement, you could apply a little trick with the CHARINDEX function, which is faster and more flexible. If you need to just execute a SELECT statement, you can use it inside a user defined function.
DECLARE @ProdList as varchar(200) SET @ProdList = "'PROD1','PROD2','PROD3'" DELETE FROM tblProducts WHERE CHARINDEX(',' + ProductID + ',', ',' + CASE WHEN @ProdList IS NOT NULL THEN @ProdList ELSE ProductID END + ',') <> 0
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.