Query Problem

Hi, I have a problem with the WHERE IN statement
The following statement works ok.
SELECT Id
FROM table1
WHERE (Id IN
('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
But this statement throws a error : Conversion failed when converting
from a character string to uniqueidentifier.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
SELECT Id
FROM table1
WHERE (Id IN (@.Id))
Anyone have any Ideas?
Thanks
Toby> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
The IN clause will treat @.Id as a single value. If the list size is fixed,
you can specify multiple parameters:
SELECT Id
FROM table1
WHERE (Id IN (@.Id1, @.Id2, @.Id3))
See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
solutions. You also have additional XML options in SQL 2005.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tobi" <toby.riley@.gmail.com> wrote in message
news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> Hi, I have a problem with the WHERE IN statement
> The following statement works ok.
> SELECT Id
> FROM table1
> WHERE (Id IN
> ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> But this statement throws a error : Conversion failed when converting
> from a character string to uniqueidentifier.
>
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id))
> Anyone have any Ideas?
> Thanks
> Toby
>|||Tobi
Another alternative to dan's suggestion would be to run the query using
sp_executesql which might be easier on your code if you need more than 3
parameters.
e.g.
declare @.Id varchar(4000)
set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
'''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
+'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
declare @.QueryStr as nVarchar(3000)
Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
exec sp_executesql @.querystr
"Dan Guzman" wrote:
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> The IN clause will treat @.Id as a single value. If the list size is fixed,
> you can specify multiple parameters:
> SELECT Id
> FROM table1
> WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> solutions. You also have additional XML options in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tobi" <toby.riley@.gmail.com> wrote in message
> news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > Hi, I have a problem with the WHERE IN statement
> >
> > The following statement works ok.
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN
> > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> >
> > But this statement throws a error : Conversion failed when converting
> > from a character string to uniqueidentifier.
> >
> >
> > declare @.Id varchar(4000)
> > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id))
> >
> > Anyone have any Ideas?
> >
> > Thanks
> >
> > Toby
> >
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:
> Tobi
> Another alternative to dan's suggestion would be to run the query using
> sp_executesql which might be easier on your code if you need more than 3
> parameters.
> e.g.
> declare @.Id varchar(4000)
> set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
>
> declare @.QueryStr as nVarchar(3000)
> Select @.QueryStr = 'Select * from table1 where id in (' + @.Id + ')'
> exec sp_executesql @.querystr
> "Dan Guzman" wrote:
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> >
> > The IN clause will treat @.Id as a single value. If the list size is fixed,
> > you can specify multiple parameters:
> >
> > SELECT Id
> > FROM table1
> > WHERE (Id IN (@.Id1, @.Id2, @.Id3))
> >
> > See http://www.sommarskog.se/arrays-in-sql.html for a discussion of various
> > solutions. You also have additional XML options in SQL 2005.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Tobi" <toby.riley@.gmail.com> wrote in message
> > news:1154771947.162803.55670@.p79g2000cwp.googlegroups.com...
> > > Hi, I have a problem with the WHERE IN statement
> > >
> > > The following statement works ok.
> > >
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN
> > > ('{23ABFD83-0A00-40D2-8E1F-333055062862}','{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}','{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'))
> > >
> > > But this statement throws a error : Conversion failed when converting
> > > from a character string to uniqueidentifier.
> > >
> > >
> > > declare @.Id varchar(4000)
> > > set @.Id = '''{23ABFD83-0A00-40D2-8E1F-333055062862}''' + ',' +
> > > '''{B5F98C5E-F899-4EEC-BA75-AF6DFC6773FB}'''+ ','
> > > +'''{0A134F1C-3E50-4859-B36F-CC56CFF095A7}'''
> > > SELECT Id
> > > FROM table1
> > > WHERE (Id IN (@.Id))
> > >
> > > Anyone have any Ideas?
> > >
> > > Thanks
> > >
> > > Toby
> > >
> >
> >
> >