Select with Condition in SP - 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

Select with Condition in SP

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-17 : 06:15:45

Dear Gurus,

How do I create condition if I would use a SP? I can do that easily with ASP, but in SP, I am totally clueless, please advise.

Example as below (The code below does not work):

ALTER PROCEDURE [dbo].[SP_SELECTmyTable]
@Condition1 int,
@Condition2 int,
@Condition3 int
AS
BEGIN
SELECT * FROM myTable WHERE 0=0

IF @Condition1 = 1 THEN
  AND Column1 = 1
END

IF @Condition2 = 1 THEN
  AND Column2 = 1
END

IF @Condition3 = 1 THEN
  AND Column3 = 1
END

END

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-17 : 07:25:37

What are you trying to achieve by this code ? Could you please explain it more?

Vijay is here to learn something from you guys.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-17 : 08:21:25

Yes, this is what I need to do. The conditions depends what is passed in. If condition is true, then add condition 1. Etc...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-17 : 09:17:31

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-17 : 09:19:39

[code]

Few Questions on your requirement:

1.Why have you used below select statement ?.I don't see it's purpose

SELECT * FROM myTable WHERE 0=0

2. what this columns means? Do you want to store values in these variables
Column1
Column2
Column3

3.Do you want to set the values for Column1,Column2 and Column3?

ALTER PROCEDURE [dbo].[SP_SELECTmyTable]
@Condition1 int,
@Condition2 int,
@Condition3 int
AS
BEGIN
DECLARE @Column1 INT,@Column2 INT,@Column3 INT

SELECT * FROM myTable WHERE 0=0

IF @Condition1 = 1 THEN
SET @Column1 = 1
END

IF @Condition2 = 1 THEN
SET @Column2= 1
END

IF @Condition3 = 1 THEN
SET @Column3= 1
END

END

By looking at your code and you what you explained is not clear.
[/code]

Vijay is here to learn something from you guys.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-17 : 20:43:15

Okay, let me explain with a little more examples. Basically I am trying to build a dynamic Query

Example #1:
if pass in EXEC SP_SELECTmyTable (0, 0, 0) Then select EVERYTHING:
SELECT * FROM myTable

Example #2:
if pass in EXEC SP_SELECTmyTable (1, 1, 1) Then:
SELECT * FROM myTable WHERE Column1 = TRUE OR Column2 = TRUE OR Column3 = TRUE

Example #3:
if pass in EXEC SP_SELECTmyTable (1, 0, 0) Then:
SELECT * FROM myTable WHERE Column1 = TRUE

Example #4:
if pass in EXEC SP_SELECTmyTable (0, 1, 1) Then:
SELECT * FROM myTable WHERE Column2 = TRUE OR Column3 = TRUE.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-17 : 22:19:12

[code]
SELECT *
FROM myTable
WHERE
(
@Condition1 = 0
OR Column1 = @Condition1
)
AND
(
@Condition2 = 0
OR Column2 = @Condition2
)
AND
(
@Condition3 = 0
OR Column3 = @Condition3
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-17 : 23:00:55

Hi KHTan,
What if the value of the conditions are not the same as the parameter? Can it be done using your suggestion?

Example:
(IF @Condition1 = 1, then Column1 = "X")
OR (IF @Condition2 = 1, then Column1 = "Y")
OR (IF @Condition3 = 1, then Column1 = "Z")

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-17 : 23:24:39

In fact, I am trying to SELECT FROM someTable1 and INSERT INTO SomeTable2, to make this simplified:

INSERT INTO someTable1 (
SELECT something FROM someTable2 WHERE 0=0
AND (IF @Condition1 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME <> 'Sat')
AND (IF @Condition2 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME <> 'Sun')
AND (IF @Condition3 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME IS NULL)
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-17 : 23:47:33

quote:
Originally posted by calvinfoo

Hi KHTan,
What if the value of the conditions are not the same as the parameter? Can it be done using your suggestion?

Example:
(IF @Condition1 = 1, then Column1 = "X")
OR (IF @Condition2 = 1, then Column1 = "Y")
OR (IF @Condition3 = 1, then Column1 = "Z")


SELECT * 
FROM   myTable
WHERE  
(
    @Condition1 = 0
OR  Column1     = 'X'
)
AND
(
    @Condition2 = 0
OR  Column2     = 'Y'
)
AND
(
    @Condition3 = 0
OR  Column3     = 'Z'
)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-17 : 23:48:47

quote:
Originally posted by calvinfoo

In fact, I am trying to SELECT FROM someTable1 and INSERT INTO SomeTable2, to make this simplified:

INSERT INTO someTable1 (
SELECT something FROM someTable2 WHERE 0=0
AND (IF @Condition1 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME <> 'Sat')
AND (IF @Condition2 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME <> 'Sun')
AND (IF @Condition3 = 1, THEN F_TABLE_DATE.WEEKDAY_NAME IS NULL)
)

Are you using MVJ's F_TABLE_DATE to generate your own calendar table ?

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-18 : 01:24:55

Oh yes, I am using that MVJ's table, I LEFT OUTTER JOIN the F_TABLE_DATE to get the weekday. User selects a date range, then the system filters Sat/Sun/PublicHoliday if the user checked on "Exclude Saturday, Sunday & Public Holiday" on a Leave Application System

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-18 : 01:26:46

I don't generate my own calendar, but I use it to get some extra date information (Sat, Sun), then also another table to store the Public Holiday LIst.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 02:36:09

quote:
Originally posted by calvinfoo

Oh yes, I am using that MVJ's table, I LEFT OUTTER JOIN the F_TABLE_DATE to get the weekday. User selects a date range, then the system filters Sat/Sun/PublicHoliday if the user checked on "Exclude Saturday, Sunday & Public Holiday" on a Leave Application System

That means there is a date column in your table ? and you left join it to the F_TABLE_DATE just to get the weekday ?

You don't need to do that, you can just use datename(weekday, [date]) to get it.

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-18 : 03:01:22

Okay, I'll use datename, I didn't know this function. Thanks for the tips.

Yes, there is a date column in the Leave Application System.

How to solve it? Users can select a range of date, and user has the choice to tick an option to exclude all Saturday, Sunday, and Public Holiday.

User checked "Exclude Sat/Sun/PH", the date range will be generated and inserted into a Leave Table.
Default is unchecked, and a date range selected will include Sat/Sun/PH.

The reason is, in factory, there are group of people that work shifts and does not follow normal office hour. and the office staffs that follow standard office hour are complaining about the system, especially those taking more than one week (maternity leave example), they have to deselect Sat Sun and Public Holiday manually when a date range are generated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 03:22:47

[code]
SELECT *
FROM myTable
WHERE (
@Condition1 = 0
OR (@Condition1 = 1 AND datename(weekday, [DATE]) <> 'Saturday')
)
AND (
@Condition2 = 0
OR (@Condition2 = 1 AND datename(weekday, [DATE]) <> 'Sunday')
)
AND (
@Condition3 = 0
OR (@Condition3 = 1 AND PublicHoliday = 0)
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-05-18 : 04:02:11

I don't know how you did it, God of SQL, LOL.... Even with the answer you given, I still having difficulty trying to digest it. I must take some time to do that...

Thanks, and below is my solution that I managed to get based on your tips, thanks for your amazing tips. When I go to SG, I'll make sure I will call you out and treat you a nice dinner

Simplified as below:


INSERT INTO Leaves (someColumns)
SELECT @EmpID,
FROM F_TABLE_DATE(@DateFrom, @DateTo) 

LEFT OUTER JOIN Holidays ON F_TABLE_DATE.DATE = Holidays.HolidayDate
WHERE 
  @ExcludeHolidays = 0
	OR	(
		@ExcludeHolidays = 1 AND (
		datename(weekday, [DATE]) <> 'Sunday'
		AND datename(weekday, [DATE]) <> 'Saturday'
		AND Holidays.HolidayDate IS NULL))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources