1

Running into problems with a SQL Server 2008 stored procedure: I keep getting the following error.

Conversion failed when converting from a character string to uniqueidentifier.

Here's the stored procedure - I've chopped a lot of it out for testing

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE RateReview
 @ProfileKey                INT
,@ReviewKey                 NVARCHAR(36)
,@Rating                    BIT
,@Result                    NVARCHAR(16) OUTPUT
AS
BEGIN
SET NOCOUNT ON;

SET @ReviewKey = 'NotFound'

DECLARE @ReviewID           INT = 0
DECLARE @VisitorProfileID   INT = 0
DECLARE @ReviewRatingID     INT = 0
DECLARE @VotedUp            BIT = 0

DECLARE @Temp UNIQUEIDENTIFIER
SET @Temp = CONVERT(UNIQUEIDENTIFIER, @ReviewKey)

-- Commented code here

END
GO

I try to call this with the standard Management Studio "Execute" menu option, which given me this:

DECLARE @return_value int,
    @Result nvarchar(16)

EXEC    @return_value = [dbo].[maxi_content_RateReview]
    @ProfileKey = 1985118925,
    @ReviewKey = N'4D051C99-1D59-4BB0-BFB9-D26786B5C809',
    @Rating = 1,
    @Result = @Result OUTPUT

SELECT  @Result as N'@Result'

SELECT  'Return Value' = @return_value

GO

I've checked that the GUID is correct and tried with both CAST and CONVERT - always the same problem. Any ideas? It's driving me nuts!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben Pritchard
  • 231
  • 1
  • 4
  • 11
  • See this SO ANSWER http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server/1390129#1390129 – sll Oct 25 '11 at 16:11
  • 1
    This is not a dupe. The linked question and answers are about converting a character string without dashes such as `a89b1acd95016ae6b9c8aabb07da2010` to `uniqueidentifier` – Martin Smith Oct 25 '11 at 16:20

3 Answers3

5

Your code has this line

SET @ReviewKey = 'NotFound'

Then you try and cast that string to uniqueidentifier. If you delete that line it works!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

You 

SET @ReviewKey = 'NotFound'

Then try to 

SET @Temp = CONVERT(UNIQUEIDENTIFIER, @ReviewKey)

Which wont work as 'NotFound' isn't a GUID

Alex K.
  • 171,639
  • 30
  • 264
  • 288
0

Unless my eyeballs are deceiving me, your problem is on this line:

SET @ReviewKey = 'NotFound'

The string 'NotFound' cannot be converted to a GUID. It looks like your proc is overwriting whatever value is passed in with 'NotFound'.

MusiGenesis
  • 74,184
  • 40
  • 190
  • 334