Passing paramteres between two procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing paramteres between two procedures

Author

Topic

rebooot
Starting Member

9 Posts

Posted - 2010-04-12 : 14:13:06

Hi guys,

I have a stored procedure (procedure_A) that accepts to run four parameters:

@SQLPeriodIndex
@theMarket
@theSegment
@theCategory

This procedure will return a set of rows. I want to call that procdure and select from the rows it returns a subset according to my where clause. For example:

select * from procedure_A
where MyVariable = 'United Nations'

I know I cannot do this directly in T-SQL so instead I had to create another Stored Procedure call it procedure_B that calls procedure_A through using the OPENQUERY method. In order for me to call procedure_A through OPENQUERY I still need to pass to it the 4 parameters required for it to run. I am having great difficulty getting the syntax together for that. I created local variables @MyIndex, @MyMarket, @MySgement, @MyCategory to pass to procedure_A as follows:

SELECT * FROM OPENQUERY(LOCALSERVER, 'Development..procedure_A @SQLPeriodIndex=@MyIndex, @theMarket=@MyMarket, @theSegment=@MySgement, @theCategory=@MyCategory')
where MyVariable = 'United Nations'

That doesn't work. I get the error"
"Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@MyIndex".

How can I formulate the OPENQUERY to pass my local variables to procedure_A?

Just in case I can't change the code in procedure_A because it perfoms all sort of complex pivoting calcualtions. So, let's just assume I want to use it as is. I also, cannot use multi--statement functions (and embed the code of procedure_A in it and the where clause to it) since I have to specify the structure of the table that it will return. Unfortunately, procedure_A has dymanic pivoting taking place in it. If you do not know what that is, then please ignore this last statement and just assume I only need to find the syntax for OPENQUERY.

I would appreciate any help.

Thank you very much.
Sam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 14:16:33

I think you need to use dynamic sql here

see

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rebooot
Starting Member

9 Posts

Posted - 2010-04-12 : 16:08:41

Nope. The dynamic SQL is not taking the construct that I have. Do you have an example? Or anyone for that matter?

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources