ISQL is not supported in SQL Server 2005. SQLCMD is a replacement for ISQL utility. I don’t go in detail about SQLCMD. Instead I’m going to explain one big difference between these two utilities that require attention.
SQLCMD does not support passing of stored procedure return value to the exit value. Let me explain this with examples.
Consider a simple Stored Procedure.
CREATE PROC dbo.Sample
@Val INT
AS
BEGIN
IF @Val = 1
RETURN 1
ELSE
RETURN 0
END
This stored proc is self explanatory.
When this SP is called from ISQL, the RETURN value is passed to EXIT command of ISQL. That means we can capture this return value and process accordingly.
In case of SQLCMD, the RETURN value will not be passed to EXIT command. Because, SQLCMD expects SP to SELECT return value. That is, if we change SP as shown below, this works with SQLCMD.
CREATE PROC dbo.Sample
@Val INT
AS
BEGIN
IF @Val = 1
SELECT 1
ELSE
SELECT 0
END
Assume that you have batch files where you are calling many SPs using ISQL. And you have to change from ISQL to SQLCMD. Normally, in batch files the returned value is used for error handling. If you have huge number of SPs, then it would be a lot of effort to change each and every SP to return value using SELECT. Instead you can change batch files so that it’ll handle return value and no change is required in SP. Let us see an example here.
SET SP=dbo.Sample 2
SET SQL=
SET SQL=%SQL% DECLARE @Return INT
SET SQL=%SQL% SET @Return = -100
SET SQL=%SQL% EXEC @Return = %SP%
SET SQL=%SQL% SELECT @Return
CALL SQLCMD -Q "exit( %SQL% )" -b /
Here, you are executing SP using EXEC statement. Then RETURNed value is SELECTed and passed to EXIT command of SQLCMD. Actually you are executing following statements using SQLCMD.
DECLARE @Return INT
SET @Return = -100
EXEC @Return = dbo.Sample 1
SELECT @Return
Here, @Return is set to -100 before executing SP. SQLCMD returns -100 if any error encounters before selecting return value.
This way, you can keep all your existing SPs intact.
Comments
Post a Comment