SQL Server stored procedure issue calling another stored procedure

Question

Here's a issue I have with a stored procedure (using SQL Server 2005), inside this stored procedure it calls another stored procedure putting the data into a temp table.

INSERT INTO #tmpTable (Column1, Column2, Column3) 
EXEC psp_rptInsideStoredprocedure 2

This inside stored procedure has a mode parameter that determines which columns get passed out. In this mode (Mode2) only 3 columns get passed out, when this inside stored procedure is used for another report (Mode1) 4 columns gets passed out. Sometimes the parent stored procedure complains about trying to insert the 4 column and sometimes not.

I know it's always passing in mode 2 but it's like SQL Server knows that sometimes this stored procedure has passed back 4 columns.

Any thoughts on a solution?

Thanks

Don

Answer 1

Daisy chaining stored procedures is generally not a great idea. I would remove the call to the sproc and type out the t-sql for exactly what you need. If your really set on calling another sproc. Make a new sproc that does exactly what you need for this one situation.

It's all about the Single Responsibility Principle

Answer 2

Make the child procedure always return the same number and type of columns, use NULL if necessary. If the results are so different for the two versions, that you can't combine them like this, you should consider making two different procedures.

I'm just guessing at your procedure, but you could create a #ResultSet temp table and populate it based on your type parameter, but always return all columns, etc. If you are returning lots of rows this becomes bad, as the temp table is overhead. in that case, just make sure your result sets return the same number of columns:

IF @version=1
BEGIN
    SELECT col1, col2, NULL FROM ... WHERE ...
END
ELSE
BEGIN
    SELECT col1, col2, col3 FROM ... WHERE ...
END

Then when both parents call it, accept all the columns, but they can ignore what they don't need.

Answer 3

msh faheeeeeeeeeeeeeeem :D