May 15, 2008 at 12:47 am
Here is my SP, i m using SQL Server 2000,
In the Severity Column, i have 3 values :
Critical,
Information and
Error
But when i run my SP : Exec GetFilteredLog null,'Critical' , it gives the following msg:
Invalid column name 'Critical'.When i check the Query it makes this:
Select LogID,Severity,UserName,Timestamp,MachineName,PayLoad,Message,Priority,CategoryID
from Log Where Severity in (Critical), if u checked here it not add the '' in the Value CRITICAL, i tried to concatenate
with Comma, but stll gives error , kindly tell me how can i get this as this is varchar value.
Now after inserting the Comma , my Serverity stmt becomes:
IF @Severity is not null
BEGIN
Select @Where = Case When @Where is null then 'Where Severity in ('''+ @Severity+''')'
Else @Where + ' And '+ 'Severity in ('''+ @Severity+ ''')'
End
END
now its working fine but if i give only 1 value in this variable and the stmt its make is :
Select LogID,Severity,UserName,Timestamp,MachineName,PayLoad,Message,Priority,CategoryID
from Log Where Severity in ('Critical'),
But now problem with the mutiple values as i used the in Clause, now this stmt becomes:
Select LogID,Severity,UserName,Timestamp,MachineName,PayLoad,Message,Priority,CategoryID
from Log Where Severity in ('Critical',Information), not inserted the '' in the 2nd filter as this is the varchar values, so i want to insert ''
in all the values passed in that clause , plz help me how can i do this?
CREATE PROCEDURE GetFilteredLog
@CategoryID varchar(50) = null ,
@Severity varchar(50) = null ,
@UserActivityCode varchar(50)= null ,
@Priority varchar(50) = null
AS
Declare @Select nvarchar(4000)
Declare @From nvarchar(4000)
Declare @Where nvarchar(4000)
Declare @sql nvarchar(4000)
select @Select = ' Select LogID,Severity,UserName,Timestamp,MachineName,PayLoad,Message,Priority,CategoryID'
select @From = 'from Log'
IF @CategoryID is not null
BEGIN
Select @Where = 'Where CategoryID in ('+ @CategoryID+ ')'
END
IF @Severity is not null
BEGIN
Select @Where = Case When @Where is null then 'Where Severity in ('+ @Severity+')'
Else @Where + ' And '+ 'Severity in ('+ '@Severity'+ ')'
End
END
IF @UserActivityCode is not null
BEGIN
Select @Where = Case When @Where is null then 'Where UserActivityCode in ('+ @UserActivityCode+ ')'
Else @Where + ' AND '+ 'UserActivityCode in ('+ @UserActivityCode+ ')'
End
END
IF (@Priority is not null)
BEGIN
Select @Where = Case When @Where is null then 'Where Priority in ('+ @Priority+ ')'
Else @Where + ' AND '+ 'Priority in ('+ @Priority+ ')'
End
END
Set @sql = @Select +' '+ @From +' '+ Coalesce(@Where,'')
select @sql
Exec sp_executesql @sql
Kindly reply me, Thanx in Advance.
May 15, 2008 at 1:32 am
Hi;
For multiple values ;
DECLARE
@CategoryID varchar(50) ,
@Severity varchar(50) ,
@UserActivityCode varchar(50) ,
@Priority varchar(50) ,
@severityitems VARCHAR(500)
SET @CategoryID = null
set @Severity = 'hasan,mansur'
sET @UserActivityCode = null
set @Priority = null
SET @severityitems = ''
Declare @Select nvarchar(4000)
Declare @From nvarchar(4000)
Declare @Where nvarchar(4000)
Declare @sql nvarchar(4000)
select @Select = ' Select LogID,Severity,UserName,Timestamp,MachineName,PayLoad,Message,Priority,CategoryID'
select @From = 'from Log'
IF @CategoryID is not null
BEGIN
Select @Where = 'Where CategoryID in ('+ @CategoryID+ ')'
END
IF @Severity is not null
BEGIN
WHILE CHARINDEX(',',@Severity) > 0
BEGIN
SELECT @severityitems = @severityitems + '''' + LEFT(@Severity,CHARINDEX(',',@Severity)-1) + ''','
SELECT @Severity = SUBSTRING(@Severity,CHARINDEX(',',@Severity) + 1,LEN(@Severity))
END
IF CHARINDEX(',',@Severity) >= 0
BEGIN
SET @Severity = REPLACE(@Severity,',','')
SELECT @severityitems = @severityitems + '''' + @Severity + ''''
END
Select @Where = Case When @Where is null then 'Where Severity in ('+ @severityitems+')'
Else @Where + ' And '+ 'Severity in ('+ '@Severity'+ ')'
End
END
IF @UserActivityCode is not null
BEGIN
Select @Where = Case When @Where is null then 'Where UserActivityCode in ('+ @UserActivityCode+ ')'
Else @Where + ' AND '+ 'UserActivityCode in ('+ @UserActivityCode+ ')'
End
END
IF (@Priority is not null)
BEGIN
Select @Where = Case When @Where is null then 'Where Priority in ('+ @Priority+ ')'
Else @Where + ' AND '+ 'Priority in ('+ @Priority+ ')'
End
END
Set @sql = @Select +' '+ @From +' '+ Coalesce(@Where,'')
print @sql
May 15, 2008 at 1:42 am
Thankyou so much, its working now. Thanx again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply