Need help on select case - 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

Need help on select case

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-09 : 08:43:31

i am passing one parameter into query.i need to write selece case. If paramerter is null then return all rows. if parameter is not null then return rows based on parameter

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 08:48:23

quote:
Originally posted by sqllover

i am passing one parameter into query.i need to write selece case. If paramerter is null then return all rows. if parameter is not null then return rows based on parameter

[CODE]
if @param is null
select requred column
else
select req column where param=condition
[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 09:01:17

select columns from your_table
where col=@param or @param is null

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 09:13:11

quote:
Originally posted by madhivanan

select columns from your_table
where col=@param or @param is null

Madhivanan

Failing to plan is Planning to fail

Excelent approach..Mr.Madhi..iam seeing today as u are using or operator where ever possible :) you r or Rock.honesly i learnt today of using or opeator..keeps the logic as simple as this..
Thanks agian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 09:58:29

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

select columns from your_table
where col=@param or @param is null

Madhivanan

Failing to plan is Planning to fail

Excelent approach..Mr.Madhi..iam seeing today as u are using or operator where ever possible :) you r or Rock.honesly i learnt today of using or opeator..keeps the logic as simple as this..
Thanks agian

You are welcome

Madhivanan

Failing to plan is Planning to fail

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-11 : 00:55:53

thanks buddy

LHendren
Starting Member

17 Posts

Posted - 2010-03-11 : 12:40:53

Following up on this thread, I cannot understand why this code does not render a GridView with ALL of the employee last names when the employeenameTextBox is empty.

I have 2 Dropdown Menus: Client(custnmbr) and ProjectID (paprojid) and a Gridview showing date, employee last name, hours, etc. for the specific client AND projectid from the Dropdown menus.

I am trying to generate a view such that if the employeenameTextBox is empty, all of the employee names, dates, hours, etc are shown in the Gridview
Otherwise,
Show only the dates, hours, etc for the employee whose last name is in the employeenameTextBox (as depicted in the code below)

Code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:csESS %>"
SelectCommand="SELECT P.PADT, P.PAQtyQ, P.EMPLOYID, P.PAUnit_of_Measure, P.PAUNITCOST, P.PAACREV, P.PATOTCST, S.LASTNAME
FROM PA30101 AS P
JOIN UPR00100 AS S
ON (P.EMPLOYID = S.EMPLOYID)
JOIN PA01201 AS T
ON (P.PAPROJNUMBER = T.PAPROJNUMBER)
WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and S.LASTNAME = @LASTNAME or @LASTNAME IS NULL
ORDER BY P.PADT">

<SelectParameters>
<asp:ControlParameter ControlID="clientDropDown" Name="CUSTNMBR" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="projectidDropDown" Name="PAPROJID" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="employeeNameTextBox" Name="LASTNAME" PropertyName="Text" Type="String" />
</SelectParameters>

</asp:SqlDataSource>

This code generates an empty GridView until the employeNameTextBox is filled with a legitimate LastName.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-12 : 01:42:08

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and S.LASTNAME = @LASTNAME or @LASTNAME IS NULL

should be

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or @LASTNAME IS NULL)

Madhivanan

Failing to plan is Planning to fail

LHendren
Starting Member

17 Posts

Posted - 2010-03-16 : 15:23:57

Madhivanan, thank you very much for your help - I sincerely appreciate it. However, I still cannot display all of the rows when the employeenameTextBox is empty. It works fine if I input an employee's last name. To someone who has written dBase programs for over 20 years, this is very frustrating since I could do this easily in dBase.

SelectCommand="SELECT P.PADT, P.PAQtyQ, P.EMPLOYID, P.PAUnit_of_Measure, P.PAUNITCOST, P.PAACREV, P.PATOTCST, S.LASTNAME
FROM PA30101 AS P
JOIN UPR00100 AS S
ON (P.EMPLOYID = S.EMPLOYID)
JOIN PA01201 AS T
ON (P.PAPROJNUMBER = T.PAPROJNUMBER)
WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or @LASTNAME IS NULL)
ORDER BY P.PADT">

<SelectParameters>
<asp:ControlParameter ControlID="clientDropDown" Name="CUSTNMBR" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="projectidDropDown" Name="PAPROJID" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="employeeNameTextBox" Name="LASTNAME" PropertyName="Text" Type="String" />

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 02:29:16

Which refers to employeename text box?

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2010-03-17 : 03:25:13

where COl = isnull(@DBNAME,col)
Hope this helps

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

LHendren
Starting Member

17 Posts

Posted - 2010-03-17 : 09:56:56

Madhivanan, @LASTNAME refers to employeenameTextBox

Larry

quote:
Originally posted by madhivanan

Which refers to employeename text box?

Madhivanan

Failing to plan is Planning to fail

LHendren
Starting Member

17 Posts

Posted - 2010-03-17 : 10:00:49

Ashishashish, I have tried all 3 of these; however, they all give the same results.....no rows in the GridView until I input the employee LASTNAME.

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or @LASTNAME IS NULL)

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = IsNull(@LASTNAME,S.LASTNAME))

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or S.LASTNAME = IsNull(@LASTNAME,S.LASTNAME))

Larry

quote:
Originally posted by ashishashish

where COl = isnull(@DBNAME,col)
Hope this helps

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 10:06:59

have you set any default value for lastname parameter?

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

LHendren
Starting Member

17 Posts

Posted - 2010-03-17 : 11:26:21

Visakh16, I checked again and no, I have not set a default value for @LASTNAME.

quote:
Originally posted by visakh16

have you set any default value for lastname parameter?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 11:36:28

then how will it work without passing a value for the parameter?

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

LHendren
Starting Member

17 Posts

Posted - 2010-03-17 : 14:05:52

visakl16, when the Web form opens, I select a client name from the clientDropDown (@CUSTNMBR), then I select a project number from the projectidDropDown (@PAPROJID). The GridView is blank at this time. I would like it to display ALL of the rows at this time.

Then, I would like to filter the GridView by entering an employee's last name in the employeenameTextBox (@LASTNAME) The GridView would then only show the rows with the queried Lastname.

Without the @LASTNAME parameter code, the GridView works fine displaying ALL of the rows. When I add references to the @LASTNAME parameter in the code, the GridView opens with empty rows. Then when I add a Lastname in the employeenameTextBox, the GridView shows all rows for that Lastname.

Thank you for your help on this.
Larry

quote:
Originally posted by visakh16

then how will it work without passing a value for the parameter?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 14:08:33

ok then add a default value for employeenameTextBox as ''(blank) and then make query as

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or @LASTNAME ='')

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

LHendren
Starting Member

17 Posts

Posted - 2010-03-17 : 17:23:11

visakh16, once I added a default value for emplyeenameTextBox as blank and changed ...or @LASTNAME = ' '), it finally worked for me. Thank you; your help is sincerely appreciated.

quote:
Originally posted by visakh16

ok then add a default value for employeenameTextBox as ''(blank) and then make query as

WHERE T.PAPROJID = @PAPROJID and T.CUSTNMBR = @CUSTNMBR and (S.LASTNAME = @LASTNAME or @LASTNAME ='')

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:03:06

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources