April 18, 2008 at 12:56 pm
Hi all - I am trying to call a stored procedure that can take the same parameter name more than once. This is a procedure provided by our backup software to perform restores. Basically I am calling this procedure for each user database on a production server and restoring to a dev server. Each database could have one or many data/log/index files. Here is the procedure call with a limited set of parameters. @withmove tells the procedure what file to restore and where to place it.
EXEC @Returncode = [master].[dbo].[xp_ss_restore]
@database = @DatabaseName,
@filename = @ArchivePath,
@withmove = @moveFiles1,
@withmove = @moveFiles2 etc...
There are 5 data file parameters passed to the procedure. Not all parameters contain values. In the parameter list I would like to provide:
IF LEN(@moveFiles1) > 0 @withmove = @moveFiles1,
IF LEN(@moveFiles2) > 0 @withmove = @moveFiles2 etc..
Is there any way to provide a conditional parameter list to a procedure?
April 18, 2008 at 1:13 pm
Basically, if there are two datafiles for a database, you want two @WithMove input parameters. If there are three, then three @WithMove input parameters. and etc...
You could do this a couple different ways.
One, figure out the max number of files that need to be moved for a database and put that many input parameters for the SP defaulted to NULL
SP_SOMESPNAME (
@WithMove1 varchar(?) = NULL,
@WithMove2 varchar(?) = NULL,
.
.
@WithMoveN varchar(?) = NULL)
If the parameter is not null and not an empty string, move the file
IF @WithMove1 IS NOT NULL AND @WithMove1 <> ''
{code to move file}
IF @WithMove2 IS NOT NULL AND @WithMove2 <> ''
{code to move file}
etc...
Or use one input parameter for all files that have to be moved by listing each filepath and filename seperated by some delimiter. for example ',' or '|'
Parse this one variable for each path and file in a loop and move the appropriate file to the new system. You will need to use SUBSTRING and CHARINDEX and LEN functions to do this.
Dave
April 18, 2008 at 1:20 pm
Thanks for your reply. I don't have control over the procedure being called, only the parameters being passed. I can pass as many @withmove parameters as I have data files however the number of data files vary per database. This will be an automated process that's why I was hoping i could use an IF or CASE statement when supplying the parameters.
April 18, 2008 at 1:30 pm
Not that I know of with out reviewing the SP you are calling.
April 18, 2008 at 3:32 pm
david.tyler (4/18/2008)
Thanks for your reply. I don't have control over the procedure being called, only the parameters being passed. I can pass as many @withmove parameters as I have data files however the number of data files vary per database. This will be an automated process that's why I was hoping i could use an IF or CASE statement when supplying the parameters.
Simply put, nope. Can't do what you're looking for.
There are ways to make a proc deal with multi-value parameters (most of them require a string split of one sort or another, or XML). But those all require that you have access to the stored procedure being called.
What you're describing can't be done, at least, not as described.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply