can we return a null from stored procedure.

Question

Can we return null value from stored procedure. i dont want to use collase or isnull. I want to capture NULL at the frontend. Is it possible ?

Edit:

I am using Sql Server 2005

eg. where i want to use

CREATE PROCEDURE [Authentication].[spOnlineTest_CheckLogin]

  @UserName  NVARCHAR(50)
AS
 BEGIN TRY
  BEGIN TRAN
                    COMMIT TRAN
     RETURN NULL
        END TRY

Error The 'spOnlineTest_CheckLogin' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

Answer 1

No, the return type of a stored procedure is INT and it cannot be null.

Answer 2

It is quite possible. If you use a out parameter to emit the return to the calling method, you can do this.

Answer 3

use an output parameter, example

CREATE PROCEDURE Test
  @UserName  NVARCHAR(50), @Status int output
AS
 BEGIN TRY
  BEGIN TRAN
                    COMMIT TRAN
     set @Status = null
        END TRY
        begin catch
        end catch
        go

then call it like this

  declare @s int 
  set @s =5
  exec Test'bla',@s output
  select @s --will be null

Answer 4

You can think of a proc like follows. Let me first set the context. We might have a table Table1(id int, name varchar(2), Address varchar(2)) and want to get the id and if not found, it will be null. So we might write a proc like the following:

CREATE PROCEDURE GetId
  @Name  VARCHAR(50), @Status int output
AS
 BEGIN TRY
     set @Status = null
     select @Status = id from Table1 where name=@name

This will work for you.