Variable within SQL string not recognized

  • 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

  • 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

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • 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!

  • 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 +''' )

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply