February 11, 2009 at 11:09 am
I'm getting the following error: Must declare the scalar variable "@Severity" when it hits the execute sp_executesql, i've also tried exec(@sql2) witht he same results.
I need to use the variable in the OR so that if the variable is empty, the query will return ALL values.
alter PROCEDURE 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
February 11, 2009 at 11:18 am
alter PROCEDURE 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
February 11, 2009 at 12:34 pm
Thanks Carolyn,
I got a syntax error and moved the "+" outside of the single quote on @RequestId and Convert(Unique......
Now I'm gettting the following error: Operand type clash: uniqueidentifier is incompatible with int
I'm hoping you can help again.
Thanks!
February 12, 2009 at 6:35 am
I think you may also need to convert the ''''00000000-0000-0000-0000-000000000000 into an uniqueidentifier as well to compare them.
DECLARE @compid UNIQUEIDENTIFIER
SET @compid = CONVERT(UNIQUEIDENTIFIER,'00000000-0000-0000-0000-000000000000')
AND ([KeyId] = '''+@KeyId+''' OR +'''CONVERT(UNIQUEIDENTIFIER,@KeyId)+''' = '''+@compid +''' )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply