Exec Stored Proc

I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnNamenvarchar (50),
@.dtDate1 Date,
@.dtDate2 Date
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ' + ''' + @.dtDate1 + ''' + ' AND ' + ''' + @.dtDate2 + '''')i havent tried it but check if this works:

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName +
 ' BETWEEN ' + '''' + @.dtDate1 + '''' + ' AND ' + '''' + @.dtDate2 + ''''

hth|||Try this:

ALTER PROCEDURE dbo.GetSearchByDateRange

(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as

EXEC ('SELECT * FROM Customers WHERE ' + @.strColumnName + ' BETWEEN ''' + @.dtDate1 + ''' AND ''' + @.dtDate2 + '''')|||How many possible values for @.strColumnName could there be? For performance and code reliability reasons I would write it like below. That way the SQL is precompiled (allowing the db engine to skip a step during execution and also allowing syntax errors to be spotted at the time of sp creation instead of execution.

ALTER PROCEDURE dbo.GetSearchByDateRange
(
@.strColumnName nvarchar (50),
@.dtDate1 DateTime,
@.dtDate2 DateTime
)
as
 if (@.strColumnName = 'Birthdate')
 begin
 SELECT * FROM Customers
 WHERE Birthdate BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 if (@.strColumnName = 'LastPayment')
 begin
 SELECT * FROM Customers
 WHERE 'LastPayment'BETWEEN @.dtDate1 AND @.dtDate2
 return 0
 end
 ETC...

|||Thank you all for the infos.
Corbi I think you had a good point there and I will definatly take it in consideration.

Oh last thing how do I control the injection of a date from my code to the store proc if my db is using small dates? I see that it can cause problem if I don't send the right format to the sp.
Thanks again|||If possible, i would change the stored procedure parameters to "smalldatetime", process the different date formats in your application and convert your userinput to "smalldatetime" there.

Hth,

Moon|||the only things that need to be considered in the Execute(Whatever Valid SQL Query in string format) is to have double single qoutes for each date value in the query, and that those date values are converted to a valid string (this is because you need to concatenate a string, so try this

Declare @.SQLString As VarChar(8000)

Set @.SQLString = 'SELECT * FROM Employees WHERE ' + @.strColumnName + ' BETWEEN ''' + Convert(Char(8), @.dtDate1, 112) + ''' AND ''' + Convert(Char(8), @.dtDate2, 112) + ''''

Exec (@.SQLString)

This will Work fine

Delfino III Salinas Sepúlveda
DSS Hi-Tech, México
diiisalinas@.prodigy.net.mx|||Also use DateTime or smalldatetime types.