Geek Answers Handbook

We can return null from the stored procedure

Is it possible to return null from a stored procedure. I do not want to use collase or isnull. I want to capture NULL in an interface. 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 procedure 'spOnlineTest_CheckLogin' tried to return NULL, which is not valid. Instead, status 0 will be returned. Msg 0, level 11, state 0, line 0 A serious error occurred in the current command. Results, if any, should be discarded.

+7
database sql-server tsql stored-procedures sql-server-2005
source share
3 answers

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

+7
source share

use 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 as follows

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

You can think of good as shown below. Let me first set the context. We can have the table Table1(id int, name varchar(2), Address varchar(2)) and you want to get the identifier, and if it is not found, it will be zero. Therefore, we could write proc as shown below:

 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.

0
source share

More articles:


All Articles