stored procedure calling another stored procedure

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

General SQL Server Forums

New to SQL Server Programming

stored procedure calling another stored procedure

Author

Topic

gudushen
Starting Member

16 Posts

Posted - 2008-10-30 : 15:40:34

Hi friends, can you tell me the syntax for calling a storeprocedure in another one?
basically I'm trying to do the following

select id, .... from ....
where date = ( exec sp_maxdate id )

so for each "id" in the select, i should run sp_maxdate

what would the correct syntax be?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 15:44:46

can't do it like that. You should consider convert your SP_MAXDATE stored procedure into a function instead of SP.

Then you can do ..
select ...
where date = dbo.fn_maxdate(id)

gudushen
Starting Member

16 Posts

Posted - 2008-10-30 : 15:57:01

quote:
Originally posted by hanbingl

can't do it like that. You should consider convert your SP_MAXDATE stored procedure into a function instead of SP.

Then you can do ..
select ...
where date = dbo.fn_maxdate(id)

that's a really good suggestion, I couldn't do it through, I think it's because I have sql server 7.0

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 16:06:28

I guess another solution is to use openquery:


select ... 
where date = (
SELECT * as maxdate FROM OPENQUERY(LOCALSERVER, 'exec sp_maxdate id')
)

gudushen
Starting Member

16 Posts

Posted - 2008-10-30 : 16:37:52

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 23:57:17

quote:
Originally posted by hanbingl

I guess another solution is to use openquery:

select ...
where date = (
SELECT * as maxdate FROM OPENQUERY(LOCALSERVER, 'exec sp_maxdate id')
)

OPENQUERY requires you to setup a linked server. Use OPENROWSET instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

gudushen
Starting Member

16 Posts

Posted - 2008-10-31 : 09:44:03

I'm having problem with openrowset
this is what I have:

select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
Integrated Security=SSPI','EXEC MTL-CRM01.sp_getMaxDate idno')

I get the following error:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'CRM01'.

anyone know why?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 09:50:14

select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
Integrated Security=SSPI','EXEC [MTL-CRM01].sp_getMaxDate idno')

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 09:51:31

quote:
Originally posted by hanbingl

I guess another solution is to use openquery:

select ...
where date = (
SELECT * as maxdate FROM OPENQUERY(LOCALSERVER, 'exec sp_maxdate id')
)

I dont think you can use * as alias
In a subquery actually alias doesnt matter.

Madhivanan

Failing to plan is Planning to fail

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-31 : 09:58:35

change "Data Source=TEST" to "Data Source=<YOUR SERVER NAME>""

gudushen
Starting Member

16 Posts

Posted - 2008-10-31 : 10:45:09

thanks madhivanan, i'm really bad with this syntax...

right now

select db1.id, .... from db1, ....
where date = select * from
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','EXEC [eccnet_test]..sp_getMaxDate [db1.id]')

gives me blank results. if i use real id numbers the openrowset works fine. almost there...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 10:49:12

[code]select db1.id, .... from db1, ....
where date in ( select <datefieldhere> from
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','EXEC [eccnet_test]..sp_getMaxDate [db1.id]')
[/code]

gudushen
Starting Member

16 Posts

Posted - 2008-10-31 : 11:23:47

quote:

Originally posted by visakh16

select db1.id, .... from db1, ....
where date in ( select <datefieldhere> from 
OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','EXEC [eccnet_test]..sp_getMaxDate [db1.id]')

still gives me a blank result, again, if i use a real value for db1.id in OPENROWSET, it works fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:25:14

does[eccnet_test]..sp_getMaxDate return date values which are in db1?

gudushen
Starting Member

16 Posts

Posted - 2008-10-31 : 11:36:52

quote:
Originally posted by visakh16

does[eccnet_test]..sp_getMaxDate return date values which are in db1?

in the real query, i have:

SELECT IV_Master.EAN_UPC_NO AS iv_master_upc,
ean.EAN_UPC_NO AS EAN_UPC,
FROM eannet AS ean
INNER JOIN IV_Master
ON ean.DATE_UPDATED > IV_Master.DATE_UPDATED
AND ean.EAN_UPC_NO = iv_master.EAN_UPC_no
INNER JOIN SAMPLE_INVENTORY AS inv
ON inv.EAN_UPC_NO = ean.EAN_UPC_NO
INNER JOIN SAMPLE_RECEIPTS AS recp
ON inv.SAMPLE_RECEIPTS_ID = recp.SAMPLE_RECEIPTS_ID
WHERE ((recp.IMAGE_REQUIRED = 'COM3') OR (recp.IMAGE_REQUIRED = 'COM4') )
and recp.Date_Received = (select MaxDate from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','EXEC [eccnet_test]..sp_getMaxDate [ean.ean_upc_no]'))

each ean_upc_no gets serveral(more than 3) hits in sample_inventory, which connects to sample_receipts, sample_receipts has a date_received field and I only want the latest date.
the OPENROWSET is to filter out duplicate rows with the same ean_upc_no.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:39:44

i think you may be better off making sp_getMaxDate as a function rather than a stored procedure.By doing so you will be able to use it in joins or in where clause

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-01 : 02:49:32

or move the result to temp table and join with that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources