Inserting GUID into SQL Server

Question

I have a stored proc were I want to insert a GUID (user id) into a table in MS SQL but I keep getting an error about the hyphen '-' that is part of the guid value, here's my proc defined below;

@userID uniqueidentifier,
@bookID int,
@dateReserved datetime,
@status bit

INSERT INTO Reservation(BookId, DateReserved, [Status], UserId)
VALUES (@bookID, @dateReserved, @status, @userID)

But when I put single quotes around the value if the stored proc is executed in Management Studio, it runs fine. How can I handle the guid insertion without problems from my stored proc?

Thanks guys.

Update Here's the sql exec

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_ReserveBook]
 @userID = AE019609-99E0-4EF5-85BB-AD90DC302E70,
 @bookID = 7,
 @dateReserved = N'09/03/2009',
 @status = 1

SELECT  'Return Value' = @return_value

Here's the error message

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

Answer 1

Is the error happening when using ADO.NET for your insert, or in SQL Mgmt Studio?

If in code, are you typing the parameter as SqlDbType.UniqueIdentifier?

Perhaps can you post the offending code that's generating the error?

Answer 2

You simply need to QUOTE your GUID:

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_ReserveBook]
        @userID = 'AE019609-99E0-4EF5-85BB-AD90DC302E70',  
        @bookID = 7,
        @dateReserved = N'09/03/2009',
        @status = 1

SELECT  'Return Value' = @return_value

Marc

Answer 3

You need single quotes around your GUID....it's just a string to sql server.

You could try letting the sp generate the GUID with the sql function newid() ...sql server only i think.

And if you're pulling the GUID from another table, let the SP go get that GUID from that table.

Answer 4

Just cast it from a varchar.

DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_ReserveBook]
        @userID = CONVERT(uniqueidentifier, 'AE019609-99E0-4EF5-85BB-AD90DC302E70'),
        @bookID = 7,
        @dateReserved = N'09/03/2009',
        @status = 1

SELECT  'Return Value' = @return_value