In(@variable) clause and TABLE datatype variable
From: Aamir Ghanchi (AamirGhanchi_at_discussions.microsoft.com)
Date: 02/07/05
- Next message: MGFoster: "Re: Slow Query Issue In SP"
- Previous message: Aamir Ghanchi: "IN(@variable) clause and Table Data Type variable"
- Next in thread: David Portas: "Re: In(@variable) clause and TABLE datatype variable"
- Reply: David Portas: "Re: In(@variable) clause and TABLE datatype variable"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Feb 2005 14:33:01 -0800
Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comma
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @TableVariable TABLE ( col char(4) );
INSERT @TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @ListVariable varchar(100);
set @ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
>From @TableVariable as TableVariable
Where TableVariable.col IN (@ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @command varchar(2000)
set @command='
Select TableVariable.col
>From @TableVariable as TableVariable
Where TableVariable.col IN ('+@ListVariable+')
'
exec (@command)
--Then we get the error message:
-- Must declare the variable '@TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
- Next message: MGFoster: "Re: Slow Query Issue In SP"
- Previous message: Aamir Ghanchi: "IN(@variable) clause and Table Data Type variable"
- Next in thread: David Portas: "Re: In(@variable) clause and TABLE datatype variable"
- Reply: David Portas: "Re: In(@variable) clause and TABLE datatype variable"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
