CODEWITHSUNDEEP
sqlstored-procedurescommand-linecmd
chama
chama

Reputation: 6163

How do I capture a returned value from a stored procedure using windows command script?

I am writing a command script that calls a stored procedure. That stored procedure returns a value that I need later in the script. Is there any way for me to capture that value into a variable of some sort?

Thanks in advance!

Upvotes: 1

Views: 2865

Answers (3)

Ric
Ric

Reputation: 21

In windows CMD shell, this can be done with a FOR loop on a sqlcmd call. Usually only if the query returns 1 record.

FOR /F %%A IN ('sqlcmd <query paramters>') DO SET MYCMDVARIABLE=%%A

It can be configured for multiple columns, but will always return values from the last row in the SQL return set.

Upvotes: 2

Cahit
Cahit

Reputation: 2534

If you're dealing with SQL 2005 and above, try using sqlcmd instead.

Check out all the goodies it provides that you can use with scripting: MSDN Page for sqlcmd command

Upvotes: 0

Andrea Turri
Andrea Turri

Reputation: 6500

I used the following code to call a stored procedure and retrieve its return value. The code creates a temporary stored procedure that takes two input parameters and returns their sum via the return value. If your stored procedure executes queries, make sure you're processing all available results via sqlsrv_next_result before trying to access the return value.

I hope this information can help you..

Code is below:

$conn = sqlsrv_connect('.\SQLExpress', array('Database'=>'Northwind'));

$sql = "CREATE PROCEDURE #ReturnParam (@p1 int, @p2 int) AS
        RETURN @p1 + @p2";
$stmt = sqlsrv_query($conn, $sql);

$sql = '{? = CALL #ReturnParam(?, ?)}';
$returnVariable = -1;
$inputVariable1 = 18;
$inputVariable2 = 24;

$params = array(array($returnVariable, SQLSRV_PARAM_OUT),
                array($inputVariable1, SQLSRV_PARAM_IN),
                array($inputVariable2, SQLSRV_PARAM_IN));

$stmt = sqlsrv_query($conn, $sql, $params);

echo "Return value: ".$returnVariable;

Upvotes: 0

Related Questions