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

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-15 : 15:50:24

I want to write a stored procedure
where if @fromdate and @todate parameters are passed it should return the data based on date parameters
If nothing is passed it should return all the data

Please help the below code to rewrite

select * from table where
(dt Between @FROMDATE And @TODATE)
AND (dt Between @FROMDATE And @TODATE)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 15:52:41

declare @FROMDATE datetime, @TODATE datetime
select @FROMDATE = isnull(@FROMDATE, '17530101'), @TODATE = isnull(@TODATE, '99991231')

select * from table
where dt between @FROMDATE And @TODATE

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-15 : 17:27:31

Sorry if i m not clear about my issue.

I have write a store procedure with two conditions
1.If @datefrom and @dateparameters values are passed sp should returned the values based on date values supplied.
like(select * from table
where dt between @FROMDATE And @TODATE)
2.If no parameters are passed or null parameters are passed then it should retured the output like( select * from table)

Create procedure usp_dtproc
@fromdt datetime,
@todt datetime
as
if parameter value is fromdt and todt is true
select * from table
where dt between @FROMDATE And @TODATE
else
If parameter values are null then
it should execute the below query

select * form table

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-15 : 17:27:31

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 17:51:23

have you even tried my query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-15 : 19:32:43

Thanks i got the desired results....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 20:00:28

no kidding?

but do you know why?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 00:28:17

Other method

select * from table
where dt>=isnull(@FROMDATE,dt) And dt<=isnull(@TODATE,dt)

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2007-06-16 : 00:36:07

Hi,

Declare @F datetime , @T datetime
--Select @f ='2016-03-12 00:00:00.000', @t = '2020-03-31 00:00:00.000'
Select @f = NULL , @T = NULL

Select * From TableName
where ( ( @F is Null or Charge_From > = @f ) and (@T is Null or Charge_To <= @T))

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-06-16 : 13:30:56

When i tried with the below query i m not getting the desired output...when passing the nulls..

declare @FROMDATE datetime, @TODATE datetime
select @FROMDATE = isnull(@FROMDATE, '17530101'), @TODATE = isnull(@TODATE, '99991231')

select * from table where
CAST(CAST(YR AS VARCHAR) + RIGHT('0' + CAST(MM AS VARCHAR), 2) + RIGHT('0' + CAST(DD AS VARCHAR), 2) AS DATETIME) Between @FROMDATE And @TODATE

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-06-17 : 14:28:19

Hi,
How about this one?

Select * from table where
((@fromdate is null) and (@todate is null)or (dt between @fromdate and @todate))

But while executing the stored proc if the parameters are null
you need to write like this

exec dtproc @fromdate=null,@todate=null

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-18 : 01:14:03

quote:
Originally posted by sqldba2k6

When i tried with the below query i m not getting the desired output...when passing the nulls..

declare @FROMDATE datetime, @TODATE datetime
select @FROMDATE = isnull(@FROMDATE, '17530101'), @TODATE = isnull(@TODATE, '99991231')

select * from table where
CAST(CAST(YR AS VARCHAR) + RIGHT('0' + CAST(MM AS VARCHAR), 2) + RIGHT('0' + CAST(DD AS VARCHAR), 2) AS DATETIME) Between @FROMDATE And @TODATE

Dont use convert and try

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources