Passing parameters to nested parameter queries

[WGW]

Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...

I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In short, if a select query contains a result table that is
generated as a parameter query, how do I pass the parameter through the
select query to the nested parameter query?

For example, if I have a sp parameter query called [e_typerows], I could
write the following select query which uses the (single) result set from
[e_typerows] plus other tables or views:

SELECT TOP 100 PERCENT bi_occ_01_e.row , bi_occ_01_e.pos ,
bi_dictionary_e .typestring, bi_occ_01_e.sha dow
FROM bi_dictionary_e INNER JOIN
(bi_occ_01_e INNER JOIN
[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ON
bi_dictionary_e .typeid = bi_occ_01_e.typ eid
ORDER BY bi_occ_01_e.row , bi_occ_01_e.pos

Even though [e_typerows] is a (storted procedure) parameter query this
will work IF my parameter in [e_typerows] has a default, say "yyyy". In
the default case no parameter needs passing. But what if it doesn't have
a default value or if I need to pass a parameter to it?

Is there any way to replace[e_typerows]in the above query with something
like [e_typerows @myparameter='x xxx']? (Maybe I should try that! I've
seen nothing about it though.)

My parameter query, [e_typerows], looks like this in fact:

@typestringpara meter varchar(100) = "yyyy"

SELECT dbo.bi_dictiona ry_e.typestring , dbo.bi_occ_01_e .row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictiona ry_e ON
dbo.bi_occ_01_e .typeid = dbo.bi_dictiona ry_e.typeid
GROUP BY dbo.bi_dictiona ry_e.typestring ,
dbo.bi_occ_01_e .row
HAVING (dbo.bi_diction ary_e.typestrin g = @typestringpara meter)

Any suggestions would be greatly appreciated! (For the moment, I first
generate the parameter query result set, then I loop through each row of
that result set and do a select query with that row as a parameter --
very complicated!, though it does work.)

[Simon Hayes]

WGW <wi****@interch ange.ubc.ca> wrote in message news:<bu******* ***@nntp.itserv ices.ubc.ca>...

Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...

I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In short, if a select query contains a result table that is
generated as a parameter query, how do I pass the parameter through the
select query to the nested parameter query?

For example, if I have a sp parameter query called [e_typerows], I could
write the following select query which uses the (single) result set from
[e_typerows] plus other tables or views:

SELECT TOP 100 PERCENT bi_occ_01_e.row , bi_occ_01_e.pos ,
bi_dictionary_e .typestring, bi_occ_01_e.sha dow
FROM bi_dictionary_e INNER JOIN
(bi_occ_01_e INNER JOIN
[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ON
bi_dictionary_e .typeid = bi_occ_01_e.typ eid
ORDER BY bi_occ_01_e.row , bi_occ_01_e.pos

Even though [e_typerows] is a (storted procedure) parameter query this
will work IF my parameter in [e_typerows] has a default, say "yyyy". In
the default case no parameter needs passing. But what if it doesn't have
a default value or if I need to pass a parameter to it?

Is there any way to replace[e_typerows]in the above query with something
like [e_typerows @myparameter='x xxx']? (Maybe I should try that! I've
seen nothing about it though.)

My parameter query, [e_typerows], looks like this in fact:

@typestringpara meter varchar(100) = "yyyy"

SELECT dbo.bi_dictiona ry_e.typestring , dbo.bi_occ_01_e .row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictiona ry_e ON
dbo.bi_occ_01_e .typeid = dbo.bi_dictiona ry_e.typeid
GROUP BY dbo.bi_dictiona ry_e.typestring ,
dbo.bi_occ_01_e .row
HAVING (dbo.bi_diction ary_e.typestrin g = @typestringpara meter)

Any suggestions would be greatly appreciated! (For the moment, I first
generate the parameter query result set, then I loop through each row of
that result set and do a select query with that row as a parameter --
very complicated!, though it does work.)

I'm not sure I follow your logic above, but if you're asking how to
re-use or query the result set from a stored procedure, then you can
find a good discussion of the options here:

http://www.sommarskog.se/share_data.html

Simon

[WGW]

Thanks for the pointer: I think it will lead me finally to the answer to
my question, which I have a feeling is too simple to be understood.. in
simple terms!

I think I to do something trivial. It is really simply a call between
two procedures:

>>>>>>>>> > stored procedure 1:
CREATE PROCEDURE parameter_test @parameter1 varchar(100)
AS
select top 10 u_id from test
where u_id= @parameter1
GO

stored procedure 2:
CREATE PROCEDURE select_test @parameter2 varchar(100)
AS
SELECT *
FROM EXEC parameter_test @parameter1=@pa rameter2 INNER JOIN
test2 ON test.u_id = test2.u_id
GO

<<<<<<<<<<<<<<< <<<<<

I didn't understand that an EXEC returns a result set that is like any
other table. The @parameter2 is not being used as such in the select of
procedure 2, but rather in the procedure 1; the parameter is passed to
the nested sp1. Will that work? I will go experiment!

I think the difficulty here is I am simply too little informed to ask
the question properly.
Thanks for the help!

Bill

I found two other tips. The second seems the most general: "how to feed
the result set of one procedure into another".
The first (http://dbforums.com/t576857.html)is :
>>>>>>>> >> P.S. I have tried to insert the result set into a temp table in MS SQL
Server 97. Can I do the same thing in sybase? I have tried the following
stored procedure, but it worked well in MS SQL Server and gave error
"incorrect near exec..." in sybase.

my stored procedures in MS SQL Server 97.
stored procedure 1:
CREATE PROCEDURE [dbo].[testing1] AS
select top 10 u_id from test
GO

stored procedure 2:
CREATE PROCEDURE [dbo].[testing2] AS

CREATE TABLE #abc (u_id char(18))

INSERT #abc
EXEC testing1

SELECT *
FROM #abc

DROP TABLE #abc
GO
<<<<<<<<<<<<< <<


A second description was at
http://www.isug.com/Sybase_FAQ/ASE/section6.3.html (but of course it is
not sysbase I am interested in):
>>>>>>>> >>>>>>

6.3.1: How to feed the result set of one stored procedure into another.

I am sure that this is all documented, but it is worth adding here. It
uses CIS, as do a number of useful tricks. CIS is disabled by default
before 12.0 and not available before 11.5. It is courtesy of BobW from
sybase.public.a se.general, full acceditation will be granted if I can
find out who he is. Excellent tip!

So, the scenario is that you have a stored procedure, AP_A, and you wish
to use the result set that it returns in a query.

Create a proxy table for SP_A.

create table proxy_SP_A (
a int,
b int,
c int,
_p1 int null,
_p2 int null
) external procedure
at "SELF.dbname.db o.SP_A"

Columns a, b, c correspond to the result set of SP_A. Columns _p1, _p2
correspond to the @p1, @p2 parameters of SP_A. "SELF" is an alias put in
sysservers to refer back to the local server.

If you only have one row returned the proxy table can be used with the
following:

declare @a int, @b int, @c int
select @a = a, @b = b, @c = c from proxy_SP_B
where _p1 = 3 and _p2 = 5

More rows can be handled with a cursor.

<<<<<<<<<<<<<<< <<

Simon Hayes wrote: WGW <wi****@interch ange.ubc.ca> wrote in message news:<bu******* ***@nntp.itserv ices.ubc.ca>...

Though I am a novice to MS SQL server (2000 I believe), I can do almost!
everything I need. Maybe not efficiently, but usefully. However, I have
a problem -- a complex query problem...

I can create a parameter query in a stored procedure, but how do I use
the result set of a parameter query in a select query (in the same or
another sp)? In short, if a select query contains a result table that is
generated as a parameter query, how do I pass the parameter through the
select query to the nested parameter query?

For example, if I have a sp parameter query called [e_typerows], I could
write the following select query which uses the (single) result set from
[e_typerows] plus other tables or views:

SELECT TOP 100 PERCENT bi_occ_01_e.row , bi_occ_01_e.pos ,
bi_dictionary _e.typestring, bi_occ_01_e.sha dow
FROM bi_dictionary_e INNER JOIN
(bi_occ_01_ e INNER JOIN
[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ON
bi_dictionary _e.typeid = bi_occ_01_e.typ eid
ORDER BY bi_occ_01_e.row , bi_occ_01_e.pos

Even though [e_typerows] is a (storted procedure) parameter query this
will work IF my parameter in [e_typerows] has a default, say "yyyy". In
the default case no parameter needs passing. But what if it doesn't have
a default value or if I need to pass a parameter to it?

Is there any way to replace[e_typerows]in the above query with something
like [e_typerows @myparameter='x xxx']? (Maybe I should try that! I've
seen nothing about it though.)

My parameter query, [e_typerows], looks like this in fact:

@typestringpa rameter varchar(100) = "yyyy"

SELECT dbo.bi_dictiona ry_e.typestring , dbo.bi_occ_01_e .row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictio nary_e ON
dbo.bi_occ_01 _e.typeid = dbo.bi_dictiona ry_e.typeid
GROUP BY dbo.bi_dictiona ry_e.typestring ,
dbo.bi_occ_01 _e.row
HAVING (dbo.bi_diction ary_e.typestrin g = @typestringpara meter)

Any suggestions would be greatly appreciated! (For the moment, I first
generate the parameter query result set, then I loop through each row of
that result set and do a select query with that row as a parameter --
very complicated!, though it does work.)

I'm not sure I follow your logic above, but if you're asking how to
re-use or query the result set from a stored procedure, then you can
find a good discussion of the options here:

http://www.sommarskog.se/share_data.html

Simon

[Erland Sommarskog]

WGW (wi****@interch ange.ubc.ca) writes:

P.S. I have tried to insert the result set into a temp table in MS SQL
Server 97. Can I do the same thing in sybase? I have tried the following
stored procedure, but it worked well in MS SQL Server and gave error
"incorrect near exec..." in sybase.

For a complete answer, you would have to ask in a Sybase forum, but
INSERT EXEC was added to SQL Server 6.5, which was the second version
of SQL Server after the split between Sybase and Microsoft. When 6.0
came it had a couple of new features that also where in System 10,
Sybase first major post-split release, so one could suspect MS got that
with the code base they got from Sybase. But 6.5 news are likely to
be Microsoft's own inventions. (There were new inventions already in
6.0; EXEC() for dynamic SQL for instance.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp