Live Situation #2 : Return value from Stored Procedure

Sometimes a problem sounds the damn easiest, but we get trapped into a really weird situation.

Because we just try to solve these problems by relating them with a similar concept. The technology resemblance is not so simple.
Problems may be as easier as to just add two numbers. There could be a number of technical approaches. Somebody just write a C function, somebody using pointers, somebody can play around the address values to display the same result value or someone using built-in API.

In this ORM world of programming (Object Relationship Mapping – Hibernate, Active Records etc.), the framework is giving a flexibility to a programmer to treat the entire database tuple (rows) as just like basic data types variable. And hence we over mistakes ourselves assuming they behave exactly.

Recently we had an issue with a Biztalk project around SQL Server.

Situation was like to retrieve the party name from one table in SQL Server. Later on based on the party name you need to populate another table.

currentParty = getPartyName();
if(null != currentParty ){
   // Populate the other table

We wrote a stored procedure pretty simple to above. But that did not work.
This is not the right place to explore each byte of our experiment; but finally what we concluded is – HOW TO CALL A SP, HOW TO PASS ARGUMENTS TO IT, HOW TO RETRIEVE PARAMETERS.

If you are a C#, Java, Ruby etc developers – You think this is pretty simple. But that’s not the case.
Don’ttreat the store procedure as normal functions. Syntactically stored procedures differs a lot.

1. Below is an example on – HOW TO RETURN VALUE from a SP

CREATE PROCEDURE doAddition_Using_ReturnValue
      DECLARE @retRes int;
      --SET @retRes  = exec getSum_As_ReturnValue 4,5
      exec @retRes = getSum_As_ReturnValue 4,5
      print @retRes

CREATE PROCEDURE getSum_As_ReturnValue
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int --// No special keyword for input paramaters
      declare @res int
      set @res = @n1 + @n2;
      return @res

Your SP call should be – exec doAddition_Using_ReturnValue;

So what a big deal here. The big deal is hereYour return statement is compatible with int data type only. What if you want to return a string value and capture into a variable? As in my case party name has to be a string.

The answer is using OUTPUT parameter and that happens to be using a “SELECT” in SP.

2. Below is an example on How to return value using SELECT as OPUTPUT parameter

CREATE PROCEDURE doAddition_Using_OutParameter
      DECLARE @outRes int;
      exec getSum_As_OutParameter 4,5, @outRes OUTPUT --// @outRes holds the returned value getSum_As_OutParameter having two in parameters 4,5
      print @outRes

CREATE PROCEDURE getSum_As_OutParameter
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int, @res int OUTPUT   // @res must come in a SELECT statement for returning purpose
      set @res = @n1 + @n2;
      SELECT @res

Special thanks to Mario for letting me know that SELECT and return are different in SP calls.
Thanks to Biztalk friends here – Sandeep, Som & Manish for trapping me into a silly(sorry toughest!) problem.

Live Situation #1 : Using a metachar in SQL query

Problem – Fetching the metachar from a database table in MSSQL Server
Scenario – In one of the Biztalk project closely executed by my friend had one issue in generating and processing an EDI file.

1. Extract member information data from SQL Server via SQLAdapter in Biztalk
2. Have some stored procedure calls
3. Create EDI and sends it to the send port

There were 18000 records in sql table and is expected to have an EDI of 18000 claims. Entire process was moving smoothly so far.
Suddenly Biztalk threw an error and it was difficult to predict what exactly the error is”.

He made a small change in SP to fetch only 100 records and run the entire process. He succeeded into it and could generate a proper EDI.
He changed the SP to 1000 then 2000, 3000, 4000. Worked well. But it failed in case of SP fetching 5000 records.

My dear friend suspected that there is a data issue between 4000 to 5000. But what exactly? After little normalization we felt it must be an issue of some wild chars (., *. # as they may appear in member address field).

So how can we identify them?

We planned to put a plan SELECT query as below
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘*’, address) > 0 //incorrect

This gives you a syntax error. We modified it by putting an escape char
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\*’, address) > 0 // incorrect

Modified to – escape the \ char as well
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\\*’, address) > 0 // incorrect

Then finally we stored the ASCII value of it in a variable and run it as below

Declare @findMetaChar nvarchar(5);
Set @findMetaChar = char(42)
SELECT top 9000 address FROM dataMembers
where CHARINDEX(@findMetaChar, address) > 0

This listed us 3 rows and observered as there were * chars in address field.
And in Biztalk, * we took as segment element separator. Hence this caused Biztalk to stop the processing.