0

I have the following problem. I want to send null value to Stored Procedure with querystringparameter.

This is my Stored Procedure

CREATE PROCEDURE spGetAllProducts
 @catID int = null,
 @subcatID int = null
AS
BEGIN
    SELECT * FROM Products WHERE
    ((@catID is null) OR (category_id = @catID))
    AND ((@subcatID is null) OR (subcategory_id = @subcatID))
END

it is working when i am testing with following queries as exactly how i want

spGetAllProducts null, '3'
spGetAllProducts '8', null
spGetAllProducts '8', '3'
spGetAllProducts null, null

but when i am trying to send null value with queryparameter it is not working

here is my sqldatasource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringReviewDB %>" SelectCommandType="StoredProcedure" SelectCommand="spGetAllProducts">

    <SelectParameters>
    <asp:QueryStringParameter Direction="Input" Name="catID" QueryStringField="catID" Type="Int32" />
    <asp:QueryStringParameter Direction="Input" Name="subcatID" QueryStringField="subcatID" Type="Int32" DefaultValue="" ConvertEmptyStringToNull="True" />
    </SelectParameters>

    </asp:SqlDataSource>

When i am sending the query like this

http://localhost:50693/TreeViews.aspx?catID=8&subcatID=3

it is working but when i am sending the following query it is not working

http://localhost:50693/TreeViews.aspx?catID=8&subcatID=

Thx from now

Kemal Umut
  • 23
  • 1
  • 9
  • As @LearningAsp answered, your second query equal to the following: `spGetAllProducts '8', ''` – Serg Jul 11 '13 at 15:23

1 Answers1

0

try like this

CREATE PROCEDURE spGetAllProducts @catID int = null, @subcatID int = null AS BEGIN SELECT * FROM Products WHERE ((@catID '') OR (category_id = @catID)) AND ((@subcatID = '') OR (subcategory_id = @subcatID)) END

I think as stored procedure takes it as empty string not as null ,I had similar problem and it worked for me

LearningAsp
  • 351
  • 1
  • 2
  • 12