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

Did you transpose your userid and bookid arguments by mistake? Can you post an example EXEC statement that is causing the bug. Your SQL so far looks fine. — Jim Counts
– Jim Counts, Commented Jun 6, 2009 at 14:45

Sören Kuklau · Accepted Answer · 2009-06-06 14:56:37Z

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

marc_s · Accepted Answer · 2009-06-06 14:52:35Z

10

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

answered Jun 6, 2009 at 14:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

4 Comments

Matthew Vines Over a year ago

beat me by 4 seconds. Removing mine.

simplyme Over a year ago

Yea I noticed that, so how can i do that from my stored proc, because when I tried doing that, an error saying a varchar can't be converted to a uniqueidentifier was what i got. Thanks

p.campbell Over a year ago

@simply: sounds like that* is the real problem; can you add that line of code which creates that varchar/uid error message?

Jeff Davis Over a year ago

Using a string only works in some cases. Converting the string to uniquidentifier works in all cases.

Jody · Accepted Answer · 2009-06-06 14:53:41Z

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.

mishik · Accepted Answer · 2013-07-15 10:26:57Z

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.

Collectives™ on Stack Overflow

Inserting GUID into SQL Server

4 Answers 4

Comments

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Linked

Related