T-SQL CSV Variable

[rw2003]

I have a stored procedure which requires a comman seperated list of variables. This list should then be passed into a Select statement so it looks like this:
@var VarChar(35)

SELECT x FROM z WHERE x IN (@var)

But whenever this is run, I get a error converting the varchar to an int (the field x is an int).

Does anyone know either a way round this or a way to pass the required values through ???

Thanks in advance,

Richard

 

[clapag22]

Easy Way:

declare @sql varchar(255)

select @sql = 'SELECT x FROM z WHERE x IN (' + @var + ')'

exec(@sql)

There's another way to actually parse the string out, but this may work just fine for you. If you want to parse it, look up PATINDEX and/or CHARINDEX in BOL.

 

[Zukkster]

I assume you are trying to dynamically create the SQL statement by passing the variables to the Stored Procedure adding them into the statement and then executing the statement.

Someing like;

Set @strSQL = 'Select ' + @varW + ' From ' + @varZ + ' WHERE ' + @varX + ...

Execute (@strSQL)

If @varX is an integer value but you want to insert it into a SQL statement, you have to pass it to the stored procedure as text, because you can't make the SQL statement by by adding an integer into a string. You can only add combine two sting variables.

In addition if you were trying to do the same sort of thing but were adding a text field into the WHERE clause of the SQL statement you would have to do something like;

SET QUOTED_IDENTIFIER ON

Set @strSQL = "Select " + @varW + " FROM " + @varZ + " WHERE '" + @varX + "'..."

Execute (@strSQL)

SET QUOTED_IDENTIFIER OFF

This is because the SQL string still needs to have the ' mark around the text in the WHERE part of the statement to show that it is text.