Dynamic query issue... - 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)

Dynamic query issue...

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-14 : 09:33:31

Hi,

I am encountering issue (Conversion failed when converting datetime from character string.) while processing this dynamic query below:

ALTER PROCEDURE [dbo].[test_proc]
(@iFromUTCDateTime DATETIME = NULL
, @iToUTCDateTime DATETIME = NULL)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @StartDate DATETIME
, @EndDate DATETIME
, @DbName VARCHAR(50)
, @Sql VARCHAR(MAX)
, @Sql1 NVARCHAR(MAX)
, @params NVARCHAR(MAX)

SET @SQL = ''
SET @DbName = msdb.dbo.AI_GET_PARAMETER('VIP_DB','DBName')

SELECT @StartDate = Isnull(@iFromUTCDateTime,CONVERT(VARCHAR,Getutcdate(),101))
, @EndDate = CASE
WHEN @iToUTCDateTime IS NULL THEN Dateadd(ms,-3,CONVERT(VARCHAR,@StartDate + 1,101))
WHEN @iToUTCDateTime < @StartDate THEN @StartDate
ELSE @iToUTCDateTime
END

IF Object_id('tempdb..##Info') IS NOT NULL
DROP TABLE ##Info

SELECT @Sql = 'SELECT UID, COUNT(* ) Dups
, ModuleMsn
, CallTime
, SID
, TYPE
INTO ##Info
FROM (SELECT h.uid, h.ModuleMsn
, h.CallTime
, Isnull(CASE ConnectType
WHEN 10 THEN CASE
WHEN (Num1xrttAttempts%2) = 0 THEN c.Sid1xrtt2
ELSE c.Sid1xrtt1
END
WHEN 11 THEN CASE
WHEN (NumCsdAttempts%2) = 0 THEN c.SidCsd2
ELSE c.SidCsd1
END
WHEN 20 THEN CASE
WHEN (NumAmpsAttempts%2) = 0 THEN c.SidAmps2
ELSE c.SidAmps1
END
ELSE 1
END,1) SID
, (CASE Connecttype
WHEN 10 THEN ''1xRTT''
WHEN 11 THEN ''CSD ''
WHEN 20 THEN ''AMPS ''
ELSE ''1xRTT''
END) TYPE
FROM ' + quotename(@DbName) + '.dbo.EventHeader h WITH (NOLOCK INDEX (IX2_EventHeader)) ' +
' JOIN ' + quotename (@DbName) +'.dbo.EventFV3CallData c WITH (NOLOCK INDEX (PK_EventFv3CallData)) ' +
' ON c.Eventuid = h.Uid
AND c.Connecttype IN (10,11,20)
WHERE h.Calltime BETWEEN Isnull('''+ @iFromUTCDateTime +''',CONVERT(VARCHAR,Getutcdate(),101)) AND CASE
WHEN ''' + @iToUTCDateTime + ''' IS NULL THEN Dateadd(ms,-3,CONVERT(VARCHAR, '''+@StartDate+''' + 1,101))
WHEN ''' + @iToUTCDateTime +''' < ''' + @StartDate +''' THEN ''' + @StartDate + '''
ELSE ''' + @iToUTCDateTime + '''
END ) t
GROUP BY UID, ModuleMsn,Calltime,SID,TYPE '

print @sql
execute (@sql) -- tried this
return
EXEC sp_executesql @sql -- even tried this

IF Object_id('tempdb..##Info') IS NOT NULL
DROP TABLE ##Info
END

Anyone please help...

Regards,
Sourav

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-14 : 10:02:45

I'm not sure if this is the problem, but..

You are converting @Startdate to a Varchar field and valuing/comparing it to a datetime...Try leaving it as Datetime itself.

@StartDate = Isnull(@iFromUTCDateTime,Getutcdate()) 

WHEN @iToUTCDateTime IS NULL THEN Dateadd(ms,-3,@StartDate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 11:08:43

you're using ## tables. are you aware of issues that this may cause if this procedure is executed by more than one user simultaneuosly?

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-14 : 12:19:06

@@vijayisonly

I am still getting this error
Conversion failed when converting datetime from character string.

@@visakh16

This is not used by users.. it is only used by a batch job..

So, the issue is still open.. Please help

Thanks,
Sourav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:06:33

is the error occuring while executing the procedure? how are you passing datevalues? in what format?

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-09-15 : 03:31:06

Yes, error comes while executing procedure..

This is how i execute the same...
EXEC test_proc '2009-08-07 00:00:00.000' , '2009-08-07 23:59:59.997'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources