Passing a variable to a function

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 2005 Forums

Transact-SQL (2005)

Passing a variable to a function

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 10:03:59

How do I use a varible in place of the "dd", or "hh" as I would like to pass the SP the amount of time to look back. Heres an example:

ALTER PROCEDURE [dbo].[CHSP_OR_UPDATES_001] @Interval VARCHAR(5) = "dd"

AS

DECLARE @DATETHEN datetime
SET @DATETHEN = DATEADD(@Interval, - 1, @DATENOW)

The DateAdd function won't work.

Once again many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 10:16:12

you've either use dynamic sql or like below


ALTER PROCEDURE [dbo].[CHSP_OR_UPDATES_001] 
@Interval VARCHAR(5) = 'dd'

AS


DECLARE @DATETHEN datetime
SET @DATETHEN = CASE @Interval
                WHEN 'hh' THEN DATEADD(hh, - 1, @DATENOW)
                WHEN 'dd' THEN DATEADD(dd, - 1, @DATENOW)
                WHEN 'ss' THEN DATEADD(ss, - 1, @DATENOW)
                ...
               END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 10:21:36

Also, 1 hour is 3600 seconds and 1 day is 86400 seconds.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 10:23:13

Using dynamic:

alter PROCEDURE [dbo].[CHSP_OR_UPDATES_001]
@Interval VARCHAR(5) = 'dd'

AS
declare @SQLcmd varchar(1000)

SET @SQLcmd = 'Select DATEADD('+@Interval+', -1, getdate()) '

Exec(@SQLcmd)

GO
--Usage:
Exec [dbo].[CHSP_OR_UPDATES_001] 'hh'
Exec [dbo].[CHSP_OR_UPDATES_001] 'dd'

eddy556
Starting Member

36 Posts

Posted - 2009-01-13 : 10:37:53

Ahh yes visakh16, sorry you've actually given me that code before but due to massively changing what I had it got lost in there somewhere. Many thanks again :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 10:38:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources