How to assign an exec result to a sql variable?

by

I always use the return value to pass back error status. If you need to pass back one value I’d use an output parameter.

sample stored procedure, with an OUTPUT parameter:

CREATE PROCEDURE YourStoredProcedure 
(
    @Param1    int
   ,@Param2    varchar(5)
   ,@Param3    datetime OUTPUT
)
AS
IF ISNULL(@Param1,0)>5
BEGIN
    SET @Param3=GETDATE()
END
ELSE
BEGIN
    SET @Param3='1/1/2010'
END
RETURN 0
GO

call to the stored procedure, with an OUTPUT parameter:

DECLARE @OutputParameter  datetime
       ,@ReturnValue      int

EXEC @ReturnValue=YourStoredProcedure 1,null, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT CONVERT(char(23),@OutputParameter ,121)

OUTPUT:

0
2010-01-01 00:00:00.000

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Function vs. Stored Procedure in SQL Server
  3. SQL Server – How to lock a table until a stored procedure finishes
  4. What is a stored procedure?
  5. SQL Server SELECT into existing table
  6. How to check if a stored procedure exists before creating it
  7. Stored procedure that exports data into csv files only exports to one file
  8. Inserting rows into a table with one IDENTITY column only [duplicate]
  9. Eliminate and reduce overlapping date ranges
  10. I dont know sql syntax for my table [closed]
  11. Get top 1 row of each group
  12. Convert Month Number to Month Name Function in SQL
  13. Multiple INSERT statements vs. single INSERT with multiple VALUES
  14. Limit on the WHERE col IN (…) condition
  15. Removing leading zeroes from a field in a SQL statement
  16. Generate a resultset of incrementing dates in TSQL
  17. Better techniques for trimming leading zeros in SQL Server?
  18. Common Table Expression, why semicolon?
  19. T-SQL split string based on delimiter
  20. Query error with ambiguous column name in SQL
  21. Why (and how) to split column using master..spt_values?
  22. Splitting delimited values in a SQL column into multiple rows
  23. What does =* mean?
  24. SQL: parse the first, middle and last name from a fullname field
  25. SQL Select Upcoming Birthdays
  26. How can I include null values in a MIN or MAX?
  27. Get Start and End Date from Week Number SQL Server
  28. Get the last day of the month in SQL
  29. SQL take just the numeric values from a varchar
  30. Cumulative Total in MS Sql server [duplicate]

Leave a Comment