Passing value from stored procedure to DOS script
-
Hi All,
Does anybody know how to pass a value from Stored Procedure to DOS script variable?
In my script I execute SQL stored procedure which returns a numeric value back to the script. I want to assign this value to a variable.
I figured out how to return the value to a file, and later parse it:
OSQL -
for /f "tokens=1" %%a in ('type jobs.txt') do ( set FAILEDJOB_CNT=%%a )
but, I think there should be a simpler way to do it, without writing to a file.
Does anybody know the correct syntax for DOS and St.Proc?
Appreciate your help.
-
Looks like your already on the right track.
Don't think theres's a way.
http://www.sqlteam.com/item.asp?ItemID=744
Perhaps write an app in an easy to use language like vb.
-
I'm assuming you want to run a .cmd or .bat script (or something similar).
If so then you can use xp_cmdshell, something like this:
declare @cmd varchar(100)
set @cmd='c:\bin\test.cmd ' + select something from somwhere where x=y
exec master.dbo.xpcmdshell @cmd
HTH
-
Oops! Sorry about the typo
-
You can try using EXIT in the SQL file
EXIT(set nocount on
DECLARE @ReturnCode INT
Exec YourStoreProc @retstatus=@ReturnCode output
select ISNULL(@ReturnCode,7))
In the cmd / bat file after the osql
use %ERRORLEVEL% variable to get the value of the Returncode in the bat file
I have this methodology implemented sucessfully, so this should solve your problem.
Cheer
-
Thank you Nitin,
It almost worked. I can see the returning value “1” in the DOS window when the script is executed, but still can not capture this value into the batch script variable. The retcd var displays -100 instead of expected 1 sent by SQL statement.
Here is a piece of .bat script:
OSQL -Usa –SMy_WKS -Psapwd -Q "EXIT(Exec test_db..usp_jobs_status 0)"
set retcd=%ERRORLEVEL%
echo %retcd%
The stored procedure looks like:
CREATE procedure usp_jobs_status
@OutVal smallint OUT
as
select @OutVal = count(*) from.....
return @OutVal
Any idea what I am missing here?
Thanks a lot for your help.
-
use select query in the exit block, then the value would be avalaible for
%errolevel% variable
osql -E -SMSHSRMNSUKD0311 -Q "EXIT(Declare @asd smallint Exec test_db..usp_jobs_status @OutVal=@asd OUTPUT select @asd)
echo %ERRORLEVEL%
Or insted modify your procedure to select query rather than using output variable
-
Thank you Nitin!
It worked!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply