Post reply

t-sql 2012 compare column to parameter value

  • June 2, 2017 at 3:29 pm

    #323838

    In an existing t-sql 2012 stored procedure, I want to add a new parameter called @newparam defined probably as int.

    This parameter valuet will be compared against the production column called 'sExclude' is defined as (bit,null). I checked all the

    values for  'sExclude' in production and there are no records where the production value is null.

    What I want to do is select is the following:
    1. Select sExclude when @newparm value matches the value in the sExclude column.
        In other words when sExclude = 0 and @newparm = 0
                          or when sExclude = 1 and @newparm = 1.
    2. When the @newparm value is any other value like 2, I do not care cared of the sExclude value is. I just want to select all

    records regardless of what the sExclude value is actually in production.

    I am trying to determine how to write the t-sql for this logic in a where clause.

    Would I say

    'Where sExclude = @newparm or (@newparm not in (0,1))

    Would you tell me how you would setup the t-sql 2012 for what i need in this situation?
            

  • June 2, 2017 at 4:04 pm

    #1945217

    Oh, so you're a big fan of implicit conversion? Try it with a multi-million (or more) row table. Why not save yourself the trouble and make the parameter type match the field type from the outset?

  • June 2, 2017 at 4:16 pm

    #1945218


    CREATE PROCEDURE My123 @int
    AS
    SELECT *
    FROM My345
    WHERE sExclude=
    CASE WHEN @int<2 THEN @int 
    ELSE sExclude END;
    GO

  • June 2, 2017 at 6:41 pm

    #1945222

    Piet Linden's point about implicit conversion is something that's pretty serious.

    In a nutshell, when the engine  has to compare values (join, where or having) the data types have to match.  If they don't, SQL Server will do the conversion for you, but it comes with a price.  The data types will be converted in order using the list at https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql.  A varchar will be converted to an nvarchar; a bit will be converted to an integer.  In order for this to be done, it means the whole column has to be read before the implicit conversion can be performed.  If this has to be done over 1M rows, you'll definitely feel the impact of all those reads.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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