Compare date in stored procedure in an exec statement

Question

I have a stored procedure that looks like this:

create stored procedure aaa 
     @columnName nvarchar(10), 
     @comparisonParam nvarchar(10),
     @val nvarchar(100) 
as
    declare @date date
    set @date = convert(@val, date)

    exec('select * from Sheep where ' + @columnName + @comparisonParam  + @date )

When actually the query is supposed to be like this:

select * from Sheep where birth_date = 12-12-2000

When I run the procedure it doesn't work with date value, but with string and int it works.

Answer 1

The date value must be quoted.

On a side note, I'd warn against doing this. If you need to build up dynamic sql you need to consider the risks such as: sql injection attacks, bad syntax, invalid semantics etc.

Consider using an existing component to build the query. A few examples:

.NET LINQ (to SQL/Entities) http://msdn.microsoft.com/en-us/library/bb397926.aspx

.NET SqlCommandBuilder http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx


See Best way of constructing dynamic sql queries in C#/.NET3.5?

Answer 2

Your date literal needs to be surrounded in single quotes (I use CHAR(39) usually since it is easier to read and doesn't require escaping). Otherwise you are saying:

WHERE birth_date = (12) - (12) - (2000)

Which resolves to:

WHERE birth_date = -2000

Which resolves to DATEADD(DAY, -2000, '1900-01-01') or:

WHERE birth_date = '1894-07-11'

This is probably not going to yield the results you want.

With typical SQL injection warnings in place of course, and assuming that @columnName is always a string or date/time column, here is how I would re-write your stored procedure (though I would probably try to avoid the dynamic SQL altogether if I could).

ALTER PROCEDURE dbo.aaa 
  @columnName       NVARCHAR(10), 
  @comparisonParam  NVARCHAR(10),
  @val              NVARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT * FROM dbo.Sheep WHERE '
    + QUOTENAME(@columnName) + @comparisonParam + CHAR(39) 
    + REPLACE(@val, CHAR(39), CHAR(39) + CHAR(39)) 
    + CHAR(39);

  EXEC sp_executesql @sql;
END
GO

In order to thwart potential issues you may want to add validation for columns and data types, and ensure that the operation is one you expect. e.g.

CREATE PROCEDURE dbo.bbb
  @columnName       NVARCHAR(10), 
  @comparisonParam  NVARCHAR(10),
  @val              NVARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @delimiter CHAR(1);

  SELECT @delimiter = CASE 
    WHEN [system_type_id] IN 
      (104,48,52,56,127,59,60,62,106,108,122) THEN '' -- numeric
    WHEN [system_type_id] IN 
      (35,40,41,42,43,58,61,99,167,175,231,239) THEN CHAR(39) -- string
    END FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.Sheep')
    AND name = @columnName;

  IF @delimiter IS NULL
  BEGIN
    RAISERROR('Column ''%s'' was not found or an unexpected data type.', 11, 1, 
      @columnName);
    RETURN;
  END

  IF @comparisonParam NOT IN (N'=', N'>=', N'<=', N'<', N'>', N'LIKE')
  BEGIN
    RAISERROR('Comparison param ''%s'' was not valid.', 11, 1, @comparisonParam);
    RETURN;
  END

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'SELECT * FROM dbo.Sheep WHERE '
           + QUOTENAME(@columnName) + ' ' + @comparisonParam + ' ' 
           + @delimiter + REPLACE(@val, CHAR(39), CHAR(39) + CHAR(39)) 
           + @delimiter;

  EXEC sp_executesql @sql;
END
GO

Now make sure you use an unambiguous date format for your string literals. 12-12-2000 is not a good choice. 20001212 is much better.

There are possibly some ways to do this without dynamic SQL - I gave a very simplified answer here. This may be feasible depending on the data types, the number of potential columns, and the number of operations you want to support.

Answer 3

Build your dynamic SQL using a typed date parameter. Use sp_executesql which allows to pass parameter definitions and parameter values to the embedded SQL:

create procedure aaa 
   @columnName nvarchar(10), 
   @comparisonParam nvarchar(10),
   @val nvarchar(100)
as
    declare @date date, @sql nvarchar(max);
    set @date = convert(@val, date);

    -- Note how @date is a *variable* in the generated SQL:
    set @sql =N'select * from Sheep where ' + 
            quotename(@columnName) + @comparisonParam  + N'@date';

    -- Use sp_executesql and define the type and value of the variable
    exec sp_executesql @sql, N'@date date', @date;

Answer 4

create stored procedure aaa 
     @columnName nvarchar(10), 
     @comparisonParam nvarchar(10),
     @val nvarchar(100) 
as
    declare @date date
    set @date = convert(@val, date)

    exec('select * from Sheep where ' + @columnName + @comparisonParam  + @date )

Answer 5

You need to create table valued function for this rather than creating a stored procedure.

You can use any table valued function like

SELECT * from dbo.CallMyFunction(parameter1, parameter2

eg.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

See this for reference http://msdn.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

EDIT

Instead of using dynamic sql try giving a thought on

SELECT * FROM 
FROM    [dbo].[Person]
WHERE   ([PersonID] = @PersonID
         OR @AreaID IS NULL
        )
        AND (([Code] BETWEEN @Code AND CHAR(255))
             OR @Code IS NULL
            )
        AND (([Name] BETWEEN @Name AND CHAR(255))
             OR @Name IS NULL
            )
        AND (([Notes] BETWEEN @Notes AND CHAR(255))
             OR @Notes IS NULL
            )