Execute stored procedure

Question

Execute stored procedure

Deepthy

SSCommitted

Points: 1823
More actions
March 6, 2011 at 12:24 pm

#236060

Hi all,
I want to execute the stored procedure usp_myproc(@versionid int,@stationname@ varchar(100),@enddate date,@reason varchar(100)) by passing 200 different values of @versionid and @stationname while the @enddate and 'reason are always constant.
Is there a way I can achieve it in one step instead of passing the parameters 200 times manually?
I have the 200 versionid's and stationnames in a table say 'sample' and the enddate is always todays date and the reason is 'see call f003124'.
Any help is really appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Deepthy (3/6/2011)
Hi all,
I want to execute the stored procedure usp_myproc(@versionid int,@stationname@ varchar(100),@enddate date,@reason varchar(100)) by passing 200 different values of @versionid and @stationname while the @enddate and 'reason are always constant.
Is there a way I can achieve it in one step instead of passing the parameters 200 times manually?
I have the 200 versionid's and stationnames in a table say 'sample' and the enddate is always todays date and the reason is 'see call f003124'.
Any help is really appreciated.

Only one way that I know of... rewrite the proc to be set-based instead of RBAR. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Deepthy SSCommitted Points: 1823 More actions · Answer 2

Thanks for the reply Jeff.

I cannot alter the procedures as I am not supposed to.

I want to instead run the stored procedure in a cursor, is it a good approach?

DECLARE @pversionid int

DECLARE @pstationname varchar(100)

DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT versionid, stationname FROM generatingstation

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @pversionid, @pstationname

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC usp_myproc

@versionid = @pversionid,

@stationname= @pstationname,

@enddate = getdate(),

@reason = 'see call f003124'

FETCH NEXT FROM my_cursor INTO @pversionid, @pstationname

END

CLOSE my_cursor

DEALLOCATE my_cursor

If not is there any alternative?

Please help

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

There are ways to build all 200 commands at once and then execute them with a single EXEC but it won't make the code any more effecient because building the commands isn't the slow spot. You're cursor is going to be just as good if not better because people will understand how it works during any future troubleshooting.

It's a real shame that you can't rework the original stored procedure. It sounds like they made it for a GUI and now you need to use it for a batch.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions