Execute Stored Procedure with Select Statement as Input Parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Execute Stored Procedure with Select...

11 posts, Page 1 of 2

1 2 »»

Execute Stored Procedure with Select Statement as Input Parameters

Rate Topic

Display Mode

Topic Options

Author

Message

prakashdotc

Posted Wednesday, September 01, 2010 7:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 07, 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

Ninja's_RGR'us

Posted Wednesday, September 01, 2010 7:47 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357, Visits: 9,535

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

prakashdotc

Posted Wednesday, September 01, 2010 7:52 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 07, 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

Ninja's_RGR'us

Posted Wednesday, September 01, 2010 7:56 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357, Visits: 9,535

How long can a inventory update get???

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

Post #978781

prakashdotc

Posted Wednesday, September 01, 2010 8:04 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 07, 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

Ninja's_RGR'us

Posted Wednesday, September 01, 2010 8:18 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357, Visits: 9,535

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

prakashdotc

Posted Thursday, September 02, 2010 6:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 07, 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

Ninja's_RGR'us

Posted Thursday, September 02, 2010 7:21 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357, Visits: 9,535

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

prakashdotc

Posted Thursday, September 02, 2010 7:32 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 07, 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

Gourav S

Posted Wednesday, February 02, 2011 10:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 9:17 AM
Points: 3, Visits: 19

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions