Inserting GUID into SQL Server

问题:

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

回答1:

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

回答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

回答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.

回答4:

Try using this:

sql_cmd.Parameters.AddWithValue
("@Company_ID",**Guid.Parse**("00000000-0000-0000-0000-000000000000");

That will take a simple string rep. and turn it into a GUID obj.