Passing Multiple Values into a Variable

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:

Passing multiple values into a variable