where condition in TSQL based on input parameters

Question

I have an sql server table with an int column named [type] which is nullable. I have following stored procedure which gets type as input:

    @Jobtype VARCHAR(50) = '',  
    @Language INT
AS
BEGIN
    SELECT [TabId]      
      ,CASE
            WHEN @Language = 2 THEN [TabNameTextLang2]
            ELSE [TabNameTextLang1]
        END AS TabName
      ,CASE
            WHEN @Language = 2 THEN [PageTitleLang2]
            ELSE [PageTitleLang1]
        END AS PageTitle
      ,[TabLink]
      ,[Type]
      ,[Priority]
      ,[DutyStationId]
      ,[DateCreated]
      ,[DateModified]
      ,[Deleted]
    FROM dbo.hr_Tabs
    WHERE Deleted = 0 AND 
    ((@Jobtype = '' and [Type] is null) or  ([Type] = CASt(@Jobtype as int )))

END

this table has a row where type is null. I want that when i pass @jobtype as '' then only that row is selected otherwise rows are shown by their type. But when I try to pass above stored procedure parameters like

exec myproce '', 1

I get all records

Please suggest how to filter records based on type in my case

Do you get all rows, or just all rows where type is 0? CAST('' as int) produces a 0, so the second part of the condition could be true. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 4, 2013 at 9:55

Damien_The_Unbeliever · Accepted Answer · 2013-11-04 09:57:48Z

2

Use:

((@Jobtype = '' and [Type] is null) or
 (@Jobtype <> '' and [Type] = CAST(@Jobtype as int )))

(I would also generally suggest to use NULL rather than an empty string as your special marker value)

answered Nov 4, 2013 at 9:57

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DotnetSparrow Over a year ago

in that case I will use following ? ((@Jobtype is null and [Type] is null) or (@Jobtype is not null and [Type] = @Jobtype))

t-clausen.dk · Accepted Answer · 2013-11-04 11:45:49Z

-1

Alternative

FROM dbo.hr_Tabs
CROSS APPLY (select [Type] as dummy intersect select nullif(@Jobtype, '')) x
WHERE Deleted = 0

In order to get an exact match to mr. Smith's answer (logic is the same):

FROM dbo.hr_Tabs
WHERE Deleted = 0 and exists 
(select [Type] as dummy intersect select nullif(@Jobtype, ''))

edited Nov 4, 2013 at 11:45

answered Nov 4, 2013 at 10:11

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

2 Comments

t-clausen.dk Over a year ago

Normal procedure is to comment when downvoting. This syntax is valid

t-clausen.dk Over a year ago

I was inspired by this answer by @MartinSmith

Collectives™ on Stack Overflow

where condition in TSQL based on input parameters

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related