How do I use a SP within a SP?

I know how to use a view within a view or SP, but how do I use a SP?

Do I just write the SQL code the same as I would if I was using a view?

I am using MS SQL 2000 (and Access 2002 .ADP as the front end)

__________________
Mitch

If you just want to run another SP then just use the EXEC statement. However, if you're expecting to use a SP that returns a resultset in the same way that you would use a view then you can't do that directly.

What I mean is you can't do SELECT * FROM EXEC another_sp WHERE fieldInSPResultSet = 'blah'. There is a workaround, but its not pretty.

hth
Phil

Hi

You need to execute the SP e.g. EXEC <stored_procedure>

Check out BOL for more information on EXECUTE

I havn't used views so I cant help you with that. But here are some examples of the top of my head that I have used.

You can get the return code by putting it into a variable e.g.

SELECT @var = EXEC <stored_procedure>

You can insert the resultset into #tmp table depending on what you want to do e.g.

INSERT INTO #tmp
   EXEC <stored_procedure>

Nickie

Think of a View as a virtual table. It looks like a table, and acts like a table, but it is materialized "on the fly" as it were. Thus, you can use a View anywhere you use a table, i.e. typically in a FROM clause.

A Stored Procedure is an executable procedure containing a collection of TSQL statements which are executed, typically via an EXEC command. There are other ways to execute a Store procedure such as via an ADO command object. The contents of a stored procedure are a set of executable statements which may or may not generate a resultset. Thus, you cannot use a SP as you would a view. It can only be executed.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

OK, so how should I approach creating a SP that takes some parameters where I need the results from a second SP that itself takes some parameters?

Do views use indexes? Or is it just SP that do?

Is one faster then the other?

Hi

Check out BOL under CREATE PROCEDURE for more info but this should give you an idea

CREATE PROCEDURE <stored proc name> (@param1 type, @param2 type)
AS

DECLARE @var1 type

EXEC your_other_stored_proc @var, @param1, @param2 OUTPUT

etc...

It depends what you are doing with it.

A Stored procedure is a collection of Transact SQL statements.

Nickie

A view is just the results of a query. The tables which comprise the query may be indexed, and if they are, the view will use them if it needs to.

In SQL Server 2000 you can also create an indexed view. This is a view which has an index on it. There are some restrictions on the situations where this is possible, so read BOL - look for "indexes, on views".

Asking if a view is faster than a stored procedure is like asking which tastes better, apples or oranges. The answer is it depends.

A View is essentially just a SELECT statement. A stored procedure is a set of executable procedural statements such as IF/ELSE, WHILE loops, as well as SQL statements like SELECT. Which one is "faster" depends on the characteristics of each.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

This is "foggy" in my mind: What I need to understand is how to do something in SQL that I used to do in MS Access, namely use a query that takes a parameter(s) that is used within another query that itself takes a different parameter(s).

Do I write one big SQL statement? How do I do the joins to the sub query that has to run first? OR do I run that first sub query and have its output go to a temp table (I think it is called a hash table) then base my query on that table like I would a view?

Just not sure how to go from English to German.;)

You can create a Stored Procedure that takes a parameter like this

CREATE PROCEDURE Procedure1 @Parameter int
AS

SELECT * from Table1
WHERE NumberField1 = @Parameter

RETURN

This will return a row (or set of rows) from the stored procedure filtered by the @parameter value that you give it.

Do you know how to pass a parameter in an ADP?





Sal