Passing multiple values into a variable is a little more difficult than it should be. In other languages you can use functions such as Lists or Arrays, but SQL makes it a bit more complicated. To show you what I mean, let’s look at an example.
First, let’s create a Demo table:
CREATE TABLE [dbo].[Demo]( [ID] [int] NULL, [Name] [varchar](50) NULL, [Address] [varchar](50) NULL, [State] [varchar](50) NULL, [Zip] [int] NULL )
Next, populate it with some data:
INSERT INTO [dbo].[Demo] VALUES (1, 'Brady', '123 Main Street', 'TN', 12345)
INSERT INTO [dbo].[Demo] VALUES (2, 'Tommy', '124 Main Street', 'TN', 12345)
INSERT INTO [dbo].[Demo] VALUES (3, 'Jonny', '125 Main Street', 'TN', 12345)
Now that we have some data, let’s try a query using variables. I want to define a variable on the column ID.
DECLARE @MultipleValue varchar(200) SET @MultipleValue = '1,2' SELECT * FROM Demo WHERE ID IN (@MultipleValue)
After running this query, I get 0 results and an error:
Msg 245, Level 16, State 1, Line 24
Conversion failed when converting the varchar value ‘1,2’ to data type int.
Why? I know the ID’s 1 and 2 are in the table, but SQL is looking at this variable as one string. So unless I have 1,2 in the same ID column, it will show 0 results.
One way to get around this is to use a UDF, user defined function. In this function, we’re going to convert the comma separated values (1,2) into a table, then query from that.
CREATE FUNCTION [dbo].[MultipleValues] (@InStr VARCHAR(MAX))
RETURNS @TempTable TABLE
(id int not null)
AS
BEGIN
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTable(id) VALUES (@VALUE)
END
RETURN
END
GO
Now that we have a UDF, let’s use this in the query:
DECLARE @MultipleValue varchar(200) SET @MultipleValue = '1,2' SELECT * FROM Demo WHERE ID IN (SELECT * FROM dbo.MultipleValues(@MultipleValue))
Ta da!
We now have two results. ID 1 and 2:
