CODEWITHSUNDEEP
sqlsql-serverdatabasestored-proceduresuniqueidentifier
coding1223322
coding1223322

Reputation: 473

Incorrect Syntax When Executing Stored Proc NewId()

IdentityId is of UNIQUEIDENTIFIER type and when I insert a record and set that as NewId(), I get the error message

Incorrect Syntax new )

Not sure what I am missing. Please advice.

Execute command:

Exec dbo.CreateUser 
         @Title = 'MR',
         @FirstName = 'John', 
         @LastName = 'Smith', 
         @IdentityId = NewId(), 
         @PhoneNumber = '01234456789', 
         @MobileNumber = '0987654321', 
         @StatesId = 2, 
         @AddressLineOne = '1 A Road',
         @AddressLineTwo = '',
         @Town = 'Some Town',
         @County = 'County',
         @PostCode = 'AA1 1AA',
         @Country = 'United Kingdom',
         @OrganisationName = 'OrgName',
         @OrganisationDomain = 'orgName.com'

CreateUser stored procedure:

CREATE PROCEDURE [dbo].[CreateUser]
    @Title NVARCHAR(50),
    @FirstName NVARCHAR(50), 
    @LastName NVARCHAR(50), 
    @IdentityId UNIQUEIDENTIFIER, 
    @PhoneNumber NVARCHAR(50), 
    @MobileNumber NVARCHAR(50), 
    @StatesId INT, 
    @AddressLineOne NVARCHAR(MAX),
    @AddressLineTwo NVARCHAR(MAX),
    @Town NVARCHAR(50),
    @County NVARCHAR(50),
    @PostCode NVARCHAR(10),
    @Country NVARCHAR(20),
    @OrganisationName NVARCHAR(MAX),
    @OrganisationDomain NVARCHAR(MAX)
AS
    INSERT INTO Addresses (AddressLineOne, AddressLineTwo, Town, County, PostCode, Country)
    VALUES (@AddressLineOne, @AddressLineTwo, @Town, @County, @PostCode, @Country)

    INSERT INTO Organisations(Name, Domain, AddressId)
    VALUES (@OrganisationName, @OrganisationDomain, SCOPE_IDENTITY())

    INSERT INTO Users (Title, FirstName, LastName, IdentityId, MobileNumber, PhoneNumber, OrganisationId, StatesId, DateCreated)
    VALUES (@Title, @FirstName, @LastName, @IdentityId, @PhoneNumber, @MobileNumber, SCOPE_IDENTITY(), @StatesId, GETDATE())

Upvotes: 2

Views: 1188

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Set the value to a variable and use that:

declare @id uniqueidentifer;

set @id = newid();

exec . . .
     @IdentityId = @id,
     . . .;

There is nothing specific about newid() here. The exec statement doesn't parse expressions, even simple function calls.

Also, but begin and end around the stored procedure body. You should always do this, to prevent unexpected errors.

Upvotes: 2

Related Questions