MS SQL Dynamic stored procedure using a datetime variable

Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.

What I have is:

CREATE PROCEDURE busy_report

@.TableName varchar(255),
@.reporteddate datetime=NULL

AS
if @.reporteddate is null
select @.reporteddate = CURRENT_TIMESTAMP

-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SET DATEFORMAT dmy

-- Enter the dynamic SQL statement into the
-- variable @.SQLStatement
SELECT @.SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@.TableName + "WHERE reporteddate = ' "
+ @.reporteddate + " '"

-- Execute the SQL statement
EXEC(@.SQLStatement)
GO

The error I keep getting is:

Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.

Any ideas appreciated.

(Edit:)

I've also tried it this way:

CREATE PROCEDURE UK_busy_report

@.TableName varchar(255),
@.reporteddate datetime=NULL

AS

-- Create a variable @.SQLStatement
DECLARE @.SQLStatement varchar(255)
SELECT @.reporteddate=CONVERT(datetime, @.reporteddate)
IF @.@.ERROR <> 0 BEGIN

/* Do some error processing */

PRINT 'Error Occured' END

ELSE
-- Enter the dynamic SQL statement into the
-- variable @.SQLStatement
SELECT @.SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@.TableName + "WHERE reporteddate = ' "
+ @.reporteddate + " '"

-- Execute the SQL statement
EXEC(@.SQLStatement)
GO

Which gives me the same error!

.logic.Ahhh...the smell of Oracle....

CREATE PROCEDURE busy_report
@.TableName varchar(255)
, @.reporteddate datetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ @.reporteddate
+ ''''

EXEC(@.SQLStatement)
END
GO

It's been a while

If you want specifc date formats look up CONVERT in Books Online (BOL)|||It gives you the same error because it occurs on the last concatenation of @.SQLStatement.

"WHERE reporteddate = '" + convert(char(10), @.reporteddate, 101) + "'"|||Originally posted by rdjabarov
It gives you the same error because it occurs on the last concatenation of @.SQLStatement.

"WHERE reporteddate = '" + convert(char(10), @.reporteddate, 101) + "'"

Yeah...you're definetley going to need to worry about conversion...

What's the column reporteddate defined as?|||Originally posted by Brett Kaiser
Yeah...you're definetley going to need to worry about conversion...

What's the column reporteddate defined as?

Hi, the column is a smalldatetime type.

With your solution Brett I' getting a Server: Msg 295, Level 16, State 3, Procedure UK_busy_report, Line 11
Syntax error converting character string to smalldatetime data type.

Using:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate smalldatetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ @.reporteddate
+ ''''

EXEC(@.SQLStatement)
END
GO

I've also tried to convert it to nvarchar as follows:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate smalldatetime=NULL
AS
BEGIN
DECLARE @.SQLStatement varchar(255)

IF @.reporteddate IS NULL
SELECT @.reporteddate = GetDate()

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ convert (nvarchar(14), @.reporteddate, 101)
+ ''''

EXEC(@.SQLStatement)
END
GO

And I get: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

I think this conversion is the way to go but not sure of exact syntax. I'll keep checking through BOL and if anyone has any more ideas they'd be greatly appreciated :)

.logic.|||I've again altered it to:

CREATE PROCEDURE UK_busy_report
@.TableName varchar(255)
, @.reporteddate varchar(40)
AS
BEGIN
DECLARE @.SQLStatement varchar(255)
DECLARE @.date datetime


SELECT @.reporteddate=CONVERT(datetime, @.date, 103)
IF @.@.ERROR <> 0

BEGIN
Print 'ERROR'
END

ELSE

SELECT @.SQLStatement = 'SELECT vendor, reporteddate, count(vendor) FROM '
+ @.TableName + 'WHERE reporteddate = '
+ ''''
+ convert (nvarchar(14), @.reporteddate, 101)
+ ''''

EXEC(@.SQLStatement)
END
GO

I'm running it with: exec UK_busy_report EU_master_week6, '02/02/04';

And it seems to be running fine but it doesn't return any info, even though I know that date exists in the table.