How to handle sql server stored procedure output parameter in .bat file

Question

Post reply

How to handle sql server stored procedure output parameter in .bat file

VENUGOPAL.RR

Ten Centuries

Points: 1395
More actions
January 20, 2012 at 5:04 am

#254028

Hi,
I have to run two procedures using .bat file.
To run second procedure i need output patameter of first procedure.
Please help me in this regard.
DECLARE @P1 INT, @P2 INT
--SETTING VALUES TO VARAIBLES
EXEC SP1 @P1, @P2 OUT
EXEC SP2 @P2
how to run this script in batch file.
Regards,
Venugopal.rr

Viewing 8 posts - 1 through 7 (of 7 total)

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

TedT Right there with Babe Points: 796 More actions · Answer 1

Why do you need to execute these in a batch file?

Perhaps SSIS might be a better solution?

For the sake of simplicity, you may want to nest your stored procs (not necessarily best practise!!) but this will avoid you having to call both from the batch file.

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

VENUGOPAL.RR Ten Centuries Points: 1395 More actions · Answer 2

By using SSIS we can do, but i want to do it using .bat file

Based on value of first procedure output parameter, I will decide to execute second procedure.

DECLARE @P1 INT, @P2 INT

--SETTING VALUES TO VARAIBLES

EXEC SP1 @P1, @P2 OUT

IF @P2 = 1

EXEC SP2 @P1

sturner SSC-Insane Points: 22411 More actions · Answer 3

you can write a small .net wrapper program to execute the procedure and return the output param as an exit code (assuming it is an integer).

Another option is to modify the the stored procedure to write the output param to a table that can be queried from the batch file.... or, call the procedure from another procedure that writes to a table.

The probability of survival is inversely proportional to the angle of arrival.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

See if this helps:

http://www.sqlservercentral.com/Forums/Topic1188295-392-1.aspx

-- Gianluca Sartori

VENUGOPAL.RR Ten Centuries Points: 1395 More actions · Answer 5

Thanks for your reply,

sqlcmd -s servername -q "exit(exec testproc)"

@echo %errorlevel%

stored procedure returns an integer value with select statement

ex: select -1; return;

when i run the procedure using .bat file(bat file script given as above), i am getting errorlevel as 1.

for any other value i am getting as specified in the procedure. if i return 100 i am getting 100.

but for -1 i am getting 1. why i am getting the value different for -1.

I need the reason why it behaving differently for -1 only.

Regards,

venugopal.rr

Lowell SSC Guru Points: 323518 More actions · Answer 6

well, SQL server has no problem returning negative numbers...maybe it's a command line limitation?

CREATE PROCEDURE checkstate @param int

AS

return @param;

GO

DECLARE @return_status int;

EXEC @return_status = checkstate -1;

SELECT 'Return Status' = @return_status;

GO

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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Interesting, I didn't know that a negative %errorlevel% was possible.

-- Gianluca Sartori