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.
wstevens@.vodamail.co.za: