How to assign an exec result to a sql variable

Question

How do you assign the result of an exec call to a variable in SQL   I have a stored proc called up GetBusinessDay  which returns a single date     Can you do something like this   exec  PreviousBusinessDay   dbo up GetBusinessDay  Date  -1

User · Answer

From the documentation  assuming that you use SQL-Server    USE AdventureWorks  GO DECLARE  returnstatus nvarchar 15   SET  returnstatus   NULL  EXEC  returnstatus   dbo ufnGetSalesOrderStatusText  Status   2  PRINT  returnstatus  GO   So yes  it should work that way

User · Answer

I had the same question  While there are good answers here I decided to create a table-valued function  With a table  or scalar  valued function you don t have to change your stored proc  I simply did a select from the table-valued function  Note that the parameter  MyParameter is optional    CREATE FUNCTION  dbo   MyDateFunction     MyParameter varchar max   RETURNS TABLE  AS RETURN        --- Query your table or view or whatever and select the results      SELECT DateValue FROM MyTable WHERE ID    MyParameter      To assign to your variable you simply can do something like   Declare  MyDate datetime  SET  MyDate    SELECT DateValue FROM MyDateFunction  MyParameter      You can also use a scalar valued function   CREATE FUNCTION TestDateFunction     RETURNS datetime   BEGIN       RETURN  SELECT GetDate     END   Then you can simply do  Declare  MyDate datetime  SET  MyDate    Select dbo TestDateFunction     SELECT  MyDate

User · Answer

This will work if you wish to simply return an integer    DECLARE  ResultForPos INT  EXEC  ResultForPos   storedprocedureName  InputParameter  SELECT  ResultForPos

User · Answer

declare  EventId int  CREATE TABLE  EventId  EventId int   insert into  EventId exec rptInputEventId  set  EventId    select   from  EventId   drop table  EventId

User · Answer

Here is solution for dynamic queries  For example if you have more tables with different suffix  dbo SOMETHINGTABLE ONE  dbo SOMETHINGTABLE TWO  Code  DECLARE  INDEX AS NVARCHAR 20  DECLARE  CheckVALUE AS NVARCHAR max     SELECT COUNT SOMETHING  FROM  dbo SOMETHINGTABLE    INDEX    DECLARE  tempTable Table  TempVALUE int  DECLARE  RESULTVAL INT  INSERT INTO  tempTable     EXEC sp executesql  CheckVALUE  SET  RESULTVAL    SELECT   FROM  tempTable   DELETE  tempTable  SELECT  RESULTVAL

User · Answer

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  gt 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

[sql] How to assign an exec result to a sql variable?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures