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
AS
BEGIN
      DECLARE @retRes int;
      --SET @retRes  = exec getSum_As_ReturnValue 4,5
      exec @retRes = getSum_As_ReturnValue 4,5
      print @retRes
END
GO

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

Your SP call should be – exec doAddition_Using_ReturnValue;

So what a big deal here. The big deal is here – Your 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
AS
BEGIN
      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
END
GO

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
AS
BEGIN
      set @res = @n1 + @n2;
      SELECT @res
END
GO

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.