CODEWITHSUNDEEP
sqlsql-servert-sql
S Nash
S Nash

Reputation: 2499

How to get rid of dynamic SQL (T-SQL)

I have a stored procedure that uses dynamic SQL. My company recently decided to get rid of dynamic code.

Here I greatly simplified it to explain my point; @PersonID and @MhnNum are parameters of the stored procedure:

Declare @sql Varchar(max)

Set @sql="Select from tableA p"

If @PersonID Is Not Null
    Set @sql = @sql + ' Where  p.[ID] = ' + cast(@PersonID  as varchar(12))      

If @MhnNum Is Not Null
    Set @sql = @sql + ' Where p.[MhnNum] = ' + '''' + cast(@MhnNum  as varchar(12)) + ''''

Is there an (easy) way to get rid of this dynamic SQL?

Right now my solution is to create 2 If's with repetitive code. There must be a more elegant way to do this.

Upvotes: 0

Views: 207

Answers (3)

Thom A
Thom A

Reputation: 95588

Honestly, Dynamic SQL is probably the way to go, as you have a catch-all query, just not your dynamic SQL. It's a huge injection risk. Parametrise the statements:

DECLARE @SQL nvarchar(MAX),
         @CRLF nchar(2) = CHAR(13) + CHAR(10);

 SET @SQL = N'SELECT *' + @CRLF +
            N'FROM TableA A' + @CRLF +
            CASE WHEN @PersonID IS NOT NULL THEN N'WHERE A.ID = @PersonID;'
                 WHEN @MhnNum IS NOT NULL THEN N'WHERE A.MhnNum = @MhnNum;'
            END;

EXEC sys.sp_executesql @SQL, N'@PersonID int, @MhnNum int', @PersonID, @MhnNum;

Using a non-dynamic approach will cause the caching of bad query plans, which isn't going to be desired.

If you must use non dynamic SQL (due to a pointless Company Policy, there is nothing wrong with Dynamic SQL if used correctly), then add OPTION RECOMPILE:

IF @PersonID IS NOT NULL AND @MhnNum IS NOT NULL
    THROW 68542, N'Both @PersonID and @MhnNum cannot be non-NULL values.', 11;

SELECT *
FROM TableA A
WHERE (A.ID = @PersonID OR @PersonID IS NULL)
  AND (A.MhnNum = @MhnNum OR @MhnNum IS NULL)
OPTION (RECOMPILE);

The THROW is in there, as your code will also error if you have 2 non-NULL values.

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82474

A simple combination of or and and can get you the same result:

Select *
From tableA
Where (@personId is null or id = @personId) 
And (@MhnNum is null or whnNum =@mhnNum)

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23797

(writing in a comment would be a mess)

You could check the parameters and act accordingly. ie:

Select from tableA p
where  (@PersonID IS NULL or p.[ID] = @PersonID) and
 (@MhnNum  IS NULL or p.[MhnNum] = @MhnNum);

Upvotes: 1

Related Questions