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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

openshacCommented:
What do you get if you do this:

SELECT @value

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
@appSites = N'82BB6C2B-F810-4A9A-B31F-2470A8CC823B,BC737286-1468-4744-B0CB-89421744E64D'  ---- remove the space after that comma
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.