SQL Server: Returning UniqueIdentifier, Operand type clash: uniqueidentifier is incompatible with int

Question

I'm getting this error when trying to return a unique identifier from a stored procedure - thoughts?

Operand type clash: uniqueidentifier is incompatible with int

Code:

CREATE PROCEDURE TEST
AS
    RETURN NEWID()

Answer 1

Stored procedures can only 'return' an INT (it's meant to be used as a status code). If you want to get other data out of a stored procedure, you should SELECT it. Accessing that data from the code making the call may be a little different, but that's how you have to do it.

For example:

CREATE PROCEDURE Test
AS
    SELECT NEWID()

Answer 2

Procedures in SQL Server do not return values . . . well, there is an implicit integer status, but that is not what you want.

Perhaps you intend:

CREATE PROCEDURE TEST (
    @out_id uniqueidentifier output
)
AS
BEGIN
    SET @out_id = NEWID();
END;

Or, perhaps you want to define a function:

CREATE FUNCTION TEST
RETURNS uniqueidentifier
BEGIN
    RETURN NEWID();
END;

However, I don't believe you can really do this, because newid() is considered to have side effects -- and that is not allowed in a SQL Server function.