Problem in SQL Stored procedure. HELP!!!!!!

[Guest_imported]

Hi

I'm returning a string say STR1 from a table in a stored procedure

@STR1='AFG','ALB','IND'

when I query another table with IN clause. SQL looks like

SELECT * FROM DATA where IND0 in (@STR1)

It doesn't return anything. It works when the STR1 is just 'AFG'.

Is it a limitation?. or do I have to do differently.

Thanks

Sathya

 

[dbmsguy]

you might try declaring the string as:

select @str1 = "'val1','val2','val3'" Paul

http://www.dbmsguy.com

 

[Guest_imported]

Thank your very much for the immediate reply.

I tried it. But still it's not returning any records

Once again thank you very much for the tip.

Sathya

 

[tlbroadbent]

You CANNOT use a variable in the IN clause directly. You will need to create a SQL statement dynamically and then EXECUTE it.

Declare @sql nvarchar(2000)

Set @sql='SELECT * FROM DATA where IND0 IN (' + @STR1 + ')'
Exec @sql Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Guest_imported]

Is there is way to execute a prepared SQL string in a stored procedure.

something like this.

@SQL = "SELECT * FROM DATA WHERE IND0 in (" + STR1 + &quot"
EXEC SQL

Thanks

Sathya

 

[Guest_imported]

I tried it. It's giving invalid stored procedure 'SELECT * FROM DATA"

Thanks

Sathya

 

[dbmsguy]

it would be:

exec(@sql) Paul

http://www.dbmsguy.com

 

[tlbroadbent]

Paul,

Thanks. I mistyped the Exec line. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous