Passing SQL Stored Procedure Entirety of Where Clause

Passing SQL stored procedure entirety of WHERE clause

The short answer is that you can't do it like this -- SQL Server looks at the contents of a variable as a VALUE. It doesn't dynamically build up the string to execute (which is why this is the correct way to avoid SQL injection attacks).

You should make every effort to avoid a dynamic WHERE as you're trying to do, largely for this reason, but also for the sake of efficiency. Instead, try to build up the WHERE clause so that it short-circuits pieces with lots of ORs, depending on the situation.

If there's no way around it, you can still build a string of your own assembled from the pieces of the command, and then EXEC it.

So you could do this:

DECLARE @mywhere VARCHAR(500)
DECLARE @mystmt VARCHAR(1000)
SET @mywhere = ' WHERE MfgPartNumber LIKE ''a%'' '
SELECT @mystmt = 'SELECT TOP 100 * FROM Products.Product AS p ' + @mywhere + ';'
EXEC( @mystmt )

But I recommend instead that you do this:

SELECT TOP 100 * 
    FROM Products.Product AS p 
    WHERE 
        ( MfgPartNumber LIKE 'a%' AND ModeMfrPartNumStartsWith=1)
    OR  ( CategoryID = 123 AND ModeCategory=1 )

Stored procedure with conditional Where clause in SQL Server

This can be done like:

SELECT intID, strQuestion, strAnswer, intCategory, intOrder
FROM tblA 
WHERE flgInd = @flgInd OR @flgInd IS NULL;

You can also use a CASE expression:

SELECT intID, strQuestion, strAnswer, intCategory, intOrder
FROM tblA 
WHERE CASE 
          WHEN flgInd = @flgInd THEN 1
          WHEN @flgInd IS NULL THEN 1 
          ELSE 0
       END = 1;

Is it possible to pass entire WHERE condition in stored procedure in MySQL 5.x?

Yes it's possible You can use prepared-statements for it, and build whole query as a string, but it's not an elegant way to do things...

also notice that:

• Yours queries should take advantage of parametrized prepared-statements, in case of SQL-Injection
• Even parametrized prepared-statements, are not fully "secure", and You should avoid that kind of DB programming

Optimize dynamic SQL stored procedure by reducing the unique query plans generated

Yes there is a way you can improve it, use the parameters correctly, rather than using string concatenation. Your method will generate a different query plan for every different combination of values of parameter, rather than just every different combination of parameters, which will generate orders of magnitude more query plans.

DECLARE @Sql nvarchar(max) = N'
Select ID, FirstName, FamilyName, MiddleName, MaidenName, Email
From Employees
Where DeletedOn Is Null
'
    + CASE WHEN @LastName <> '' THEN ' And FamilyName = @LastName' ELSE '' END
    + CASE WHEN @MiddleName <> '' THEN ' And MiddleName = @MiddleName' ELSE '' END
    + CASE WHEN @FirstName <> '' THEN ' And FirstName = @FirstName' ELSE '' END
    + CASE WHEN @ItemID > 0 THEN ' And ItemID = @ItemID' ELSE '' END;

EXEC sp_executesql
  @Sql,
  N'@LastName varchar(50), @MiddleName varchar(40), @FirstName varchar(50)',
  @LastName = @LastName,
  @MiddleName = @MiddleName,
  @FirstName = @FirstName;

And as Aaron Bertrand points out, this also fixes the issue where any parameter value containing a single quote (') will fail.

Beyond this however, as Aaron also mentions, the performance is most likely down to other issues such as indexing.

---

Related Topics