Stored Proc Question

I'm building a WHERE clause that uses the IN keyword like:   IN (100, 104,  -45).    where the values are numeric.

I've declared a variable, like:  DECLARE @HOMEIDS varchar(50);

Then I set it like:  SET @HOMEIDS = '-100, -101';

I want in effect,  IN (@HOMEIDS), but doing this get me an error:  Error converting data type varchar to bigint.

How can I fix this?
LVL 1
HLRosenbergerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Variables are not supported in the IN clause.
If you want to use @HOMEIDS as a list of values, you might have to use a Split user defined function, or pass in a table variable to your query or use dynamic SQL. The approaches are discussed in http://sqlstudies.com/2013/04/08/how-do-i-use-a-variable-in-an-in-clause/
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't use it directly in the SQL Server. You'll need to build it first and store it in a varchar, then use Execute command to execute it. Something like:
Declare @MySelect VarChar(MAX)
 
SET @MySelect = ' SELECT * FROM TableName WHERE HomeID IN ('  + @HOMEIDS + ')'
 
EXECUTE (@MySelect)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
This is no possible, cause the list must be a literal. There are two approaches:

1) Using dynamic SQL.
2) Using a table variable

e.g.

DECLARE @HOMEIDS VARCHAR(50);
DECLARE @Sql VARCHAR(MAX);

SET @HOMEIDS = '-100, -101';
SET @Sql = '
	SELECT  TN.*
	FROM    tableName TN
	WHERE   TN.keyword IN ( @HOMEIDS );
';
SET @Sql = REPLACE(@Sql, '@HOMEIDS', @HOMEIDS);

EXECUTE (@Sql);

DECLARE @HOMEIDS TABLE ( ID INT );

INSERT  INTO @HOMEIDS
VALUES  ( 100 ) ,
        ( 104 ),
        ( -45 );

SELECT  *
FROM    tableName TN
        INNER JOIN @HOMEIDS H ON H.ID = TN.keyword;

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

MlandaTCommented:
@Vitor's dynamic SQL example works very well. You must however note though that dynamic SQL opens you up to SQL Injection. You must perhaps add some validation to make sure for example that there are no alphabet characters in the value supplied to this stored procedure. Something like
Declare @MySelect VarChar(MAX)
IF @HOMEIDS NOT LIKE '%[A-Z]%'
BEGIN
     SET @MySelect = ' SELECT * FROM TableName WHERE HomeID IN ('  + @HOMEIDS + ')'
      EXECUTE (@MySelect)
END

Open in new window

Scott PletcherSenior DBACommented:
Use a good text splitter to split the values list into single values, then join to those, like this:

SELECT ...
FROM table_name tn
INNER JOIN dbo.DelimitedSplit8K ( @HOMEIDS, ',' ) ds ON
    ds.item = tn.numeric_value
Deepak ChauhanSQL Server DBACommented:
Can you try like this.

where cast(ColumnName as varchar(100)) in (@HOMEIDS)
Preston CooperDatabase AdministratorCommented:
This article covers what you need.
http://www.sommarskog.se/arrays-in-sql-2008.html
HLRosenbergerAuthor Commented:
thanks to all!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.