Menu
Let say I got the following :
DECLARE @ExcludedList VARCHAR(MAX) SET @ExcludedList = 3 + ', ' + 4 + ' ,' + '22' SELECT * FROM A WHERE Id NOT IN (@ExcludedList) Error : Conversion failed when converting the varchar value ', ' to data type int.
I understand why the error is there but I don't know how to solve it...
953
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.
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
Variables declared before the GO statement are not accessible after the GO statement. Basically SSMS sends the first batch (i.e. Batch 1) of statements to the SQL Engine first, once its execution is over it sends the second batch of statements (i.e. Batch 2) after the GO statement to the SQL Engine for execution.
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.
This is an example where I use the table variable to list multiple values in an IN clause. The obvious reason is to be able to change the list of values only one place in a long procedure.
To make it even more dynamic and alowing user input, I suggest declaring a varchar variable for the input, and then using a WHILE to loop trough the data in the variable and insert it into the table variable.
Replace @your_list, Your_table and the values with real stuff.
DECLARE @your_list TABLE (list varchar(25)) INSERT into @your_list VALUES ('value1'),('value2376') SELECT * FROM your_table WHERE your_column in ( select list from @your_list ) The select statement abowe will do the same as:
SELECT * FROM your_table WHERE your_column in ('value','value2376' ) 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
