CODEWITHSUNDEEP
sql-serverstored-procedures
Sardoan
Sardoan

Reputation: 817

MS SQL passing parameter to stored proc indicating value is null or not null

need to build a stored procedure which returns data. On the client I have a checkbox which is used to choose if one value should be null or not.

Let us say the column-name is package. 

Create PROCEDURE [dbo].[up_query_wt_paketsuche]
(
    @packagenull int = 0 
)
AS
(
SELECT * FROM PACKAGES  
where ???
)

So I pass 1 as @packagenull if the query should return all values with packages not null and nothing if the query should return all values where package is null. The main point is to handle the parameter in the where clause. The real stored procedure is more complex, 12 parameters, union over two tables... So I would like to handle the parameter in the where clause.

Thanks.

Edit: My tables are not single tables. Here is my actual where clause without @packagenull

where f.vtyp6maske like @vtyp 
and f.marke like @marke 
and h.mkb like @mkb 
and (
      modjahr_von = @modelljahrvon 
    or 
        (     @modelljahrvon=999999 
        and (modjahr_von is null or modjahr_von is not null)
        )
     ) 
and (modjahr_bis = @modbis 
        or  (
               @modelljahrbis = 999999 
               and (modjahr_bis is null or modjahr_bis is not null)
             )
    ) 
and i.prnr like @prnr

Upvotes: 0

Views: 52

Answers (2)

Sardoan
Sardoan

Reputation: 817

@scsimon Sorry, I don't get the meaning. @packagenull is an optional parameter, so it is never null, it is 0 by default. package = @packagenull will never be true, because package is never 0 or 1, it is a string. What I want is package != null when @packagenull=1 and package = null when @packagenull=0

Upvotes: 0

S3S
S3S

Reputation: 25112

If i understand you correctly you want ALL or NONE of the rows based on the parameter. This should suffice.

Create PROCEDURE [dbo].[up_query_wt_paketsuche]
(
    @packagenull int = 0 
)
AS

SELECT * FROM PACKAGES  
where @packagenull is NULL or someColumn = @packagenull



where f.vtyp6maske like '%' + @vtyp + '%'                            --Added concat here
and f.marke like '%' + @marke + '%' and h.mkb like '%' + @mkb + '%'  --Added concat here
and (modjahr_von = @modelljahrvon or
(@modelljahrvon=999999 and 
(modjahr_von is null or modjahr_von is not null))) 
and (modjahr_bis = @modbis or (@modelljahrbis = 999999 
and (modjahr_bis is null or modjahr_bis is not null))) 
and i.prnr like '%' + @prnr + '%'                                    --Added concat here 
and (@packagenull is null or package = @packagenull)                 --Added additional parameter

Upvotes: 1

Related Questions