Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execute Stored Procedure with Select Statement as Input Parameters Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, 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
Post #978755
Posted Wednesday, September 1, 2010 7:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:14 AM
Points: 21,385, Visits: 9,603
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.
Post #978773
Posted Wednesday, September 1, 2010 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, 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

Post #978779
Posted Wednesday, September 1, 2010 7:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:14 AM
Points: 21,385, Visits: 9,603
How long can a inventory update get???

You can always post the code as attachement if you want.
Post #978781
Posted Wednesday, September 1, 2010 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, 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






Post #978791
Posted Wednesday, September 1, 2010 8:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:14 AM
Points: 21,385, Visits: 9,603
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.
Post #978809
Posted Thursday, September 2, 2010 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, 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


Post #979482
Posted Thursday, September 2, 2010 7:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:14 AM
Points: 21,385, Visits: 9,603
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.
Post #979507
Posted Thursday, September 2, 2010 7:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, Visits: 66
Thanks Ninja,

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

Thanks for your time.
Post #979521
Posted Wednesday, February 2, 2011 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:54 AM
Points: 3, Visits: 20
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
Post #1057586
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse