stored proc in nested SELECT statement

[pettrer]

Hi,

We're using Sql Server 2005. We would like to use a stored procedure in a nested SELECT statement, so to speak, but it doesn't work syntax-wise:

This works:
SELECT cKod FROM (SELECT cKod, cDescription FROM MyTable) AS a

But this doesn't:
SELECT cKod FROM (EXEC test_testing) AS a

So, what we're after is to replace the inner SELECT statement with the stored procedure ("test_testing") call, but that code yields the error: "Incorrect syntax near the keyword 'EXEC'." (Of course, test_testing consists of the same SELECT statement as the inner SELECT statement in the first line.)

We would be very grateful for any help!

/Pettrer

 

[Tom]

Inside your calling stored procedure you can call (exec) another stored procedure and store the results in a temp table.

    Create Table #TestTesting
        (
            Ckod VarChar(50)
        )

    Insert Into #TestTesting 
            Exec Test_Testing

    Select Ckod From #TestTesting

 

[pettrer]

Hello again,

Thanks for your reply. However, for various reasons we'd really like to call a stored procedure directly, like I showed in my example code. Just to confirm: Is it impossible to use the stored procedure the way I tried to?

Thanks again,

Pettrer

 

[Tom]

No you cant however you can call a stored procedure and store its results into a temp table as shown and then use those result in your query

 

[cjard]

Why would you try to select all rows from a SP that returns you a cursor anyway? Just execute the procedure..

If you need to filter or extend the results further, create another SP

 

[pettrer]

Hi again,

Well, here's the problem:

I'm converting a lot of VB code to VB.Net and there are many stored procedures involved. In one form we perform a sort of advanced search routine, with six stored procedures attached to it, depending on what radio button the user has clicked. The result is displayed in a datagridview. The parent form, that is the form that needs access to the result of the search when the user clicks OK in the search form, mirrors the result in order to perform UPDATE sql statements.

My dirty/easy way to accomplish this was to retrieve the datagridview and simply loop trough the rows and do the UPDATE stuff on the result from the .vb file. However, the other programmers didn't like this solution as that would mean slower updates (there can be a few thousand rows) and also no rollback functionality.

In the VB version of the software, this problem was solved in another (ugly) manner: For each parent form, all of the six stored procedures of the search form were duplicated in order to get the same result as the search yielded. Then, the UPDATE statement was executed, with rollback functionality included. The drawback is that there are now LOTS of stored procedures just to retrieve the same results that are listed before my eyes.

So, what we now want is to somehow get the same result as the search form got us, and then perform an UPDATE. That was the reason for trying to have a nested stored procedure call inside the SELECT statement. I fear two problems with the #table approach. One: Isn't there a risk that two users try to perform the update at the same time so that they end up getting each other's results? Two: What if something crashes so that the temp table is still there? Then it's not possible to run the stored procedure again (as the temp table never got dropped). I think that the temp table solution is not really the way we use to do things, and a bit "messy", but maybe that's just me.

Thanks both for your help!

/Pettrer

 

[InertiaM]

How about a VIEW? I havent used them for a long time, but from what you are describing, a View *may* be of use.

A good explanation of views is here

A view is a named, stored query within a database. The query is a standard SELECT statement that may contain inner or outer joins, filters, aliases and other items that you would find in a query, including some that we will consider later in this tutorial. The view is presented as a virtual table that itself can be queried with additional joins, WHERE clauses, etc. Although the view can be queried as if it were a table, no data is stored in the view itself. The information is held in the underlying tables and accessed when the view is used.

So you would do something like

CREATE VIEW cKodList AS SELECT cKod, cDescription FROM MyTable

and then run it with

SELECT cKod FROM cKodList

Note - I only ever used them twice, so they may not do what you want.