SQL Server Performance

need help in my SP

Discussion in 'General Developer Questions' started by isa, May 14, 2008.

  1. isa New Member

    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.
  2. FrankKalis Moderator

    ('+ '@Severity'+ ')' should have 3 ' on each side of the variable. Generally you should throw in some PRINTs to validate your string while testing.
  3. isa New Member

    ('''+ @Severity+''')' i tried this but its not working for 2 seveirt values, it returns null, i mean no row.

Share This Page