I'm trying to pass a comma separated list of GUIDs to a stored procedure but keep getting the following error
Syntax error converting from a character string to uniqueidentifier.
My stored procedure looks like this
CREATE PROCEDURE stp_GetProductNames
@dotnet.itags.org.ManuID varchar(5000)
AS
SELECT DISTINCT A.ProductName
FROM Products As A
INNER JOIN Manfacuturers As B On (A.ManufacturerID = B.ManufacturerID)
WHERE A.ManufacturerID IN (@dotnet.itags.org.ManuID)
ORDER BY A.ProductName
The list of GUIDS is the @dotnet.itags.org.ManuID parameter.
The GUIDS just look like this ('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx')
Anyone know how to get around this error?Heehe... seems like it should work huh? But it doesn't... is sees your csv variable as a full string and not a list of items to look in. two ways to handle this, neither of which are very elegant. 1) dynamic SQL ... build it on the fly in the SP, putting the GUIDS where they need to go. 2) And this one is a bit more intense, create a temp table, loop through your parameter, break off each GUID one by one, and put it into the temp table. Then use the temp table in your query.
Option 2 is how we ened up doing it here.
Tg
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment