Invoke Sproc1 from Sproc2 & SP2 Select SP1 results

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 2000 Forums

Transact-SQL (2000)

Invoke Sproc1 from Sproc2 & SP2 Select SP1 results

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-31 : 22:05:06

Hello,

How are you today?

In MS Access I had the following Union:


SELECT  regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder,  Regions3, 2 As Quarter, April, May, June FROM RegionofResidenceCrosstab

where RegionofResidenceCrosstab accepts two parameters, PrevYear and CurrYear.

When I run the Union query in Ms Access RegionofResidenceCrosstab runs, prompts for the necessary parameters and returns the results which the query above can then select from.

Now migrating these queries to MS SQL with MS Access as a front end using pass through queries. However when I invoke the Union query I am not sure how to pass the parameters from the Union query to the crosstab query, and then select from the crosstab results.

Any idea how I can go about this?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-31 : 22:24:28

Look up functions and table functions in Books Online. You will need to make a function that accepts the paramaters and returns a dataset(table).

You can then use it in your query.

SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstab(Param1,Param2)
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May, June FROM dbo.RegionofResidenceCrosstab(Param1,Param2)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-04-01 : 01:24:46

Hello,

Thanks for the suggestion. Wrote the function and when testing with some dummy values worked fine. However when I run this:

Declare @PrevYear1 varchar(5),@CurrYear1 varchar(5)


SELECT regionorder, Regions3, 1 As Quarter, January as A, February as B, March as C FROM dbo.RegionofResidenceCrosstabFunc(PrevYear1,CurrYear1)
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May, June FROM dbo.RegionofResidenceCrosstabFunc(PrevYear1,CurrYear1)
UNION ALL

I get the following error:

'PrevYear1' is not a recognized OPTIMIZER LOCK HINTS option.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-01 : 05:55:17

You've declared the variables @PrevYear1 and @CurrYear1...but not used them. In your code you left out the @ bit....and it's treating PrevYear1 as a column/constant value.

I think (but am open to correction) that you need to put the @ in your code....(the select statements)

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-04-01 : 09:51:47

Hello,

Yes, that was it, thanks.

BrianL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources