How to execute a stored procedure inside a select query

Question

SELECT col1         col2         col3   EXEC GetAIntFromStoredProc T col1  AS col4      FROM Tbl AS T      WHERE  col2    parm     How to write this SQL query in SQL Server 2008

User · Answer

Functions are easy to call inside a select loop, but they don't let you run inserts, updates, deletes, etc. They are only useful for query operations. You need a stored procedure to manipulate the data.

So, the real answer to this question is that you must iterate through the results of a select statement via a "cursor" and call the procedure from within that loop. Here's an example:

DECLARE @myId int;
DECLARE @myName nvarchar(60);
DECLARE myCursor CURSOR FORWARD_ONLY FOR
    SELECT Id, Name FROM SomeTable;
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myId, @myName;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXECUTE dbo.myCustomProcedure @myId, @myName;
    FETCH NEXT FROM myCursor INTO @myId, @myName;
END;
CLOSE myCursor;
DEALLOCATE myCursor;

Note that @@FETCH_STATUS is a standard variable which gets updated for you. The rest of the object names here are custom.

User · Answer

As long as you re not doing any INSERT or UPDATE statements in your stored procedure  you will probably want to make it a function  Stored procedures are for executing by an outside program  or on a timed interval  The answers here will explain it better than I can  Function vs  Stored Procedure in SQL Server

User · Answer

Thanks  twoleggedhorse    Here is the solution     First we created a function  CREATE FUNCTION GetAIntFromStoredProc  parm Nvarchar 50   RETURNS INTEGER  AS BEGIN    DECLARE  id INTEGER     set  id   select TOP 1  id From tbl where col  parm      RETURN  id END  then we do the select query  Select col1  col2  col3  GetAIntFromStoredProc T col1  As col4 From Tbl as T Where col2  parm

User · Answer

quot Not Possible quot   You can do this using this query  Initialize here declare  sql nvarchar 4000      Set Value  amp  exec command of your sp with parameters SET  sql      Exec spName  param  EXECUTE sp executesql  sql   N  param type    param    param

User · Answer

Not Possible   You can use a function instead of the stored procedure

User · Answer

Create a dynamic view and get result from it         CREATE PROCEDURE dbo usp userwise columns value        userid BIGINT   AS  BEGIN         DECLARE  maincmd NVARCHAR max           DECLARE  columnlist NVARCHAR max           DECLARE  columnname VARCHAR 150           DECLARE  nickname VARCHAR 50            SET  maincmd               SET  columnname               SET  columnlist               SET  nickname                DECLARE CUR COLUMNLIST CURSOR FAST FORWARD         FOR             SELECT columnname   nickname             FROM dbo v userwise columns              WHERE userid    userid          OPEN CUR COLUMNLIST         IF   ERROR  lt  gt  0             BEGIN                 ROLLBACK                 RETURN             END             FETCH NEXT FROM CUR COLUMNLIST         INTO  columnname   nickname          WHILE   FETCH STATUS   0             BEGIN                 SET  columnlist    columnlist    columnname                        FETCH NEXT FROM CUR COLUMNLIST                 INTO  columnname   nickname             END         CLOSE CUR COLUMNLIST         DEALLOCATE CUR COLUMNLIST            IF NOT EXISTS  SELECT   FROM sys views WHERE name    v userwise columns value               BEGIN                 SET  maincmd    CREATE VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END         ELSE             BEGIN                 SET  maincmd    ALTER VIEW dbo v userwise columns value AS SELECT sjoid  CONVERT BIGINT      CONVERT VARCHAR 10    userid       as userid                                    CHAR 39     nickname   CHAR 39      as nickname                                    columnlist     compcode FROM dbo SJOTran               END          EXECUTE sp executesql  maincmd END  ----------------------------------------------- SELECT   FROM dbo v userwise columns value

User · Answer

You can create a temp table matching your proc output and insert into it   CREATE TABLE  Temp       Col1 INT    INSERT INTO  Temp     EXEC MyProc

[sql] How to execute a stored procedure inside a select query

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to stored-procedures