Why can't I pass in a uniqueidentifier/GUID to a stored procedure

Question

Hi

I have this SP

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_test]    Script Date: 06/12/2010 11:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_test]  
    @id uniqueidentifier
AS
BEGIN

    select * from TestTbl where ProductId= @id

END

I then went to the SP with ms sql 2005 and clicked execute. It comes up with a box where I entered in the GUID. I copied and pasted it straight from my test database.

I get this error.

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'cac671b'.

So why can't I sent in GUIDs? even ones that are copied right from the database and must be valid as they where allowed into the db.

Answer 1

I imagine you're copying-and-pasting a string. Can you declare and use it like this?

CREATE PROCEDURE [dbo].[sp_test] 
    @guidstr varchar(37)
AS

DECLARE @guid uniqueidentifier 
SET @guid = CONVERT(uniqueidentifier, @guidstr)
...

Answer 2

The message

Incorrect syntax near 'cac671b'.

Must mean that it is trying to parse the GUID itself. Try delimiting it in single quotes.

Answer 3

Two hints:

• first of all, do not call your stored procedures sp_(something) - Microsoft specifically warns against that

We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures. The name you choose may conflict with some future system procedure.

• secondly: I have no trouble calling your stored proc like this:

  EXEC proc_test 'B551F2C8-8380-491B-A51F-436E51CDD08F'
  

How are you calling your stored proc?? Show us!