473,287 Members | 1,447 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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.shadow
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.typeid
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='xxxx']? (Maybe I should try that! I've
seen nothing about it though.)

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

@typestringparameter varchar(100) = "yyyy"

SELECT dbo.bi_dictionary_e.typestring, dbo.bi_occ_01_e.row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictionary_e ON
dbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeid
GROUP BY dbo.bi_dictionary_e.typestring,
dbo.bi_occ_01_e.row
HAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)

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.)

Jul 20 '05 #1
3 16897
WGW <wi****@interchange.ubc.ca> wrote in message news:<bu**********@nntp.itservices.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.shadow
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.typeid
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='xxxx']? (Maybe I should try that! I've
seen nothing about it though.)

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

@typestringparameter varchar(100) = "yyyy"

SELECT dbo.bi_dictionary_e.typestring, dbo.bi_occ_01_e.row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictionary_e ON
dbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeid
GROUP BY dbo.bi_dictionary_e.typestring,
dbo.bi_occ_01_e.row
HAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)

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
Jul 20 '05 #2
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=@parameter2 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.ase.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.dbo.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****@interchange.ubc.ca> wrote in message news:<bu**********@nntp.itservices.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.shadow
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.typeid
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='xxxx']? (Maybe I should try that! I've
seen nothing about it though.)

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

@typestringparameter varchar(100) = "yyyy"

SELECT dbo.bi_dictionary_e.typestring, dbo.bi_occ_01_e.row
FROM dbo.bi_occ_01_e INNER JOIN
dbo.bi_dictionary_e ON
dbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeid
GROUP BY dbo.bi_dictionary_e.typestring,
dbo.bi_occ_01_e.row
HAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)

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


Jul 20 '05 #3
WGW (wi****@interchange.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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures...
1
by: coffeeboy | last post by:
When I reference a control on a form in a crosstab criteria field in complains the expression in invalid. Same criteria works on simple select query. Anyone have any ideas? Thanks
1
by: Bob Darlington | last post by:
I'm trying to get the following simplified test query to work so that I can apply it to a more complex final query. I get a 'Too few parameters' error when I try to run the following append query:...
1
by: Maria | last post by:
Hello! I am new to Crystal reports an I have problems passing parameters form outside to Crystal report an creating a report with data from more than one table This is the problem: I have to...
4
by: Mike Dinnis | last post by:
Hi, I've been working through a number of turorials to try to learn more about retrieving data from a SQL database. I think i've mastered techniques where i create a sql string in the page and...
2
by: Nab | last post by:
I have just tried to pass parameters to a procedure in VB 2005 and realised that you only need to pass the input parameter. The output parameter's value will be returned without the need to pass it...
10
by: amazon | last post by:
Our vender provided us a web service: 1xyztest.xsd file... ------------------------------------ postEvent PostEventRequest ------------------------------------- authetication authentication...
9
by: Paddy | last post by:
I blogged on finding a new-to-me feature of Python, in that you are allowed to nnest parameter definitions: .... return p0,p1,p2 .... ('Does', 'this', 'work') Ruben commented that there...
11
by: Whizzo | last post by:
Hi all; Well, I've had my obligatory hour of Googling, scratching my head and trial and error and now I'm stumped. I'm using the following code as a query parameter: ALike...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.