none
Executing stored procedures in view RRS feed

  • Question

  • i m using vb.net and sql server2005 to make my windows application.......Is it possible to execute a stored procedure from the view.......??bcaz i want to combine the results of two or more stored procedures.....Is it the rght ways to do so......??plz guide me..........!!!

    Best Regards

    Omar_aa

    Monday, August 4, 2008 5:56 AM

All replies

  • No... you cant call a SP from a view. One way is re-write the SP logic  in a Multi-line table function and call that function inside View.

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Monday, August 4, 2008 6:30 AM
    Moderator
  • Omar,

     

    You cannot put sproc execution into a view definition.

     

    You can do master-detail stored procedure design.

     

    In the master sproc you define a #temptable or @tablevariable where you collect the results by:

     

    Code Snippet

    INSERT #temptable (...column list....)

    EXEC sprocOmega

     

    .........

     

    INSERT #temptable (...column list....)

    EXEC sprocDelta

     

     

    At the end of master sproc you place the final select which has the combined datasets:

     

    Code Snippet

    SELECT ...column list... FROM #temptable.....WHERE.....ORDER BY....

     

     

     

     

    Let us know if helpful.

     

    Monday, August 4, 2008 6:34 AM
    Moderator
  • FYI. using OPENQUERY  you can use SP in a select statement but it is just a workaround.

    Check

    http://madhuottapalam.blogspot.com/2008/05/faq-how-to-use-stored-procedure-in.html

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Monday, August 4, 2008 6:38 AM
    Moderator
  •  

    but how should i handle a dynamic query inside this temp table..........??
    Monday, August 4, 2008 6:43 AM
  • thnx for ur reply.....can u plz tell me that is there any way through which i can call other stored procedures from a single consolidated stored procedures....??

     

    Monday, August 4, 2008 6:51 AM
  • Omar,

     

    Check out the sample below showing how to put dynamic query results into #temptables:

     

    Code Snippet

    DECLARE @SQLCommand nvarchar (1024)

    CREATE TABLE #Product (ProductID int, ListPrice Money, Color varchar(35))

    SET @SQLCommand = N'select ProductID, ListPrice, Color from AdventureWorks.Production.Product Order by ProductID'

    PRINT @SQLCommand

    INSERT #Product

    EXEC SP_EXECUTESQL @SQLCommand

    SELECT * FROM #Product

     

     

    Let us know if helpful.

    Monday, August 4, 2008 6:54 AM
    Moderator
  •  Madhu K Nair wrote:

    FYI. using OPENQUERY  you can use SP in a select statement but it is just a workaround.

     

     

    Madhu,

     

    What would be a practical example when you are really forced to go down the OPENQUERY way?

     

    Thanks.

    Monday, August 4, 2008 6:56 AM
    Moderator
  •  

    but it would onlu add rows to the prescribed table,,.........i also want to add columns..........so how it should be done???
    Monday, August 4, 2008 7:03 AM
  •  SQLUSA wrote:
     

    What would be a practical example when you are really forced to go down the OPENQUERY way?

     

    Thanks.

     

    I do not recommend OPENQUERY in this context and i just gave a workaround. I dont think OP needs to do that. What i feel is, he wants to join the output of an SP (s) with a view. In that case, he can join to View inside SP rather than trying it in View. If not he may write the sp logic in a function and call that function in View.

     

    Madhu

    Monday, August 4, 2008 7:04 AM
    Moderator
  •  omar_aa wrote:

     

    but it would onlu add rows to the prescribed table,,.........i also want to add columns..........so how it should be done???

     

    Omar,

     

    You can have more columns in the #temptables. In that case when you are INSERTing, you have to specify the column list like this:

     

    Code Snippet

    DECLARE @SQLCommand nvarchar (1024)

    CREATE TABLE #Product (ProductID int, ListPrice Money, Color varchar(35),

    Weight smallmoney, [Description] varchar(255))

     

    SET @SQLCommand = N'select ProductID, ListPrice, Color from AdventureWorks.Production.Product Order by ProductID'

     

    PRINT @SQLCommand

     

    INSERT #Product (ProductID, ListPrice, Color )

    EXEC SP_EXECUTESQL @SQLCommand

     

    SELECT * FROM #Product

     

     

    You can also perform UPDATEs on the #temptable after the initial population by INSERT.

     

    Let us know if works.

    Monday, August 4, 2008 7:32 AM
    Moderator
  • How much difficult to merge those separated stored procedures to combine as one. Is it not owned by you. All the solutions are alternate solution or some kind of tricky hack. For long term perspective these may degrade your performance of your system.

    The better solution is, if it is owned by your end, merge those logic in single procedure & use it as single SP..

     

    Monday, August 4, 2008 11:08 AM