April 8, 2014 at 10:18 am
I am actually working on a report where I need to pass parameters.
I need to pass the parameters HoursStated which could be 5, 10,15 . So when I pass 5 it will give me the details of everything where the Hours worked (which is a integer column) is > HoursStated (Can be 5 , 10 , or 15).
But at the same time I can pass "*" and I should get all the hours which are greater than All ( 5,10, 15)
The Query is
Select * from Production
where
HourWorked > @HoursStated
The above works perfectly fine for individuals .. Don't know how should I add for all three..
April 8, 2014 at 11:14 am
Sharon
I would suggest never using an asterisk like that. You will only have difficulties. Also, always name your columns, never use an asterisk in your query. What happens to the front-end if a column is added to the tables, or removed?
So back to your question:
In your stored procedure, simply use an IF statement
IF @HourStated = 0
BEGIN
SELECT
<ColumnName1>
, <ColumnName2>
, <ColumnNameN>
FROM
Production
WHERE
HourWorked > 15
END
ELSE
BEGIN
SELECT
<ColumnName1>
, <ColumnName2>
, <ColumnNameN>
FROM
Production
WHERE
HourWorked >= @HoursStated
END
Andrew SQLDBA
April 8, 2014 at 12:34 pm
You could make this even simpler by providing a default to HoursStarted.
create procedure GetSomeData
(
@HoursStarted int = 15
) as
Select [Columns]
from Production
where HourWorked > @HoursStated
So now if the user wants "All" just pass in NULL for @HoursStarted. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2014 at 1:04 pm
Sean
That is very nice, I completely forgot about that way. That is very clean and efficient.
Andrew SQLDBA
April 9, 2014 at 4:58 am
then try this one if u want to use *--
create procedure startrred
@hourstarted varchar(10)
as
begin
if(@hourstarted!='*')
begin
Select *
from Production where hourworked>@hourstarted
END
else
begin
select * from Production where hourworked>15
end
end
April 9, 2014 at 7:21 am
super48 (4/9/2014)
then try this one if u want to use *--create procedure startrred
@hourstarted varchar(10)
as
begin
if(@hourstarted!='*')
begin
Select *
from Production where hourworked>@hourstarted
END
else
begin
select * from Production where hourworked>15
end
end
I would NEVER recommend passing in a character that will be implicitly converted to an int like this. One of the issues with this type of thing is what happens when you pass a character string that cannot be converted to an int?
However, if you go with this path keep it simple. There is no need to have separate select statements. This procedure can be greatly simplified to:
create procedure startrred
(
@hourstarted varchar(10)
)as
if @hourstarted = '*' set @hourstarted = '15'
Select *
from Production where hourworked>@hourstarted
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2014 at 10:04 am
Great. Thank y'all.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply