Query fails if data has a number in dynamic sql

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)

Query fails if data has a number in dynamic sql

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-12-03 : 00:12:27

Ok here is an edit as this gets even stranger. If the data for @eventtype and data in eventtype field is

It only seems to fail if it has an 8?:

ETHIO 8 PRESENCE FLT > fails
ETHIO h8 PRESENCE FLT > works
ETHIO 8 8 PRESENCE FLTI > works
ETHIO 9 PRESENCE FLT > works
VIC H01 PRESENCE FLT > works
ETHIO 7 DIGITAL INPUT E001 > works
ETHIO 7 PRESENCE FLT > works

I have two queries here both the same other than one used dynamic sql? Why does the dynamic sql one pass if the data for the eventtype is alpha but fail if it has numeric in nvarchar? Note these procedures also use two functions. Error is:
Msg 8114, Level 16, State 1, Line 7
Error converting data type nvarchar(max) to int.

Non dynamic sql that works:

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Event_Faults_test_AD]
@SiteID = N'98',
@TransmitterType = N'fasttrace',
@EventType = N'ETHIO 8 PRESENCE FLT',
@DateRange = N'current year'

SELECT 'Return Value' = @return_value

GO

Steve FastTrace 4 South Wing 13466 ETHIO 8 PRESENCE FLT FLT 4 4 Input 2008-01-01 00:00:00 2008-01-01 00:00:00

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Event_Faults_test_AD]

@SiteID nvarchar(max),
@TransmitterType nvarchar(max),
@EventType nvarchar(max),
@DateRange nvarchar(30)

AS

SET NOCOUNT ON

Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime

SET @StartDateRange = (Select StartDateRange From fn_DateSel (@DateRange))
SET @EndDateRange = (Select EndDateRange From fn_DateSel (@DateRange))

BEGIN
SELECT SiteName, TransmitterType, CameraNumber, CameraName, EventID, dbo.EventTypeConv ([EventType]) As EventType, Description, Priority, SourceID, dbo.SourceTypeConv ([SourceType]) As SourceType, CONVERT(VARCHAR(19), [EventStartTime], 120) AS [EventStartTime], CONVERT(VARCHAR(19), [EventEndTime], 120) AS [EventEndTime]
FROM EventDetails INNER JOIN
SiteDetails ON EventDetails.SiteID = SiteDetails.SiteID
WHERE EventDetails.EventStartTime >= @StartDateRange AND EventDetails.EventStartTime < @EndDateRange
AND SiteDetails.SiteID IN (@SiteID)
AND SiteDetails.TransmitterType IN (@TransmitterType)
AND dbo.EventTypeConv (EventType) IN (@EventType)
AND (SiteDetails.IsDeleted = 'False')
ORDER BY SiteName, EventStartTime DESC

END

SET NOCOUNT OFF

Return

Dynamic sql that fails, if I remove the 8 from the data it runs?:

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Event_Faults_AD]
@SiteID = N'98',
@TransmitterType = N'fasttrace',
@EventType = N'ETHIO 8 PRESENCE FLT',
@DateRange = N'current year',
@DBName = N'vc'

SELECT 'Return Value' = @return_value

GO

Msg 8114, Level 16, State 1, Line 7
Error converting data type nvarchar(max) to int.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Event_Faults_AD]

@SiteID nvarchar(max),
@TransmitterType nvarchar(max),
@EventType nvarchar(max),
@DateRange nvarchar(30),
@DBName nvarchar (max)

AS

SET NOCOUNT ON

Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime

SET @StartDateRange = (Select StartDateRange From fn_DateSel (@DateRange))
SET @EndDateRange = (Select EndDateRange From fn_DateSel (@DateRange))

BEGIN
Declare @sql nvarchar(max)
Declare @ParamDefinition AS nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT SiteName, TransmitterType, CameraNumber, CameraName, EventID, EventType, Description, Priority, SourceID, SourceType, EventStartTime, EventEndTime, Indicator, ZoneNumber, EventCode, ZoneText, DetectorText, RID
FROM '+name+'.dbo.EventDetails INNER JOIN
'+name+'.dbo.SiteDetails ON EventDetails.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE EventDetails.EventStartTime >= @StartDateRange AND EventDetails.EventStartTime < @EndDateRange
AND SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND SiteDetails.TransmitterType IN (Select Param From fn_MVParam ('''+@TransmitterType+''','',''))
AND dbo.EventTypeConv (EventType) IN (Select Param From fn_MVParam ('''+@EventType+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))
AND (SiteDetails.IsDeleted = ''False'')
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition =
' @SiteID nvarchar(50),
@TransmitterType nvarchar(50),
@EventType nvarchar(max),
@DateRange nvarchar(30),
@DBName nvarchar (100),
@StartDateRange DateTime,
@EndDateRange DateTime'

Create table #t([SiteName] nvarchar(50), [TransmitterType] nvarchar(50), [EventID] int, [CameraNumber] int, [CameraName] nvarchar(max), [EventType] nvarchar(max), [Description] nvarchar(max), [Priority] int, [SourceID] int, [SourceType] nvarchar(max), [EventStartTime] DateTime, [EventEndTime] DateTime, [Indicator] nvarchar(max), [ZoneNumber] nvarchar(4), [EventCode] int, [ZoneText] nchar(20), [DetectorText] nchar(20), [RID] nchar(16))
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @SiteID, @TransmitterType, @EventType, @DateRange, @DBName, @StartDateRange, @EndDateRange
Print @sql

SELECT DISTINCT [SiteName], [TransmitterType], [EventID], [CameraNumber], [CameraName], dbo.EventTypeConv ([EventType]) As EventType, [Description], [Priority], [SourceID], dbo.SourceTypeConv ([SourceType]) As SourceType, CONVERT(VARCHAR(19), [EventStartTime], 120) AS [EventStartTime], CONVERT(VARCHAR(19), [EventEndTime], 120) AS [EventEndTime], [Indicator], [ZoneNumber], [EventCode], [ZoneText], [DetectorText], [RID]
FROM #t
ORDER BY SiteName, EventStartTime DESC

Drop Table #t

END

SET NOCOUNT OFF

Return

Here are the functions:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(max), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(max))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(max)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-03 : 00:34:15

hi,
can u post the result of following stmt

Select Param From fn_MVParam ('98','','')

and also the code for function fn_MVParam.

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:41:44

i think its problem with function fn_MVParam. what does function do?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-12-03 : 01:04:24

Well I cannot understand why it ever worked at all :) Maybe a compiler error? The problem is eventid is the 5th field selected in the dynamic sql but the 3rd field in the temp table. Correct order and noe it all works. What a strange one, it should have failed for all that were alphanumeric.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources