Menu
Lets say I have a SP that has a SELECT statements as follows,
SELECT product_id, product_price FROM product WHERE product_type IN ('AA','BB','CC'); But data goes to that IN clause must be through a single variable that contains the string of values. Something link below
SELECT product_id, product_price FROM product WHERE product_type IN (input_variables); But its not working that way. Any idea how to do this?
786
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
Solution 1. You can use parameters in a WITH clause just like in a traditional statement. The problem in the example is the IN operator which requires a list of values.
Pass parameter value like this - 'AA,BB,CC'. Then, it is enough to use FIND_IN_SET function -
SELECT product_id, product_price FROM product WHERE FIND_IN_SET(product_type, param); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
