none
calling stored procedure with in select statement

    Question

  • Hi,

            Any one pls tell me how to call the stored procedure in SQL server

    Monday, September 27, 2010 2:57 PM

Answers

  • Calling a stored procedure is a separate command and a proc cannot be called inside of a SELECT. If you really need to refererence results inside a SELECT, consider converting your proc into a table valued function.

    To call a proc, you use the word EXEC or EXECUTE:

    EXEC myproc param1, param2 ......

    If the call is the first or only thing in the back, you can omit the EXEC, but it is good practice to always include the EXEC.

    The only statement that a proc call can be embedded in is an INSERT, if the proc returns only a single set of results and you have a table with the appropriate columns to hold that result.

    INSERT INTO mytable
       EXEC myproc paramlist ....

     

    If you can give us more details of exactly what you're trying to do, we can probably be more help.

     

    And always tell us what version you are running.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, September 27, 2010 3:10 PM
    Moderator

All replies

  • Create Procedure spTest1 

    As

    Select 'A' As Col1 

     

    Go 

     

    Execute spTest1

     

    You cannot call within a SQL Statement, it has to before that just in case you can use an output variable and use that variable in your SQL Statements. 

    http://msdn.microsoft.com/en-us/library/ms187926.aspx

     

    Please explain your scenario a little bit more.


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Monday, September 27, 2010 3:05 PM
  • Do you want to call your user defined stored procedure? If so, then take a look at BOL 

    Executing Stored Procedure

    Sample from this page:

    USE AdventureWorks2008R2;
    GO
    EXEC dbo.uspGetEmployeeManagers 6;
    GO


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 27, 2010 3:07 PM
    Moderator
  • Calling a stored procedure is a separate command and a proc cannot be called inside of a SELECT. If you really need to refererence results inside a SELECT, consider converting your proc into a table valued function.

    To call a proc, you use the word EXEC or EXECUTE:

    EXEC myproc param1, param2 ......

    If the call is the first or only thing in the back, you can omit the EXEC, but it is good practice to always include the EXEC.

    The only statement that a proc call can be embedded in is an INSERT, if the proc returns only a single set of results and you have a table with the appropriate columns to hold that result.

    INSERT INTO mytable
       EXEC myproc paramlist ....

     

    If you can give us more details of exactly what you're trying to do, we can probably be more help.

     

    And always tell us what version you are running.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Monday, September 27, 2010 3:10 PM
    Moderator
  • EXEC sp_serveroption 'Server', 'Data Access', TRUE
    GO
    SELECT *
      INTO tbl
      FROM OPENQUERY('Server', 'EXEC usp') ;

    If you have #temp tables used in your stored procedure, you will have to use
    SET FMTONLY OFF while calling the procedure like:

    SELECT * INTO tbl
      FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 27, 2010 3:24 PM
    Answerer
  • EXEC sp_serveroption 'Server', 'Data Access', TRUE
    GO
    SELECT *
      INTO tbl
      FROM OPENQUERY('Server', 'EXEC usp') ;

    If you have #temp tables used in your stored procedure, you will have to use
    SET FMTONLY OFF while calling the procedure like:

    SELECT * INTO tbl
      FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 27, 2010 3:24 PM
    Answerer