Stored Proc Help Part Duex...

I am getting the following error, can anyone help?

Msg 8169, Level 16, State 2, Procedure spUpdateSiteApps, Line 31
Conversion failed when converting from a character string to uniqueidentifier.

code to follow:


Stored Proc:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[spUpdateSiteApps]
 
@appID Int,
@appSites NVarchar(max)
 
AS
 
DECLARE @pos Int, @previous_pos Int, @value	UniqueIdentifier
SET @pos = 1;
SET @previous_pos = 0;
 
BEGIN
 
	SET NOCOUNT ON;
 
	DELETE FROM MainSiteApplications WHERE appID = @appID;
 
	WHILE @pos > 0
		BEGIN
			SET @pos = CHARINDEX(', ', @appSites, @previous_pos + 1)
			IF @pos > 0
 				BEGIN
					SET @value = CAST(SUBSTRING(@appSites, @previous_pos + 1, @pos - @previous_pos - 1) AS UniqueIdentifier)
					INSERT INTO MainSiteApplications (appID, siteID) VALUES (@appID, @value)
					SET @previous_pos = @pos
				END
			END
			IF @previous_pos < LEN(@appSites)
				BEGIN
					SET @value = CAST(SUBSTRING(@appSites, @previous_pos + 1, LEN(@appSites)) AS UniqueIdentifier)
					INSERT INTO MainSiteApplications (appID, siteID) VALUES (@appID, @value)
				END
			END
 
 
Executing:
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[spUpdateSiteApps]
		@appID = 6,
		@appSites = N'82BB6C2B-F810-4A9A-B31F-2470A8CC823B, BC737286-1468-4744-B0CB-89421744E64D'
 
SELECT	'Return Value' = @return_value
 
GO

Open in new window

LVL 25
kevp75Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
@appSites = N'82BB6C2B-F810-4A9A-B31F-2470A8CC823B,BC737286-1468-4744-B0CB-89421744E64D'  ---- remove the space after that comma
0
 
openshacCommented:
What do you get if you do this:

SELECT @value

Open in new window

0
 
kevp75Author Commented:
same error...  though running it in SQLSMSE shows one of the values for a split second then goes to the error
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[spUpdateSiteApps]
 
@appID Int,
@appSites NVarchar(max)
 
AS
 
DECLARE @pos Int, @previous_pos Int, @value	UniqueIdentifier
SET @pos = 1;
SET @previous_pos = 0;
 
BEGIN
 
	SET NOCOUNT ON;
 
	DELETE FROM MainSiteApplications WHERE appID = @appID;
 
	WHILE @pos > 0
		BEGIN
			SET @pos = CHARINDEX(', ', @appSites, @previous_pos + 1)
			IF @pos > 0
 				BEGIN
					SET @value = CAST(SUBSTRING(@appSites, @previous_pos + 1, @pos - @previous_pos - 1) AS UniqueIdentifier)
					--INSERT INTO MainSiteApplications (appID, siteID) VALUES (@appID, @value)
SELECT @value
					SET @previous_pos = @pos
				END
			END
			IF @previous_pos < LEN(@appSites)
				BEGIN
					SET @value = CAST(SUBSTRING(@appSites, @previous_pos + 1, LEN(@appSites)) AS UniqueIdentifier)
					--INSERT INTO MainSiteApplications (appID, siteID) VALUES (@appID, @value)
SELECT @value
				END
			END

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
kevp75Author Commented:
@aneeshattingal

i'll give is a shot
0
 
kevp75Author Commented:
that did it aneeshattingal

so now tell me this, what would be more efficient...  keeping CAST, or changing it to CONVERT?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Both has the same performance, but convert got more options which are mainly useful in case of formating dates and numerics, but in this case  no difference at all
0
 
kevp75Author Commented:
cool...  thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.