Uniqueidentifier is incompatible with int

Question

Post reply

Uniqueidentifier is incompatible with int

krypto69

SSChampion

Points: 13604
More actions
February 11, 2009 at 1:20 pm

#128136

Help!! Any idea how to fix this error:
Msg 206, Level 16, State 2, Procedure AppLog_AdHoc, Line 19
Operand type clash: uniqueidentifier is incompatible with int
ALTER PROCEDURE [dbo].[AppLog_AdHoc]
@Severity INT = ''
, @UserName VARCHAR(24) = ''
, @OrderId INT = ''--Param1?
, @ServiceId INT = ''--Param2?
, @RequestId VARCHAR(64) = '' --Param3?
, @EventId INT = ''
, @KeyId UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'
, @Id BIGINT = ''
, @Machine VARCHAR(48) = ''
, @Location VARCHAR(256) = ''
, @Message VARCHAR(256) = ''
, @sql VARCHAR(1000)
AS
BEGIN
DECLARE @sql2 nVARCHAR(4000)
SET @sql2 = 'SELECT [Id]
,[TimeStamp]
,[Severity]
,[Category]
,[EventId]
,[UserName]
,[KeyId]
,[Param1]
,[Param2]
,[Param3]
,[Message]
,[Location]
,[MachineName]
,[ApplicationName]
,[AppDomainName]
,[Identity]
,[DetailMessageSize]
FROM [Logger2]
WITH(NOLOCK)
WHERE ([Severity] = '+ @Severity +'OR '+ @Severity +''' = '''')
AND ([UserName] = ''' +@UserName +'''OR ''' +@UserName +''' ='''')
AND ([Param1] = '+ @OrderId +'OR '+@OrderId +'='''')
AND ([Param2] = '+@ServiceId+' OR' + @ServiceId +'= '''')
AND ([Param3] = '''+@RequestId +'''OR''' + @RequestId +'''= '''')
AND ([EventId] = '+@EventId+' OR '+@EventId+' = '''')
AND ([KeyId] = '''+@KeyId+''' OR '''+CONVERT(UNIQUEIDENTIFIER,@KeyId)+''' = ''''00000000-0000-0000-0000-000000000000'''')
AND ([Id] = '+ @Id +'OR '+@Id+' = '''')
AND ([MachineName] = '''+@Machine +'''OR '''+ @Machine+''' = '''')
AND ([Location] = '''+@Location+''' OR '''+@Location+''' = '''')
AND ([Message] = '''+ @Message+''' OR '''+@Message +'''= '''') '
+ 'AND ' + REPLACE(@sql,CHAR(39),'''')
EXECUTE sp_executesql @sql2
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Two questions.

What's the definition of Logger2?

Why is this dynamic SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 2

Here's the defiition.

The reason for dynamic sql, the front end has various fields the user may/may not enter and we don't know which they'll send.

USE [LoggerSource]

GO

/****** Object: Table [dbo].[Logger2] Script Date: 02/11/2009 15:35:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Logger2](

[Id] [bigint] IDENTITY(1,1) NOT NULL,

[TimeStamp] [datetime] NULL,

[Severity] [int] NULL,

[Category] [varchar](24) NULL,

[EventId] [int] NULL,

[UserName] [varchar](24) NULL,

[KeyId] [uniqueidentifier] NULL,

[Param1] [int] NULL,

[Param2] [int] NULL,

[Param3] [varchar](64) NULL,

[Message] [varchar](256) NULL,

[Location] [varchar](256) NULL,

[MachineName] [varchar](48) NULL,

[ApplicationName] [varchar](24) NULL,

[AppDomainName] [varchar](64) NULL,

[Identity] [varchar](48) NULL,

[DetailMessage] [varbinary](max) NULL,

[DetailMessageSize] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

krypto69 (2/11/2009)
The reason for dynamic sql, the front end has various fields the user may/may not enter and we don't know which they'll send.

Which is reasonable, but the way you've written it does not require any dynamic SQL at all, will suffer from all the perf problems that this style of query suffers from, with none of advantages that dynamic SQL usually gives for these problems.

When concatenating a string together, all the parameters and variables need to be case to varchar first. Do that, and the error will go away, or remove the dynamic SQL all together, or use dynamic SQL properly for this problem to build up a query with just the conditions the user selects, and without all that (column=variable or variable is null) mess.

See - http://www.sommarskog.se/dyn-search.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 4

Thanks Gail,

We went to the link you provided and re-wrote the proc.

It seems to be working well.

Thanks again.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

[font="Verdana"]Even worse, the way you had that written allowed for SQL injection. If you must pass parameters into dynamic SQL, either check the content of the parameter or pass it as a variable (i.e. don't use exec(), use sp_executesql).[/font]