Passing GUID as parm to Stored Procedure

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  May 22, 2006 at 8:10 am

  #113802

  I inherited support for an application that has a vendor-designed DB that uses GUIDs for virtually every PK. We needed a query for reporting purposes and the vendor built the query and sent it to us. I'm trying to convert the query to an SP so I can reference it from a Crystal Report and I'm having trouble with passing the GUID to the SP. Here's the vendor's query (abbreviated to make it easier):

  Select Fullname, Start_Date from tSearch where SearchGUID='BF33FA65-D558-43F5-0F0D20AB0028'

  My SP:

  Create Procedure dbo.usp_Search (@srch uniqueidentifier)

  AS

  BEGIN

     Select Fullname, Start_Date from tSearch where SearchGUID=@srch

  END

  GO

  When I run the procedure:

  DECLARE

  @return_value int

  EXEC @return_value = [dbo].[Candidate_Overview] @srch = 'BF33FA65-D558-43F5-0F0D20AB0028'

  SELECT

  'Return Value' = @return_value

  GO

  I get the following error:

  Msg 8114, Level 16, State 4, Procedure Candidate_Overview, Line 0

  Error converting data type varchar to uniqueidentifier.

  (1 row(s) affected)

  I tried specifying "@srch=[BF33FA65-D558-43F5-0F0D20AB0028]", I tried changing the SP Parm to Varchar and NVarchar. All yielded errors, different errors, but errors.

  Is there a standard way to pass a GUID as a parameter in a Stored Procedure? I can't find anything in BOL...

   
  
  

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  May 22, 2006 at 8:28 am

  #639284

  Is this a real live example?

  Select Fullname, Start_Date from tSearch where SearchGUID='BF33FA65-D558-43F5-0F0D20AB0028'

  If it is, then the guid is too short to be a guid.

  Try with something like 'BF33FA65-D558-43F5-ABD4-0F0D20AB0028' and see if it improves.

  (or some other 'real' value - a guid should be 8-4-4-4-12, yours is missing a group of 4)

  /Kenneth

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  May 22, 2006 at 8:38 am

  #639288

  The test GUID should have said 'BF33FA65-D558-43F5-BE47-0F0D20AB0028'.

  Turns out my problem was the Select in the SP:

        Select Fullname, Start_Date from tSearch where SearchGUID=@srch

  I actually had:

        Select Fullname, Start_Date from tSearch where SearchGUID='@srch'

  Taking off the quotes fixed it.