Stored procedure error: "Error converting data type nvarchar to uniqueidentifier"

Question

So I'm new to creating SPs and right now I am trying to create an SP to insert values into my table Report below.

CREATE TABLE Report (
    ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
    STAFF VARCHAR(1000)NOT NULL,
    EMAIL VARCHAR(1000)NOT NULL,
    LASTCHANGE DATE NOT NULL
)

CREATE PROCEDURE spInsertOrUpdate(
    @ID UNIQUEIDENTIFIER,
    @STAFF VARCHAR(1000),
    @EMAIL VARCHAR(1000),
    @LASTCHANGE DATETIME
) AS
BEGIN
    INSERT INTO Report(ID, STAFF, EMAIL, LASTCHANGE)
        VALUES(@ID, @STAFF, @EMAIL, @LASTCHANGE)
END

EXEC spInsertOrUpdate NEWID, 'Evlyn Dawson', '[email protected]', GETDATE

Right After executing the SP I following error:

Msg 8114, Level 16, State 5, Procedure spInsertOrUpdate, Line 0 Error converting data type nvarchar to uniqueidentifier

Can someone please help me out with this issue?

Solution 1

Thanks For all your help.I finally found a proper way of doing this within the SP and I got a proper understanding of SPs now.This is how I resolved the issue

CREATE PROCEDURE spInsertOrUpdate(@STAFF VARCHAR(1000),@EMAIL VARCHAR(1000),@CARS VARCHAR(1000))

AS
BEGIN
INSERT INTO Report(ID,STAFF,EMAIL,CARS,LASTCHANGE)
VALUES(NEWID(),@STAFF,@EMAIL,@CARS,GETDATE())
END

EXEC spInsertOrUpdate 'Evlyn Dawson','[email protected]','Ferrari'

Note that I have also a CARS column

Solution 2

I would start by calling the functions correctly:

EXEC spInsertOrUpdate NEWID(), 'Evlyn Dawson', '[email protected]', GETDATE();

NEWID() and GETDATE() are functions, so you need parentheses after them.

However, I don't think the lack of parentheses would cause that particular error. You would need to set variables first, and then use them for the exec.

EDIT:

A better approach is to set the ids and dates automatically:

CREATE TABLE Report (
    ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID(),
    STAFF VARCHAR(1000) NOT NULL,
    EMAIL VARCHAR(1000) NOT NULL,
    LASTCHANGE DATE NOT NULL DEFAULT GETDATE()
);

CREATE PROCEDURE spInsertOrUpdate (
    @STAFF VARCHAR(1000),
    @EMAIL VARCHAR(1000)
) AS
BEGIN
    INSERT INTO Report(STAFF, EMAIL)
        VALUES (@STAFF, @EMAIL)
END;

EXEC spInsertOrUpdate 'Evlyn Dawson', '[email protected]';

I would also discourage you from using unique identifiers as primary keys in the table. They are rather inefficient, because they can lead to page fragmentation. Use an identity column instead.

Solution 3

This error message is a bit of a wild goose chase, the problem is that both NEWID() and GETDATE() are functions, so require parentheses. Unforunately, you cannot pass a function as a parameter to a stored procedure, so you would first need to assign the values to a variable:

DECLARE @ID UNIQUEIDENTIFIER = NEWID(),
        @Date DATE = GETDATE();

EXEC #spInsertOrUpdate @ID, 'Evlyn Dawson', '[email protected]', @Date;

As an aside, a UNIQUEIDENTIFIER column is a very poor choice for a clustering key

Solution 4

So if your just calling your stored procedure with getdate and newid why dont you just add them as default on your table?

Table

  CREATE TABLE [dbo].[Report](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Report_ID]  DEFAULT (newid()),
    [STAFF] [varchar](1000) NOT NULL,
    [EMAIL] [varchar](1000) NOT NULL,
    [LASTCHANGE] [datetime] NOT NULL CONSTRAINT [DF_Report_LASTCHANGE]  DEFAULT (getdate()),
 CONSTRAINT [PK__Report__3214EC27D2D8BF72] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Procedure

create PROCEDURE spInsertOrUpdate(

@STAFF VARCHAR(1000),
@EMAIL VARCHAR(1000)

) AS
BEGIN
INSERT INTO Report(STAFF, EMAIL)
    VALUES(@STAFF, @EMAIL)
END

Execute statement

EXEC spInsertOrUpdate  'Evlyn Dawson', '[email protected]'

Edit Please also note that your lastchanged column is of type DATE, however if you want date with timestamp you should use datetime

View more solutions

Comments

[Emesh Ransinghe]

I always get this error whenever I add the parenthesis for either NEWID OR GETDATE:Incorrect syntax near ')'

[Emesh Ransinghe]

Is there an issue with my SP or with the way I'm calling it?Cause when I use normal INSERT query my table accepts these values and it works.Also the parenthesis give an error whenever I try to add them to my EXECUTION statement

[Emesh Ransinghe]

Thanks a lot.This really helped

[Emesh Ransinghe]

I really didn't want to leave my table with default values of NEWID and GETDATE.What I wanted to do was to somehow give these features to my SP.Thanks to everyone's helpful answers I was able to get it done.Thanks!!