CODEWITHSUNDEEP
sql-server
DisgruntledGoat
DisgruntledGoat

Reputation: 72510

Error when using INSERT INTO with SELECT

I'm trying to compile this stored procedure on MSSQL:

ALTER PROCEDURE [dbo].[sp_Notice_insert]
    @type text,
    @message text
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO tbl_Notices (id, type, message)
    VALUES (NewID(), @type, @message);

    DECLARE @noticeid uniqueidentifier;
    SET @noticeid = SCOPE_IDENTITY();

    INSERT INTO tbl_NoticesInbox (userid, noticeid)
    (SELECT id, @noticeid FROM tbl_User WHERE role='Staff');

END
GO

It should insert a "notice" into one table then insert a "notification" into the inbox table, for every 'Staff' user. But when compiling I get this error:

Operand type clash: numeric is incompatible with uniqueidentifier

The 'role' field is nbarchar(10) so I tried N'Staff' as the value too but I get the same error. It doesn't say which types are actually clashing. What am I doing wrong?

Upvotes: 0

Views: 2462

Answers (1)

Jamiec
Jamiec

Reputation: 136074

The problem is that SCOPE_IDENTITY() returns the last value entered into an IDENTITY column within the current scope, and therefore by default returns a numeric value.

It looks to me like you want to create a NEWID() and use that both to insert the header and related records:

DECLARE @noticeid uniqueidentifier;
SET @noticeid = NEWID();

INSERT INTO tbl_Notices (id, type, message)
VALUES (@noticeid , @type, @message);

INSERT INTO tbl_NoticesInbox (userid, noticeid)
(SELECT id, @noticeid FROM tbl_User WHERE role='Staff');

Upvotes: 6

Related Questions