CODEWITHSUNDEEP
sqlsql-serverc#-4.0stored-procedures
Artak Khachatryan
Artak Khachatryan

Reputation: 311

SQL Server stored procedure parameters

I have a table with 2 columns-

I have a c# function which calls a stored procedure

...
cmd.Parameters.Add(new SqlParameter("@aa", SqlDbType.Int));
cmd.Parameters["@aa"].Value = aaValue;
cmd.Parameters.Add(new SqlParameter("@bb", SqlDbType.Int));
cmd.Parameters["@bb"].Value = bbValue?? (object)DBNull.Value;
reader = cmd.ExecuteReader(); ...

Now I want the stored procedure to return all the entries which have "aa" equal to aaValue and "bb" equal to bbValue. but if bbValue is null, i want to return all the entries only with "aa" equal to aaValue.

How can I write such a stored procedure?

Here is what I have done, but it doesn't work

SELECT ID  
FROM MyTable 
WHERE aa = @aa AND (bb IS NULL OR bb = @bb)

Upvotes: 0

Views: 74

Answers (2)

GarethD
GarethD

Reputation: 69749

For what it is worth, you will find this approach has suboptimal performance, this line (when corrected)

(@bb is null OR bb = @bb)

Means that any index on bb cannot be used, because at compile time it is not known whether or not @bb will be NULL, so a query plan that caters for both scenarios will be chosen. It would be better to separate your queries with and IF/ELSE flow operator, that way two plans can be cached, one for when @bb is NULL and one for when it isn't:

IF @bb IS NULL
BEGIN
    SELECT  ID  
    FROM    MyTable 
    WHERE   aa = @aa;
END
ELSE
BEGIN
    SELECT  ID  
    FROM    MyTable 
    WHERE   aa = @aa
    AND     bb = @bb;
END

Upvotes: 2

jean
jean

Reputation: 4350

You just forgot the @ for the @bb variable since all you want is to check if the varibale, not the row value is null

SELECT ID  from MyTable where  aa = @aa and (@bb is null OR bb = @bb)

Upvotes: 1

Related Questions