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

by

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 🙂

More Related Contents:

  1. How to SELECT FROM stored procedure
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server Insert if not exists
  4. SQL Server – How to lock a table until a stored procedure finishes
  5. Stored procedure or function expects parameter which is not supplied
  6. What is a stored procedure?
  7. How to assign an exec result to a sql variable?
  8. Search text in stored procedure in SQL Server
  9. SQL Server SELECT into existing table
  10. How do I execute a stored procedure once for each row returned by query?
  11. How to check if a stored procedure exists before creating it
  12. Execute Stored Procedure from a Function
  13. SQL: Select dynamic column name based on variable
  14. SQL : in clause in stored procedure:how to pass values
  15. SQL Server silently truncates varchar’s in stored procedures
  16. Split values over multiple rows [duplicate]
  17. How do I pass a list as a parameter in a stored procedure?
  18. Must declare the scalar variable
  19. Stored procedure that exports data into csv files only exports to one file
  20. Custom Date/Time formatting in SQL Server
  21. Insert Update stored proc on SQL Server
  22. SQL Server: should I use information_schema tables over sys tables?
  23. Inserting rows into a table with one IDENTITY column only [duplicate]
  24. UNION the results of multiple stored procedures
  25. How to pass a temp table as a parameter into a separate stored procedure
  26. Functions vs Stored Procedures
  27. How to execute a stored procedure inside a select query
  28. Dynamic order direction
  29. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  30. Call stored procedure with table-valued parameter from java

Leave a Comment