SQL Server存储过程问题调用另一个存储过程

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.

这是我对存储过程(使用SQL Server 2005)的一个问题,在这个存储过程中,它调用另一个存储过程将数据放入临时表。

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.

这个内部存储过程有一个mode参数,用于确定哪些列被传递出去。在此模式(模式2)中,只有3列被传出,当此内部存储过程用于另一个报表(模式1)时,4列被传递出去。有时,父存储过程会抱怨尝试插入4列,有时不会。

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.

我知道它总是在模式2中传递,但它就像SQL Server知道有时这个存储过程已经传回4列。

Any thoughts on a solution?

有关解决方案的任何想法?

Thanks

Don

2 个解决方案

#1

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.

使子进程始终返回相同数量和类型的列,必要时使用NULL。如果两个版本的结果如此不同,你不能像这样将它们组合起来,你应该考虑制作两个不同的程序。

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:

我只是猜测你的程序,但你可以创建一个#ResultSet临时表并根据你的类型参数填充它,但总是返回所有列,等等。如果你返回很多行,这会变得很糟糕,因为临时表是开销。在这种情况下,只需确保您的结果集返回相同数量的列:

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.

然后,当父母双方都打电话给他们时,接受所有列,但他们可以忽略他们不需要的内容。

#2

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.

菊花链存储过程通常不是一个好主意。我将删除对sproc的调用并输出t-sql以确切地提供所需内容。如果你真的打电话给另一个sproc。制作一个新的sproc,它可以完全满足您的需求。

It's all about the Single Responsibility Principle

这完全是关于单一责任原则

#1