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-BA7
5-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-B
A75-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:

> 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 variou
s
> 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...
>
>|||Excellent that'll work, thankyou.
t
Chris Hoare wrote:[vbcol=seagreen]
> 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:
>

Query Optimization

IS there any way to rewrite this Query in optimized way?

SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))

Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.

SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)

also try

SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)