combine two SPs in SQL Server

Question

I am having two Stored Procedures of redundant code. The SPs will differ based on the table from which it is selected. I would like to combine those two SPs. Please help out.

The main SP is:

CREATE PROCEDURE [dbo].[spGetEmployeesBySearchString]            
-------------
-------------
@OtherListType_ID INT
@manager_employee_number VARCHAR(255)
-------------
-------------
DECLARE @IsGetFullTeamUnderManager bit=0
IF (@OtherListType_ID=3 AND @manager_employee_number IS NOT NULL)
    SET @IsGetFullTeamUnderManager = 1

IF (@IsGetFullTeamUnderManager=1)
BEGIN
    EXEC spFullTeamUnderManager <<Parameters>>
    RETURN
END

SELECT e.a,e.b,e.c,e.d,......
FROM Employee emp
INNER JOIN .....
WHERE ..........

The Second Stored Procedure is:

CREATE PROCEDURE [dbo].[spFullTeamUnderManager]            
-------------
-------------
-------------
SELECT e.a,e.b,e.c,e.d,......
FROM dbo.fnFullTeamUnderManager(@manager_employee_number) emp 
INNER JOIN .....
WHERE ..........

In the main SP, based on the value of @OtherListType_ID, I am calling the second SP. But the select statement, join conditions and the where conditions remains the same in both the Stored Procedures. I need to remove the redundant code. Please help out.

Damien_The_Unbeliever · Accepted Answer · 2013-03-19T08:37:08.393

1

You can't, generally, parameterise the rowset sources in a FROM clause. You could do the following and hope the optimizer is smart enough to do it right:

SELECT e.a,e.b,e.c,e.d,......
FROM (
 SELECT * FROM dbo.fnFullTeamUnderManager(@manager_employee_number) WHERE @IsGetFullTeamUnderManager=1
 UNION ALL
 SELECT * FROM Employee WHERE @IsGetFullTeamUnderManager=0
) emp 
INNER JOIN .....
WHERE ..........

If the function and Employee don't share exactly the same columns, you'll have to name the common columns explicitly instead of using SELECT * - some might insist that you should do that anyway, but today I'm in a "get it done" mood.

edited Mar 19 '13 at 08:37

answered Mar 19 '13 at 08:07

Damien_The_Unbeliever

234,701
27
340
448

I think it should be `UNION`; it will help remove duplicates. – Orson Mar 19 '13 at 08:17
@Damien : For example if the function returns only 12 rows and the employee table has 5000 rows and Suppose if I need the output from the function alone(consider second SP), then in that case also it will return all the rows from both condition and it will fail right? – prabu R Mar 19 '13 at 08:27
@ColourBlend - the `WHERE` clauses are mutually exclusive. – Damien_The_Unbeliever Mar 19 '13 at 08:35
1

@prabuR - The `WHERE` clauses are mutually exclusive - it's either selecting from the function or `Employee` but never both. As I said, hopefully the optimizer can see this and not actually perform pointless work, but the `WHERE` clauses should prevent it returning rows from both branches – Damien_The_Unbeliever Mar 19 '13 at 08:36
@Damien_The_Unbeliever Ok is see that now `WHERE @IsGetFullTeamUnderManager=0`. Better solution :) – Orson Mar 19 '13 at 14:15

score 0 · Answer 2 · answered Mar 19 '13 at 07:53

0

CREATE PROCEDURE [dbo].[spGetEmployeesBySearchString]            
-------------
-------------
@OtherListType_ID INT,
@manager_employee_number VARCHAR(255)
-------------
-------------
DECLARE @IsGetFullTeamUnderManager bit=0
IF (@OtherListType_ID=@FullTeam AND @manager_employee_number IS NOT NULL)
    SET @IsGetFullTeamUnderManager = 1

IF (@IsGetFullTeamUnderManager=1)
BEGIN    
    SELECT e.a,e.b,e.c,e.d,......
    FROM dbo.fnFullTeamUnderManager(@manager_employee_number) emp 
    INNER JOIN .....    
    WHERE ..........    
END

SELECT e.a,e.b,e.c,e.d,......
FROM Employee emp
INNER JOIN .....
WHERE ..........

answered Mar 19 '13 at 07:53

Aleksandr Fedorenko

16,594
6
37
44

Here also i need to repeat the entire select statement, join conditions and the where conditions. The main aim is to remove that redundancy since there are more number of conditions in the SP. Is there any other way? – prabu R Mar 19 '13 at 07:54
Could you give example of function and select statement with all condition, please? – Aleksandr Fedorenko Mar 19 '13 at 08:04

Orson · Answer 3 · 2013-03-19T08:16:28.557

CREATE PROCEDURE [dbo].[spGetEmployeesBySearchString]            
-------------
-------------
@OtherListType_ID INT
@manager_employee_number VARCHAR(255)
-------------
-------------

-- Me
DECLARE @SQL varchar(3000);
DECLARE @TableName varchar(30);
SET @TableName = 'Employee ';



DECLARE @IsGetFullTeamUnderManager bit=0
IF (@OtherListType_ID=3 AND @manager_employee_number IS NOT NULL)
    SET @IsGetFullTeamUnderManager = 1

IF (@IsGetFullTeamUnderManager=1)
BEGIN
    SET @TableName = 'dbo.fnFullTeamUnderManager(' + @manager_employee_number +') ';
END

SET @SQL = 'SELECT e.a,e.b,e.c,e.d
            FROM ' + @TableName + ' emp
            INNER JOIN .....
            WHERE a = ' + convert(varchar(4), @aNumber);

EXEC(@SQL);

You should also know there are better ways to execute dynamic sql statements.

Using EXEC SP_EXECUTESQL(@SQL) instead of EXEC(@SQL) is more likely to promote query plan reuse and improve security. In addition, using this approach you can ensure that the data values being passed into the query are the correct datatypes.

More Reading Execute Dynamic SQL commands in SQL Server

combine two SPs in SQL Server

3 Answers3