Menu
Possible Duplicate:
Parameterizing a SQL IN clause?
In SQL Server I'd like to do something to this effect...
DECLARE @Values varchar(1000) SET @Values = 'A, B, C' SELECT blah FROM foo WHERE myField IN (@Values) Is this possible or how to accomplish this?
473
Assigning multiple values to multiple variables If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database.
You can assign the same value to multiple variables by using = consecutively. This is useful, for example, when initializing multiple variables to the same value. It is also possible to assign another value into one after assigning the same value.
An array in Java is used to store multiple values in a single variable, instead of declaring separate variables for each value. Therefore, an array is a collection of fixed elements in what can be seen as a list. Each element in an array consists of the same data type and can be retrieved and used using its index.
SET @a := "20100630"; SELECT * FROM wordbase WHERE verified = @a; But it does not work when there are multiple values stored in a variable. SET @a := "'20100630', '20100701' "; SELECT * FROM wordbase WHERE verified in (@a);
You need a table variable:
declare @values table ( Value varchar(1000) ) insert into @values values ('A') insert into @values values ('B') insert into @values values ('C') select blah from foo where myField in (select value from @values) Ideally you shouldn't be splitting strings in T-SQL at all.
Barring that change, on older versions before SQL Server 2016, create a split function:
CREATE FUNCTION dbo.SplitStrings ( @List nvarchar(max), @Delimiter nvarchar(2) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( WITH x(x) AS ( SELECT CONVERT(xml, N'<root><i>' + REPLACE(@List, @Delimiter, N'</i><i>') + N'</i></root>') ) SELECT Item = LTRIM(RTRIM(i.i.value(N'.',N'nvarchar(max)'))) FROM x CROSS APPLY x.nodes(N'//root/i') AS i(i) ); GO Now you can say:
DECLARE @Values varchar(1000); SET @Values = 'A, B, C'; SELECT blah FROM dbo.foo INNER JOIN dbo.SplitStrings(@Values, ',') AS s ON s.Item = foo.myField; On SQL Server 2016 or above (or Azure SQL Database), it is much simpler and more efficient, however you do have to manually apply LTRIM() to take away any leading spaces:
DECLARE @Values varchar(1000) = 'A, B, C'; SELECT blah FROM dbo.foo INNER JOIN STRING_SPLIT(@Values, ',') AS s ON LTRIM(s.value) = foo.myField; 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
