CODEWITHSUNDEEP
sql-server-2008stored-procedures
Eric Herlitz
Eric Herlitz

Reputation: 26257

Inserting GUID (uniqueidentifier) value in stored procedure

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

Upvotes: 1

Views: 17299

Answers (3)

Ram
Ram

Reputation: 11

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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.

Upvotes: 2

Pintac
Pintac

Reputation: 1585

have you tryed just putting single quoates around the guid.

Upvotes: 4

Related Questions