asked on

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

openshac

What do you get if you do this:

SELECT @value

Open in new window

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kevp75

ASKER

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

kevp75

ASKER

@aneeshattingal

i'll give is a shot

kevp75

ASKER

that did it aneeshattingal

so now tell me this, what would be more efficient... keeping CAST, or changing it to CONVERT?

Aneesh

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

kevp75

ASKER

cool... thanks!