Problem in stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in stored Procedure

Author

Topic

kalit
Starting Member

2 Posts

Posted - 2011-01-10 : 01:54:45

Getting Error:
Msg 241, Level 16, State 1, Procedure GetMaxCostCenterValue, Line 15
Conversion failed when converting date and/or time from character string.

Stored Procedure:

ALTER PROCEDURE GetMaxCostCenterValue
(
@CostCenter nvarchar(225),
@DateFrom datetime,
@DateTo datetime
)
AS
SET NOCOUNT ON
BEGIN

DECLARE @Query NVARCHAR(525)
DECLARE @pn_CostCenter NVARCHAR(525)

SET @Query = N'SELECT
dbo.Formatdate(m.Month) As Financial_Month
, d.Dept_name
,m.'+@CostCenter+'
FROM
tblMasterTable m
INNER JOIN tbldept d
ON d.Dept_ID = m.dept
Where
'+@CostCenter+' =
(SELECT MAX('+@CostCenter+') from tblMasterTable)
AND m.Month between '+@DateFrom+' and '+@DateTo+''

Print(@DateFrom)
Print(@DateTo)
PRINT(@Query)

EXECUTE sp_executesql @Query, N'@CostCenter varchar(255), @DateFrom datetime, @DateTo datetime',
@CostCenter, @DateFrom, @DateTo

END

-----

EXECUTING SP

DECLARE @DateTo DATETIME
DECLARE @DateFrom DATETIME

SET @DateTo = GETDATE()
SET @DateFrom = DATEAdd(DAY,-221, @DateTo)

EXEC GetMaxCostCenterValue NetProfit, @DateFrom, @DateTo

kalit
Starting Member

2 Posts

Posted - 2011-01-10 : 02:20:54

SOLVED:

ALTER PROCEDURE GetMaxCostCenterValue
(
@CostCenter nvarchar(225),
@DateFrom datetime,
@DateTo datetime
)
AS
SET NOCOUNT ON
BEGIN

DECLARE @Query NVARCHAR(525)
DECLARE @pn_CostCenter NVARCHAR(525)

SET @Query = N'SELECT
dbo.Formatdate(m.Month) As Financial_Month
, d.Dept_name
,m.'+@CostCenter+'
FROM
tblMasterTable m
INNER JOIN tbldept d
ON d.Dept_ID = m.dept
Where
'+@CostCenter+' =
(SELECT MAX('+@CostCenter+') from tblMasterTable)
AND m.Month between '''+cast(@DateFrom as varchar(101))+''' and '''+cast(@DateTo as varchar(101))+''''

Print(@DateFrom)
Print(@DateTo)
PRINT(@Query)

EXECUTE sp_executesql @Query, N'@CostCenter varchar(255), @DateFrom datetime, @DateTo datetime',
@CostCenter, @DateFrom, @DateTo

END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-10 : 02:48:13

Your procedure is vulnerable to SQL Injection. I hope you check your inputs before calling the proc

Also, as far as I can tell, there's no need for passing the parameters to sp_executeSQL. You're concatenating the values into the string, so a simple EXEC sp_executesql @Query will work.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources