SQL Server Performance

help in SP

Discussion in 'General Developer Questions' started by isa, May 13, 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.



    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

    Can you post the concatenated SQL string please?
  3. FrankKalis Moderator

    Probably you should write something like
    @Where is null then 'Where Priority in ('''+ @Priority+ ''')'
    Note the 3 ' on each side of the parameter, to indicate it's a string.
  4. isa New Member

    Thankyou so much, its working now, i missed one quote at the end of @Serverity. Once again thanx.

Share This Page