Inserting GUID (uniqueidentifier) value in stored procedure

Question

I have this stored procedure which stores information in several tables and one of the key variables is that I can feed the procedure with a guid value to bind these tables.

It goes something like

USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[my_createCompany] @CompanyName nvarchar(255), @CompanyDescription nvarchar(255), @ParentGuid uniqueidentifier AS BEGIN DECLARE @CompanyGuid uniqueidentifier SET @CompanyGuid = NEWID() SET NOCOUNT ON Insert into [dbo].[tblPROCompany] ( [CompanyGuid], [CompanyName], [CompanyDescription], [ParentGuid] ) VALUES ( @CompanyGuid, @CompanyName, @CompanyDescription, @ParentGuid ) END

It looks right, but when assigning a GUID to the variable @ParentGuid the procedure fails. When I look at the output it something like this

USE [MyDatabase] GO DECLARE @return_value int EXEC @return_value = [dbo].[my_createCompany] @CompanyName = N'Asd', @CompanyDescription = NULL, @ParentGuid = 4864DE55-60FB-4A69-814F-428B0178F4BB SELECT 'Return Value' = @return_value GO

And the error of course Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'DE55'.

So it doesn't encapsulate the @ParentGuid as it should. For now I've made a workaround declaring the @ParentGuid as a varchar

@ParentGuid varchar(37), --uniqueidentifier

and the converting it to what the database wants before the insert

if @ParentGuid is not null BEGIN DECLARE @ParentGuidConverted uniqueidentifier SET @ParentGuidConverted = convert(uniqueidentifier, @ParentGuid) END

Is there a better way of doing this?

Thanks

Pintac · Accepted Answer · 2011-06-01 09:48:29Z

4

have you tryed just putting single quoates around the guid.

answered Jun 1, 2011 at 9:48

Pintac

1,5853 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damien_The_Unbeliever · Accepted Answer · 2011-06-01 10:11:30Z

That's only the (partially) broken behaviour when executing the stored procedure using the SSMS wizard. When actually calling it from client code, most data access libraries allow you to pass parameters using the appropriate types (e.g. as a Guid from .Net code), and the data access library will ensure that it's passed across appropriately.

If writing it directly into a query window, surround the value by single quotes.

Ram · Accepted Answer · 2012-11-15 23:16:53Z

USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[my_createCompany] @CompanyName nvarchar(255), @CompanyDescription nvarchar(255), AS BEGIN SET NOCOUNT ON Insert into [dbo].[tblPROCompany] ( [CompanyGuid], [CompanyName], [CompanyDescription], [ParentGuid] ) VALUES ( NEWID(), @CompanyName, @CompanyDescription, NEWID() ) END

Thanks. Adding single quotes around the uniqueidentifier AND inserting the line "SET QUOTED_IDENTIFIER ON" was the solution for me.

Collectives™ on Stack Overflow

Inserting GUID (uniqueidentifier) value in stored procedure

3 Answers 3

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Related