Execute Stored Procedure with Select Statement as Input Parameters

Question

Post reply

Execute Stored Procedure with Select Statement as Input Parameters

prakashdotc

Ten Centuries

Points: 1165
More actions
September 1, 2010 at 7:35 am

#140403

Hi All,
Is it possible to call a stored procedure by providing input parameters through a select statement, as specified below
EXEC spInventoryUpdate
SELECT ItemCode, Quantity FROM ITEMS WHERE QuantityOnHand > 0
Stored Procedure spInventoryUpdate takes ItemCode and Quantity on input parameter...
Thanks in Advance
Prakash.C

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Yes it's possible.

However I would reverse engineer that sp and make it run for multiple codes at the same time rather than 1 at the time.

If you send the sp code, sample data and expected output we shoul dbe able to help you out there.

prakashdotc Ten Centuries Points: 1165 More actions · Answer 2

Hi Ninja,

Thanks for the quick response.

Since SP is very big, i was not able to post that SP.

Can u post a example of the method with simple lines if possible.

Thanks

Prakash.C

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

How long can a inventory update get???

You can always post the code as attachement if you want.

prakashdotc Ten Centuries Points: 1165 More actions · Answer 4

Hi Ninja,

The SP is already used for other Processes, so that it's not possible to make any modifications in the SP such as passing XML parameters etc.

Just we need to find a way to pass required input parameters through a select statement as specified above.

consider the SP contains the statement to update quantity in items table with reference to Item Code

create procedure UpdateQty

@ItemCode VARCHAR(10)

@Qty INT

AS

UPDATE ITEMS SET Quantity = @Qty WHERE ItemCode = @ItemCode

END

and i have to call this execute this sp as

EXEC UpdateQty

SELECT ItemCode, QtyBO FROM ITEMSPO INNER JOIN ..... <some conditions>

Thanks

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

Maybe I was not clear. COPY the sp and call it update inv BATCH or whatever you like and make the changes there.

There's no magic bullet for this one, it's a just a simple select / inner join / update.

prakashdotc Ten Centuries Points: 1165 More actions · Answer 6

Hi Ninja,

The Update SP is not simple, in previous post i just specified that for example. Actually the update SP has some 350 lines with wide logics in it.

So i need a way to Execute a SP passing parameters through a select statement

Thanks

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

Look you either do it right or you use a cursor to call that sp a couple 1000 times.

I garantee you that the cursor will be slow as hell.

Redoing it right might take you a day or 2 but that'll be the end of it.

With the cursor version, you'll be cursed with a slow system and complaints for the rest of your life.

Take your pick, but I know and told you what I'd do.

prakashdotc Ten Centuries Points: 1165 More actions · Answer 8

Thanks Ninja,

I already started doing this process using #temp table and While Loop as usual

Thanks for your time.

Gourav S SSC Enthusiast Points: 155 More actions · Answer 9

I don't know where you going to code this batch exec code but can you do this?

if within sql then

declare @execstatementsbatch nvarchar(max)

select @execstatementsbatch = ''

SELECT @execstatementsbatch = @execstatementsbatch + 'EXEC UpdateQty ' + ItemCode + ', ' + QtyBO + '; '

FROM ITEMSPO

INNER JOIN .....

<some conditions>

exec(@execstatementsbatch)

this isn't optimized way due to dynamic query i suppose, plus you have 4000/8000 char limit

if you going to execute your batch from front end

then you can build your batch in similar fashion as above (it will be quicker there)

i've seen somewhere on forums similar kind of code you mentioned in your first post

of directly selecting params into exec statement

not able to find it now

will keep you updated if i find it

vinothraj Ten Centuries Points: 1349 More actions · Answer 10

We can coalesce the input elements and use it in proc to get the data rather than using cursor. However answer to your question is No, because its unable to match parameters and column value.