Passing a list of guid values to stored procedure

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 2005 Forums

.NET Inside SQL Server (2005)

Passing a list of guid values to stored procedure

Author

Topic

chinna.k09
Starting Member

3 Posts

Posted - 2010-09-24 : 14:56:33

Hi iam passing a list of guid values as comma seperated strings from my code behind but iam getting the error

Error converting passed string to guid

The stored procedure i am using bascially splits the string got as a parameter and converts that to guid and stores in table

@productlist is the list of all the guid values that i am passing

USE [AIA_Licensing]
GO

/****** Object: StoredProcedure [dbo].[sp_GetTemplatesforProducts] Script Date: 09/24/2010 12:47:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[sp_GetTemplatesforProducts]
(
@ProductList varchar(max)
)
AS
BEGIN
SET NOCOUNT ON
IF OBJECT_ID ('AIA_Licensing.dbo.Temp','U') IS NOT NULL
Drop Table dbo.Temp

CREATE TABLE Temp
(
ProductID uniqueidentifier
)

DECLARE @ProductID uniqueidentifier, @Pos int

SET @ProductList = LTRIM(RTRIM(@ProductList))+ ','
SET @Pos = CHARINDEX(',', @ProductList, 1)

IF REPLACE(@ProductList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @ProductID = LTRIM(RTRIM(LEFT(@ProductList, @Pos - 1)))
IF @ProductID <> ''
BEGIN
INSERT INTO Temp(ProductID) VALUES (CAST(@ProductID AS uniqueidentifier)) --Use Appropriate conversion
END
SET @ProductList = RIGHT(@ProductList, LEN(@ProductList) - @Pos)
SET @Pos = CHARINDEX(',', @ProductList, 1)

END
END

select pt.TemplateID, p.ProductID from
dbo.Products p
inner join Temp t
on t.ProductID = p.ProductID
inner join dbo.Products_Templates pt
on pt.ProductID = t.ProductID
group by p.ProductID, pt.TemplateID

Drop Table Temp
END

GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 15:24:20

want to show us what is in your parameter list?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

chinna.k09
Starting Member

3 Posts

Posted - 2010-09-24 : 15:46:53

The list will be '485983D4-1DFF-46E2-9433-3FFEDBC72D11, E4652DA8-1759-4C43-9DCA-1EB8DBB3AFF3'

These two are guid values ....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-24 : 15:49:22

See if this works for you:

ALTER PROC [dbo].[sp_GetTemplatesforProducts](@ProductList VARCHAR(MAX)) AS
BEGIN
SET NOCOUNT ON
SET @ProductList=REPLACE(REPLACE(@ProductList,',',''),' ','')

-- CTE portion just creates numbers for parsing GUID list
;WITH n(n) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
a(a) AS (SELECT 0 FROM n CROSS JOIN n x CROSS JOIN n y CROSS JOIN n z),
b(b) AS (SELECT 0 FROM a CROSS JOIN a x CROSS JOIN a y),
c(c) AS (SELECT (ROW_NUMBER() OVER (ORDER BY b)-1)*36+1 FROM b)

SELECT pt.TemplateID, p.ProductID 
FROM dbo.Products p
INNER JOIN dbo.Products_Templates pt ON pt.ProductID = t.ProductID
INNER JOIN c c ON CAST(SUBSTRING(@ProductList,c,36) AS UNIQUEIDENTIFIER)=p.ProductID
WHERE c.c<=LEN(@ProductList)
GROUP BY p.ProductID, pt.TemplateID

END

GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 15:50:56

[code]
DECLARE @gui uniqueidentifier
SET @gui = '485983D4-1DFF-46E2-9433-3FFEDBC72D11'
SET @gui = 'E4652DA8-1759-4C43-9DCA-1EB8DBB3AFF3'
[/code]

They appear valid

Put a print in your sproc and comment out the INSERT and post the values you get out

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

chinna.k09
Starting Member

3 Posts

Posted - 2010-09-25 : 09:57:51

Thanks guys i will check it out and post my output... thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources