Are we able to return null value from saved procedure. i shouldn't use collase or isnull. I wish to capture NULL in the frontend. Is it feasible ?

Edit:

I'm using Sql Server 2005

eg. where i wish 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 tried to return a standing of NULL, which isn't permitted. A standing of is going to be came back rather. Monosodium glutamate , Level 11, Condition , Line A serious error happened around the current command. The outcomes, if any, ought to be thrown away.

No, the return kind of a saved procedure is INT also it can't be null.

make use of 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 refer to it as such as this

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

You are able to think about a proc like follows. Allow me to first set the context. We may possess a table Table1(id int, name varchar(2), Address varchar(2)) and wish to obtain the id and when not found, it will likely be null. Therefore we might write a proc such as 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 works for you personally.