Conversion failed when converting from a character string to uniqueidentifier

jamesmhaley picture jamesmhaley · Sep 25, 2009 · Viewed 106.1k times · Source

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

KM. picture KM. · Sep 25, 2009

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!