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

Question

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

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!