SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute Stored Procedure with Select Statement as Input Parameters


Execute Stored Procedure with Select Statement as Input Parameters

Author
Message
prakashdotc
prakashdotc
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 66
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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29715 Visits: 9671
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
prakashdotc
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 66
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
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29715 Visits: 9671
How long can a inventory update get???

You can always post the code as attachement if you want.
prakashdotc
prakashdotc
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 66
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
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29715 Visits: 9671
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
prakashdotc
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 66
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
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29715 Visits: 9671
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
prakashdotc
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 66
Thanks Ninja,

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

Thanks for your time.
Gourav S
Gourav S
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 23
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search