Pass multiple values to a stored procedure, rbar avoidance request

Question

Pass multiple values to a stored procedure, rbar avoidance request

Chrissy321

SSCoach

Points: 15769
More actions
July 29, 2011 at 12:39 pm

#245145

Hello All,
I have a vendor provided stored procedure.
CREATE PROCEDURE MyVendorProc @account varchar(50)
AS
.....
So the procedure takes one value and I can't change the procedure. Once a day I need to call this procedure a couple of dozen times to get data for the accounts I am interested in.
My thought would be to create a table with all the values I need to pass and then cursor through the table and call the procedure for each row by agonizing row in the cursor.
Is there a better way to do this without the cursor?
thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

how much of a change can you do? can you add your own proc, that does whatever the vendor proc did, but as a set based manipulation?

I know with one of our 3rd party apps, the license / end user agreement says we can add all the views we want for reporting, but nothing that manipulates the data.....

is there a way, say based on a datetime field, that you could know the last time an account was updated ?

then you might be able to do the work on rows where Account.LastUpdated > LastTimeWeBatchProcessed

CREATE PROCEDURE MyBatchVendorProc @account varchar(50)

AS

.....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chrissy321 SSCoach Points: 15769 More actions · Answer 2

The vendor proc is encrypted. I know there are ways around that but I am somewhat reluctant.

The proc also accepts a date range ( I was simplifying in my example) I'll need to pass in a range for previous month, previous quarter, and year to-end of previous month.

There can be revisions to this data so even if i have the previous months' data I still will need to re-run to check if anything has changed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Chrissy321 (7/29/2011)
The vendor proc is encrypted. I know there are ways around that but I am somewhat reluctant.
The proc also accepts a date range ( I was simplifying in my example) I'll need to pass in a range for previous month, previous quarter, and year to-end of previous month.
There can be revisions to this data so even if i have the previous months' data I still will need to re-run to check if anything has changed.

It's all the same thing... begin / end date.

1 proc should be all you need.

Decrypt on a test server / db and nothing can go wrong

Chrissy321 SSCoach Points: 15769 More actions · Answer 4

From my license agreement.

Subscriber agrees that it shall not reverse engineer, disassemble, decompile or otherwise attempt to derive the source code of the software provided hereunder.

I think I would rather rbar than violate the agreement.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Chrissy321 (7/29/2011)
From my license agreement.
Subscriber agrees that it shall not reverse engineer, disassemble, decompile or otherwise attempt to derive the source code of the software provided hereunder.
I think I would rather rbar than violate the agreement.

Call the vendor and ask for a fix (or new dev).