cascading stored procedures

microsoft.public.sqlserver.programming

26 Jan 2006 11:48 PM

RobKaratzas

hi folks

I'm having a few issues PARAMETERizing cascading stored procedure calls
(i.e. HQ Database A, has done it's thing, now call Regional Office AA db to
fire off a stored procedure, etc...)

Parameterizing EVERYTHING is OK (SERVER, account, pwd, etc.),
EXCEPT the store procedure parameter value. Here's the 'vanilla' code
(which runs just fine from Query Analyzer):

declare @modYr varchar(10)

SELECT @modYr = StartDate FROM OPENROWSET('SQLOLEDB',
'UID=mysa;PWD=mypwd;server=REGIONAA_SSDB;', 'exec
pvd.dbo.sp_DetermineACCTBeginOfYear ''2005''')

print @modYr

Again, it's JUST the ''2005''' parameterization which is giving me
headaches (FROM WITHIN A CALLING SPROC). Tried many variations of stringing
and quoting...

Just wondering if there's some sort of limitation?

(Especially after I hit the 'hard-wired' first parameter on the DATEADD last
week, where I had to CASE it instead of it interpreting a field value for
it, i.e.)

SET ScheduledDate =
Case
When (TimingType='ww' and DateType='TCMRD') Then DATEADD(ww,-1 *
TimingAmount,@TCMRD)
When (TimingType='ww' and DateType<'TCMRD') Then DATEADD(ww,-1 *
TimingAmount,@SMMRD)
When (TimingType='m' and DateType='TCMRD') Then DATEADD(m,-1 *
TimingAmount,@TCMRD)
When (TimingType='m' and DateType<'TCMRD') Then DATEADD(m,-1 *
TimingAmount,@SMMRD)

:
:
end

thanks
rob