Communicate the logic used in a stored procedure to the calling C# application

Question

I have a stored procedure being called from a C# application. This stored procedure has two mutually exclusive code paths; They both return similar type of records, like this:

IF x=y
  SELECT colX, colY
  FROM TableA
ELSE
  SELECT colX,colY
  FROM TableA
  WHERE colX > 100

The task on hand involves writing the result of the sp call in a text file; the file's name depends on which of the two SELECT was executed. The name will be MyTextFile.txt or MyTextFileGT100.txt

How do I tell the C# app which of the two SELECT statements was executed?

Answer 1

Adding another column is the wrong way to go -especially with larger result-sets. You'd be increasing data over the wire by polluting every single record with what should be a one-time value.

That said, I'd suggest an optional output parameter for your stored procedure:

@branchId int = null output

Then set it within your logic blocks:

if x=y begin

  set @branchId = 1

  SELECT colX, colY
  FROM TableA

end else begin

  set @branchId = 2

  SELECT colX,colY
  FROM TableA
  WHERE colX > 100

end

This solution prevents changes to your result-sets and, provided that you always explicitly name procedure parameters, shouldn't affect any code. Further, you gain the added benefit of less data over the wire than the add a column option.

Answer 2

You could use an output parameter in the stored proc and use that to decide the name of the text file. See the links below for more info;

http://www.daniweb.com/software-development/csharp/threads/110318

Get output parameter value in ADO.NET

Answer 3

IF x=y
  SELECT colX, colY, 'case1' as WhichBranch
  FROM TableA
ELSE
  SELECT colX, colY, 'case2' as WhichBranch
  FROM TableA
  WHERE colX > 100

Answer 4

You could do something like this:

IF x=y
  SELECT colX, colY, 'Method1' as method
  FROM TableA
ELSE
  SELECT colX,colY, 'Method2' as method
  FROM TableA
  WHERE colX > 100

Answer 5

Extend the Stored Procedure like this:

IF x=y
  SELECT colX, colY, 'MyTextFile.txt' AS FN
  FROM TableA
ELSE
  SELECT colX,colY, 'MyTextFileGT100.txt' AS FN
  FROM TableA
  WHERE colX > 100