May 13, 2008 at 4:03 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.
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 13, 2008 at 7:40 am
It looks like you need single quotes around the IN clause: Where Severity in ('Critical'). Without them, SQL Server thinks you're referencing a column name instead of a column value.
To correct, the CASE statement for @Severity should be the following:
SELECT @Where = CASE WHEN @Where is null THEN 'Where Severity in ('+ @Severity+')'
ELSE @Where + ' And '+ 'Severity in ('''+ @Severity + ''')'
END
Test it out and see if it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply