CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
chobo2
chobo2

Reputation: 85765

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

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.

Upvotes: 16

Views: 49591

Answers (3)

marc_s
marc_s

Reputation: 754368

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!

Upvotes: 32

Martin Smith
Martin Smith

Reputation: 453028

The message

Incorrect syntax near 'cac671b'.

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

Upvotes: 8

chryss
chryss

Reputation: 7519

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)
...

Upvotes: 1

Related Questions