Re: In(@variable) clause and TABLE datatype variable
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 02/07/05
- Next message: Wally: "Re: JOINing the same table"
- Previous message: David Portas: "Re: IN(@variable) clause and Table Data Type variable"
- In reply to: Aamir Ghanchi: "In(@variable) clause and TABLE datatype variable"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Feb 2005 22:58:11 -0000
http://www.sommarskog.se/arrays-in-sql.html
--
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@discussions.microsoft.com> wrote in message
news:D82370C5-6E5E-4178-98B8-0017FE88E809@microsoft.com...
> 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: Wally: "Re: JOINing the same table"
- Previous message: David Portas: "Re: IN(@variable) clause and Table Data Type variable"
- In reply to: Aamir Ghanchi: "In(@variable) clause and TABLE datatype variable"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
