How to Return the Output of Stored Procedure into a Variable in SQL Server

How to return the output of stored procedure into a variable in sql server

That depends on the nature of the information you want to return.

If it is a single integer value, you can use the return statement

 create proc myproc
 as 
 begin
     return 1
 end
 go
 declare @i int
 exec @i = myproc

If you have a non integer value, or a number of scalar values, you can use output parameters

create proc myproc
  @a int output,
  @b varchar(50) output
as
begin
  select @a = 1, @b='hello'
end
go
declare @i int, @j varchar(50)
exec myproc @i output, @j output

If you want to return a dataset, you can use insert exec

create proc myproc
as 
begin
     select name from sysobjects
end
go

declare @t table (name varchar(100))
insert @t (name)
exec myproc

You can even return a cursor but that's just horrid so I shan't give an example :)

How to return the output of stored procedure into a string variable in SQL Server

Have a look on below sample, just add your stuff and it will work

    CREATE PROCEDURE return_string 
        (@param1 NVARCHAR(10),
        @param2 NVARCHAR(10), 
        @output NVARCHAR(10) OUT)
    AS
    BEGIN
        IF (1=1)
            SET @output = 'here'
        else
            SET @output = null
    END

to test

    declare @out nvarchar(10)
    exec return_string 'firdt param', 'second param', @out out
    select @out

I hope this will help you achive what you need.

So your SP would look like:

CREATE PROCEDURE sp_Check_User_Password
(
    @name nvarchar(30),
    @email nvarchar(50),
    @pass nvarchar(50) OUT
)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT 1
                FROM dbo.Mitarbeiter
                WHERE dbo.Mitarbeiter.Name = @name 
                    and dbo.Mitarbeiter.Email = @email)
    BEGIN
        SET @pass = (SELECT dbo.Mitarbeiter.Name
                        FROM dbo.Mitarbeiter
                        WHERE dbo.Mitarbeiter.Name = @name 
                            and dbo.Mitarbeiter.Email = @email)
    END
    ELSE
    BEGIN
        SET @pass = null
    END
END

How to store result of stored procedure in a variable using SQL Server

If the procedure is "returning" the value by selecting it, you'll have to use insert into with something like this:

declare @ss table (ss varchar(10))

insert into @ss exec SP_CheckAgentProperty 10

if(exists (select 1 from @ss where ss='NP') ...

or if it has an output parameter, then the call should be:

declare @ss varchar(10)

exec SP_CheckAgentProperty 10, @ss output

if(@ss='NP')

Returning output parameter from stored procedure

You need to pass parameters, as neither have default values. As @p_prefix is an OUTPUT parameter, in order to consume that value, you need to declare a variable to pass to the parameter:

DECLARE @p_prefix varchar(3);
EXEC SFT.usp_GetPrefix @p_table = 'MAC_CHEESE', @p_prefix OUTPUT;

--PRINT @p_prefix; --Or whatever you're going to do with the value.

As a side note, you can make your Procedure considerably shorter:

CREATE PROCEDURE SFT.usp_GetPrefix @p_table  VARCHAR(30), @p_prefix VARCHAR(3) OUTPUT AS
BEGIN

    SET @p_prefix = CASE LEFT(@p_table,3) WHEN 'MAC' THEN 'MAC' ELSE 'UNK' END;

END

How do we select the output of stored procedure into a variable?

You can use table variable to hold the data returned by the procedure and query the table variable to get the desired varchar value. This way.

DECLARE @ResultTable table(ResultValue1 varchar(50))
DECLARE @ResultValue1 varchar(50)

DECLARE @ProcedureName varchar(500)
SET @ProcedureName='USP_TEST1'

INSERT INTO @ResultTable EXECUTE  @ProcedureName;

SET @ResultValue1=(SELECT ResultValue1 FROM @ResultTable)

PRINT @ResultValue1

T-SQL stored procedure result into variable

I do not know what else you are doing within your first SP, but this might be better solved within an inlineable UDF:

CREATE FUNCTION dbo.CreateJSON()
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (SELECT TOP 10 * FROM sys.objects FOR JSON AUTO);
END
GO

--You can use the UDF in any context. You can define parameters to control the behaviour

DECLARE @TheJson NVARCHAR(MAX);
SET @TheJson=dbo.CreateJSON();
SELECT @TheJson;
GO

--Clean up

DROP FUNCTION dbo.CreateJSON; 

In most cases an inline TVF is better in performance! But you'd have to join its resultset, which is not so intuitive...

Storing the numeric return value of a SQL Server stored procedure into a variable results in said variable always being zero

When you you want to return some values from the stored procedure you need to use OUTPUT parameters.

For example create procedure like

CREATE PROCEDURE [dbo].[SomeStoredProcedure]
   @SequenceName [nvarchar](max),
   @Increment [int],
   @Value int OUTPUT

and then assign proper value to @Value parameter.

To read value returned from the stored procedure use:

EXEC [dbo].[SomeStoredProcedure] 'FOO', 1, @Value = @Value OUTPUT;

Value 0 after EXEC @value = SomeStoredProcedure 'FOO', 1; means that stored procedure returned no errors.

---

Related Topics