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.

Solution 1

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

Solution 2

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