Nesting stored procedure, how to ?

Hello,

I'm trying to use nested stored procedure but can't get it to work.

I've created a stored procedure named 'sousmenu1_parents' which does a simple select, no parameter.

here's what I'm trying to achieve now :

CREATE PROCEDURE SelectAllDroitSpe

-- Add the parameters for the stored procedure here

@.UserId int

AS

EXEC sousmenu1_parents;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT sousmenu1_parents.menu_id1 AS sousmenu1_id

FROM authorisations_speciales LEFT JOIN sousmenu1_parents ON authorisations_speciales.page_id = sousmenu1_parents.menu_id1

WHERE authorisations_speciales.utilisateur_id=@.UserId

END

I get 'sousmenu1_parents isn't a valid object name'

Where am I going wrong ?

Thanks a lot.

Flip:

If your stored procedure does a simple select then transform the procdure into an function.

Dave

|||

Hi,

you can′t directly select from a strored procedure although the procedure return a table with the results, you will have to EXEC the procedure and redirect the output into a table (like a temp table)

-Create a Table first (can be also a temp table)
-

INSERT INTO YourTable
EXEC procedurename

-Use the table in your query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is a good alternative; the problem with the INSERT INTO ... EXEC PROC method is that you can only use this construct at a single level in a nested series; after that you get execution errors. Don't get me wrong, I use this construct frequently but it is definitely not my first choice when I am dealing with nesting a procedure that "is a simple select". You might be able to make a case for avoiding the function for some other reason but this post is titled "Nesting stored procedure, how to?"

|||

Thanks very much.

I'm now looking up on Functions.

I'm new to Sql Server and I'm migrating an Access database.

I used many nested queries in Access and it looks like converting them is going to be a nightmare.

One thing I don't understand : if I can't "directly select from a stored procedure" what is the point in nesting stored procedure ?

|||

Flip:

Stored procedures will nest; however, it sometims requires judicious use of your alternatives. There are several options to return data from a called stored procedure to a calling stored procedure:

Use of output parameter(s)|||

ignition, I was a big user of access in my past and used many nested access queries to produce result sets. Think of Access queries as Views in SQL.

If in Access you had two queries

select col1, col2 from tablea where some where clause.... (query name is Query1)

select count(*) from Query1 (query name is Query2)

In SQL you could create a View:

Create View vw_Query1 as

select col1, col2 from tableA where....some where clause

Select count(*) from vw_Query 1

Views do not allow you to pass parameters (this is where stored procedures or functions come into play)

Reason you might want to nest stored procedures is to utilize other stored procedures to get a resultset or parameter back to the calling procedure and/or to encapsulate standard code that you cannot execute using functions. Functions have specific limitations (can't do data manipulation (insert, create, drop, can only call extended stored procedures) that you can do in stored procedures.

Create Proc usp_Test1 @.tablename varchar(100)

as

exec usp_AddMissingColumns @.tablename (does an ALTER which you can't do in functions)

exec usp_SetBlanksToNull @.tablename

return

|||

Great, it works a treat using a View !

This all make sense now ...

So for all the simple SELECT queries I had in Access (no parameters) should I use views in Sql Server ? is it in any way faster than a stored procedure, or any other advantage of this ?

Thanks again everyone, and in case I don't come back here before next year : have a lot of fun to end 2006 !

|||

Ignition, I wouldn't automatically change every simple query you have in Access to a view. If you're migrating to SQL Server, it's a good time to analyze the MS Access queries you currently have to determine if there is a continued need for them. I know that I would create queries (in Access) to do some ad hoc things, and then leave them there (laziness on my part!). When I started looking at the stored queries (for migration), I realized that many of them could be combined into a handful of views (some did basically the same thing but with different columns).

Think of a view as a subset (either just certain columns, an aggregation, and/or with specific where clauses) of a table. If you are querying the same data with the same where clause continually, this is probably a good candidate for a view... If you are continually looking at only certain columns within a table, probably a candidate for a view...

Views, stored procedures, and functions all have their place to do certain types of activities. If you lookup these items in Books Online, they will give you some scenarios on when/where and how to use them...

Good luck and happy New Year...

|||Convert your SPs to table-valued functions or even views if there is no parameterization so that you can reuse them easily.