Skip to main content

ISQL Vs SQLCMD - How they return value?!

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 /S MyServer /d MyDB /E

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

Popular posts from this blog

How to get SharePoint List or Library GUID via REST

Sometimes, you would need List or Library GUID to use that in some operation. In such cases, how do you get hold of GUID using REST API? There is a straight-forward end point which you can use: /_api/web/lists/getByTitle('ListTitle')/Id This will return the GUID of a List or Library. There is also another approach. However this approach works only if a List or Library contains at least one item. /_api/web/lists/getByTitle('ListTitle')/items This is a familiar end point which we use to fetch items of a List or Library. To get the GUID from the response, you would do: var listIDTemp = response.data.d.results[0].__metadata.id; var listID = listIDTemp.substring(listIDTemp.lastIndexOf("guid") + 5).split('\'')[0]; As you can see, we are doing string operations to fetch GUID from metadata.id. This contains information only if the response has at least one item.
Post a Comment
Read more

All about SharePoint List View Styles

Sometimes, there are out of the box features which we tend to ignore and later when we do apply, we are more than happy about the feature which is readily available in SharePoint. One such feature is List View Style. I never thought I would write a post on this. However, whenever I spoke about this with users, people were excited to see the result. That prompted me to write this post. Instead of getting into only theory part, I will basically take use cases where these styles can be applied and also touch up on on some minor limitations with certain style. When you are creating/modifying a List view, you will get an option to select View Style. As shown below, there are 8 options available and Default is always set if you ignore this style. List of View Styles I will take typical Contact List and Announcement List to explian about these styles. Let us go one by one. Default: This view, as name suggest, is the default style in a view. This is one of the widely seen style
1 comment
Read more

Get User Id using REST or JavaScript Object Model

Sometimes you would need to fetch the User Id based on either Login name or Email id. You would need User Id if you need to assign a user object to a people picker control or People/Group field. How do we get the Id based on Email or Login Name in client side development? We can achieve that using JavaScript Object Model or REST API. Let me share the first example using JavaScript Object Model (JSOM). var context = new SP.ClientContext.get_current(); this.user = context.get_web().ensureUser(loginName or Email); var o = { d: d, user: this.user }; context.load(this.user); context.executeQueryAsync(     Function.createDelegate(o, ensureUserSuccess),     Function.createDelegate(o, Fail) ); The above code fetches User Id for a given Login Name or Email Id. Interestingly, there is no equivalent endpoint available in REST! The Microsoft documentation talk about a endpoint but I could not get it working. So what is the way to get User Id using REST? You have to use the hidd
Post a Comment
Read more