22

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

jamesmhaley
  • 44,484
  • 11
  • 36
  • 49

1 Answers1

36

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!

Emma Thapa
  • 767
  • 1
  • 7
  • 14
KM.
  • 101,727
  • 34
  • 178
  • 212
  • 3
    +1 for the point that his parameter is too small, but it should be noted that because of that, his string is actually truncated. If you did a `print vPortalUID` before converting, you would see that `D385` didn't make the cut. – Eric Sep 25 '09 at 12:11
  • I don't know why I was doing that http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server :p .. I should have first cast the varchar +1 of course ! – Zerotoinfinity Jul 12 '13 at 13:12