June 2, 2017 at 3:29 pm
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
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
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
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply