UniqueIdentifier and the IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UniqueIdentifier and the IN clause

Author

Topic

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 17:20:14

I'm trying to pass a value to my stored procedure that contains a string of uniqueidentifiers separated by commas (e.g. {49651ADB-4EB6-44A8-A662-F2420FCA7695}, {FB87EE64-F477-4BA6-B949-BD6544A5D42A}) and then use that variable in my IN clause. See below.

SELECT DISTINCT
ttu.LastName + ', ' + ttu.FirstName AS TeacherName
,sch.SchoolName
,eth.EthnicityName
,grd.GradeDescription
FROM
TTUser AS ttu
INNER JOIN UserBelongs AS ub
ON ub.TTUserID = ttu.TTUserID
INNER JOIN School AS sch
ON sch.SchoolID = ub.SchoolID
INNER JOIN Grade AS grd
ON grd.GradeID = ub.GradeID
INNER JOIN Ethnicity AS eth
ON eth.EthnicityID = ttu.EthnicityID
WHERE
ub.SchoolID IN( @SchoolID )

The variable @SchoolID contains the string of unique identifiers so I can retrieve multiple schools. However, the query only returns the first value in the variable and ignores the rest, even though I know there are values for the second identifier.

If I hardcode the identifiers into the IN clause I get my results but that defeats the purpose of dynamic SQL.

Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-22 : 17:25:35

Take a look at Page47's solution:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20675

The article that the comments stemmed from:

http://www.sqlteam.com/item.asp?ItemID=11499

Tara

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 17:38:51

Thanks tduggan. However, I am using SQL Server 7 and don't think I can create functions in that version. I have SQL Server 2000 on my local box and have the ability to create functions but the database I'm going against for this example is on the network and is using version 7.

Thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-22 : 17:40:13

The SQL Team FAQ:

http://www.sqlteam.com/faq.asp

Has a link on how to use dynamic SQL to handle IN lists such as yours. It's under the fourth item, there are a couple of article links.

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 17:45:00

I had a feeling that if I wrote "dynamic sql" that I might mislead people. In my testing phase I'm not using dynamic SQL. Here's my full code used in QA.

declare @SchoolID varchar(500)
set @SchoolID = '{49651ADB-4EB6-44A8-A662-F2420FCA7695}' + ', ' + '{FB87EE64-F477-4BA6-B949-BD6544A5D42A}'

print @SchoolID
SELECT DISTINCT
ttu.LastName + ', ' + ttu.FirstName AS TeacherName
,sch.SchoolName
,eth.EthnicityName
,grd.GradeDescription
FROM
TTUser AS ttu
INNER JOIN UserBelongs AS ub
ON ub.TTUserID = ttu.TTUserID
INNER JOIN School AS sch
ON sch.SchoolID = ub.SchoolID
INNER JOIN Grade AS grd
ON grd.GradeID = ub.GradeID
INNER JOIN Ethnicity AS eth
ON eth.EthnicityID = ttu.EthnicityID
WHERE
ub.SchoolID IN( @SchoolID )

The PRINT statement produces: {49651ADB-4EB6-44A8-A662-F2420FCA7695}, {FB87EE64-F477-4BA6-B949-BD6544A5D42A}

This returns records matching the first GUID but does not see the second GUID.

Please disregard my "dynamic sql" comment. I appologize for the confusion.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-22 : 17:46:33

I guess you missed Page47's solution. His doesn't require a UDF.

Tara

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 17:52:24

Thanks. You're all too high level for me. I don't understand these solutions. I understand the CREATE Function solution, but not this other one. Thanks for trying.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-22 : 17:56:11

You don't have to understand it. Just join to it. Here's an example:



DECLARE @Sep char(1)
DECLARE @csv varchar(512)

SELECT @Sep = ','

SELECT *
FROM Table1 t
INNER JOIN
	(
		select nullif(substring(@sep+@csv+@sep,n,charindex(@sep,@sep+@csv+@sep,n)-n),'') as element      
		from dbo.Numbers      
		where 
			n <= datalength(@sep+@csv+@sep) and
			n-datalength(@sep)>0 and
			substring(@sep+@csv+@sep,n-datalength(@sep),datalength(@sep))=@sep and            
			charindex(@sep,@sep+@csv+@sep,n)-n>0
	) csv
ON t.Column3 = csv.element

So @csv would contain the comma separated list. Column3 would be the column that has the values. So the join part is the csv solution. Don't worry about what it does just yet. Just see if it works for you.

Tara

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 18:08:38

Ok, here's what I figured out. Between your last post and the CREATE FUNCTION idea I was able to get this to work. I realized I didn't need the ability to create a function to get this to work, I'd just use the code from the function inside my stored procedure.

Here's what I did. Thank you VERY much.

DECLARE @separator char(1)
DECLARE @SchoolID varchar(1000)
CREATE TABLE #IntTable (MySchoolID UNIQUEIDENTIFIER )

SET @SchoolID = '{49651ADB-4EB6-44A8-A662-F2420FCA7695}' + ',' + '{FB87EE64-F477-4BA6-B949-BD6544A5D42A}'

SET @separator = ','

DECLARE @separator_position INT
DECLARE @array_value UNIQUEIDENTIFIER

SET @SchoolID = @SchoolID + ','

WHILE patindex('%,%' , @SchoolID) <> 0
BEGIN

SELECT @separator_position = patindex('%,%' , @SchoolID)
SELECT @array_value = left(@SchoolID, @separator_position - 1)

INSERT #IntTable
VALUES ( @array_value )

SELECT @SchoolID = stuff(@SchoolID, 1, @separator_position, '')
END

SELECT DISTINCT
ttu.LastName + ', ' + ttu.FirstName AS TeacherName
,sch.SchoolName
,eth.EthnicityName
,grd.GradeDescription
FROM
TTUser AS ttu
INNER JOIN UserBelongs AS ub
ON ub.TTUserID = ttu.TTUserID
INNER JOIN School AS sch
ON sch.SchoolID = ub.SchoolID
INNER JOIN Grade AS grd
ON grd.GradeID = ub.GradeID
INNER JOIN Ethnicity AS eth
ON eth.EthnicityID = ttu.EthnicityID
INNer join #IntTable AS myTemp
ON myTemp.MySchoolID = ub.SchoolID

DROP TABLE #IntTable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-22 : 18:10:34

Using a WHILE loop will be slower than Page47's solution.

Tara

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 18:16:15

Ok, but then where is the variable that has the comma separated values in his example? I see he's joining to a subquery that calls the Numbers table but I don't get that. I'm not using a table. I'm using a variable.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-22 : 18:19:02

Check out my example. The variable @Csv would contain the comma separated list. So you would need the Numbers table as well. But that's a handy table to have around not only for the csv solution. Numbers table:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Numbers]
GO

CREATE TABLE [dbo].[Numbers] (
	[n] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Numbers] ADD 
	CONSTRAINT [PK_Numbers] PRIMARY KEY  CLUSTERED 
	(
		[n]
	)  ON [PRIMARY] 
GO

My numbers table has values 1-8000.

Tara

jdausten
Starting Member

7 Posts

Posted - 2004-11-22 : 18:19:25

Or I could just do this which is increadibly simplier and fast.

DECLARE @SchoolID varchar(1000)

SET @SchoolID = '{49651ADB-4EB6-44A8-A662-F2420FCA7695}' + ',' + '{FB87EE64-F477-4BA6-B949-BD6544A5D42A}'

SELECT DISTINCT
ttu.LastName + ', ' + ttu.FirstName AS TeacherName
,sch.SchoolName
,eth.EthnicityName
,grd.GradeDescription
FROM
TTUser AS ttu
INNER JOIN UserBelongs AS ub
ON ub.TTUserID = ttu.TTUserID
INNER JOIN School AS sch
ON sch.SchoolID = ub.SchoolID
INNER JOIN Grade AS grd
ON grd.GradeID = ub.GradeID
INNER JOIN Ethnicity AS eth
ON eth.EthnicityID = ttu.EthnicityID
WHERE
Charindex(cast(ub.SchoolID as varchar(1000)),@SchoolID)>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources