Rewrite the select statement without using dynamic sql-CodePudding

I have a procedure with the following statements:

Create procedure usp_personCountry
@inPersonId Int,
@inType varchar(100)
As
Begin

If @inType = 'Admin'
Begin
  Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Primary'
End
Else If @inType = 'Manager'
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryManager pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Manager'
End
Else If @inType = 'User'
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryUser pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'User'
End
Else
Begin 
 Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  Inner Join dbo.personCountryTeam pcm
    on pa.personId = pcm.personId
 Where p.personId = @inPersonId
  and pa.type     = 'Team'
End

End /* End of procedure */

In the procedure above, the select statement is identical for all conditions. Every time I have to add a new column, I have to add it all the 4 statements and there is a high possibility that I might end up missing adding a column to one of the statements at some point. Is there a way to rewrite this sql query so that the select statement is used only once and based on the type passed in we can construct the query? I want to accomplish this without using dynamic sql. I was wondering if there is any way of doing it. Thank you!

CodePudding user response：

You can put if conditionals inline, for example using a CASE expression. However I do question the point of the distinct. Anyway you can do this with a little less repetition like this, but it can be quite wasteful:

  Select /* Distinct -- why? */ p.personid, 
    p.name, p.email, pc.country, pa.attributeId
  INTO #blat From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
 Where p.personId = @inPersonId
  and pa.type     = CASE @inType
                    WHEN 'Admin'   THEN 'Primary'
                    WHEN 'Manager' THEN 'Manager'
                    WHEN 'User'    THEN 'User'
                    ELSE                'Team' END;

IF @inType = 'Admin'
BEGIN
  SELECT * FROM #blat;
END

IF @inType = 'Manager' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryManager WHERE personId = b.personId
  );
END 

IF @inType = 'User' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryUser WHERE personId = b.personId
  );
END 

IF @inType = 'Team' 
BEGIN
  SELECT * FROM #blat AS b WHERE EXISTS 
  (
    SELECT 1 FROM dbo.personCountryTeam WHERE personId = b.personId 
  );
END

Also I suspect it may be the case that as these tables get bigger and if there is any data skew at all, dynamic SQL is actually a better option.

DECLARE @sql nvarchar(max) = N'SELECT 
    p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId';

SET @inType = CASE @inType WHEN 'Admin' THEN 'Primary' ELSE @inType END;

IF @inType <> 'Primary'
BEGIN
  SET @sql  = N'
    Inner Join dbo.'   QUOTENAME(N'personCountry'   @inType)   ' pcm
      on pa.personId = pcm.personId';
END

SET @sql  = N'
  Where p.personId = @inPersonId
  and pa.type     = @inType';

EXEC sys.sp_executesql @sql, 
  N'@inPersonId int, @inType varchar(100)',
    @inPersonId,
    @inType;

CodePudding user response：

This could be done, but the result would be a very, very convoluted query that would perform poorly. If (when) the tables get big, it could be a significant drain on server resources.

Dynamic SQL is an option, but here I think it's use would be clever, not smart (for "clever" is quite often the enemy of "smart"). The queries are sufficiently distinct that I'd suggest making a separate stored procedure for each situation (@inType value), and either call them directly from the application or use this procedure as a wrapper to call the appropriate stored procedure.

There might be an improvement made to the underlying table design? Can't tell with the information we have.

Below is the code I came up with--it is NOT a complete solution. I got up to the part that addresses the extra joins, and that logic is where it gets too confusticated to be a reasonable query.

--  Reset, to simplify logic
If @inType = 'Admin'
    SET @inType = 'Primary'


  Select Distinct p.personid, p.name, p.email, pc.country, pa.attributeId
  From dbo.person p
  Inner Join dbo.personCountry pc
    on p.personId = pc.personId
  Inner Join dbo.personAttribute pa
    on p.personId = pa.personId
  --  from 0 or 1 INNER JOIN to 3 left outer joins (and change alias)
  Left Outer Join dbo.personCountryManager pcm
    on pa.personId = pcm.personId
  Left Outer Join dbo.personCountryUser pcu
    on pa.personId = pcu.personId
  Left Outer Join dbo.personCountryTeam pct
    on pa.personId = pct.personId
 Where p.personId = @inPersonId
  --  Now, presumes parameter matches pa-type
  --  This is a problem if that final "else" is not always "Team"
  and pa.type = @inType

The above makes some assumptions regarding parameter @inType, and still has to factor in a check for "if @inType is X and the one relevant left outer joined table found an entry", and that's when it gets seriously ugly.