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 ?
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.