Checkbox in Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Checkbox in Stored Procedure

Author

Topic

rj_connor
Starting Member

2 Posts

Posted - 2011-10-02 : 12:48:03

Hi hope someone can help me out here, i'm new to SQL and asp.net

I have a web page using asp.net linked to a SQL database.

The page allows you to enter in a email address in to a text file and a date in another text filed and when the search button i clicked a SP runs and pulls back the mailbox store location for the user on that date and displays it in a gridview below.(this works fine)

What i want to do is add a check box that will allow you enter in the email address as before, check the checkbox, leave the date filed blank click the search button and pull back all the mailbox store locations for that user and display them in the same gridview.

I can't figure out how to get this to work any help would be great.

Info

DB table has 5 rows called DN,mail,homeMDB,name,date

code for the SP is

@address nvarchar(250),
@date datetime

AS

SET NOCOUNT ON;

BEGIN
SELECT dbo.mbxlocation2.homeMDB
FROM dbo.mbxlocation2
WHERE dbo.mbxlocation2.mail = @address AND dbo.mbxlocation2.date = @date

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 13:52:17

just add in click event of checkbox code to make value of @date as null and then change procedure as follows


@address nvarchar(250),
@date datetime

AS

SET NOCOUNT ON;

BEGIN
SELECT dbo.mbxlocation2.homeMDB
FROM dbo.mbxlocation2
WHERE dbo.mbxlocation2.mail = @address 
AND (dbo.mbxlocation2.date = @date or @date is null)

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-02 : 15:38:38

However note that queries that have where clause predicates of the form (column = @parameter or @parameter IS NULL) in general do not perform well (and it's not a minor performance problem)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

rj_connor
Starting Member

2 Posts

Posted - 2011-10-03 : 10:15:33

Ok take that in to account hw about changeing the SP to (at a high level).
If dbo.mbxlocation2.mail = @address AND dbo.mbxlocation2.date = @date
SELECT dbo.mbxlocation2.homeMDB
FROM dbo.mbxlocation2
ELSE
IF dbo.mbxlocation2.mail = @address
SELECT dbo.mbxlocation2.homeMDB
FROM dbo.mbxlocation2
END

Not sure if that is posiable but something like that might work.

Kristen
Test

22859 Posts

Posted - 2011-10-03 : 12:53:00

This perhaps?


IF @date IS NOT NULL
BEGIN
    SELECT dbo.mbxlocation2.homeMDB
    FROM dbo.mbxlocation2
    WHERE dbo.mbxlocation2.mail = @address AND dbo.mbxlocation2.date = @date
END
ELSE
BEGIN
    SELECT dbo.mbxlocation2.homeMDB
    FROM dbo.mbxlocation2
    WHERE dbo.mbxlocation2.mail = @address
END

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-03 : 13:12:20

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources