Conversion failed when converting from a character string to uniqueidentifier.

Question

Created a stored procedure in SQL 9 (2005) and have since upgraded to SQL 10 (2008). Since then, the following stored procedure has stopped working and thrown up the above error:

ALTER PROCEDURE [dbo].[GetModifiedPages] 
    @vPortalUID   nvarchar(32) = ''
AS
BEGIN
    -- Convert GUID to UI
    DECLARE @nPortalUID AS uniqueidentifier
    SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)

The passed in param @vPortalUID contains: 2A66057D-F4E5-4E2B-B2F1-38C51A96D385. I execute the stored proc like this:

EXEC GetModifiedPages '2A66057D-F4E5-4E2B-B2F1-38C51A96D385'

It falls over. I have tried Convert aswell. Still no joy. Have also had the value going in with { } around it. I removed these programatically and manually as above.

If you are interested I am running the SP from an ASP Classic page, although that should not affect this as the above code was run using SSMS.

Thanks in advance for your help. James

Answer 1

this fails:

declare @vPortalUID                 nvarchar(32)
set @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
DECLARE @nPortalUID AS uniqueidentifier
    SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
print @nPortalUID

this works

declare @vPortalUID                 nvarchar(36)
set @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
DECLARE @nPortalUID AS uniqueidentifier
    SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
print @nPortalUID

the difference is nvarchar(36), your input parameter is too small!