Alter 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

Transact-SQL (2000)

Alter Procedure ??

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-04-08 : 05:12:49

Hi

I have a situation where I need to Alter a Stored Procedure on a monthly basis and I would like to get rid of the 'Human Touch'. I want to modify the date part of the following code:


CREATE PROCEDURE get_load_periodx

@load_period datetime out

AS
   SET @load_period = '20050101'

GO

I was trying to alter it using the following code. If there's an easier way please let me know:

Declare @Load_Period datetime	,
	@nSql NVarchar (150)	,
	@VDate varchar(20)


Exec get_load_periodx 
@Load_Period out

Select @Load_Period as [Load Period]

If Month(@Load_Period) In (1,3,5,7,8,10,12)
	Begin
		Set @Load_Period = @Load_Period + 31
	End
Else
	If Month(@Load_Period) In (4,6,9,11)
		Begin
			Set @Load_Period = @Load_Period + 30
		End
	Else
		Begin
			Set @Load_Period = @Load_Period + 28
		End

Select @Load_Period
Set @nSql = 'Alter Proc get_load_periodx
@Load_Period Datetime  Out
as
Set @Load_Period = ' + @Load_Period

Exec Sp_Executesql @nSql

Select @Load_Period

I can't execute it because the @Load_Period is a datetime datatype and I'm trying to add it to a nvarchar. I also tried casting it(Load_Period) to a varchar but when it has to execute the Stored Procedure can't accept it because it's now a varchar but the SP is expecting a datetime.

You can't teach an old mouse new clicks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 10:35:01

You shouldn't need to alter your proc every month. What rules determines what @Load_Period should be?
If it's as simple as the first day of current month or previous month or next month, use one of these statements:

--To return first day of current month as a datetime value
Select dateAdd(month, dateDiff(month,0,getDate()) ,0)

--To return first day of previous month as a datetime value
Select dateAdd(month,-1,dateAdd(month, dateDiff(month,0,getDate()) ,0))

--To return first day of next month as a datetime value
Select dateAdd(month,1,dateAdd(month, dateDiff(month,0,getDate()) ,0))

The dateadd function can also replace your algorithm or adding however many days is in @load_period month to @load_period.

--To add 1 month to any date
Select dateAdd(month, 1, @Load_Period)

If no rules govern what what @load_period should be at any given point other than what is hard coded in the proc, then store the value in a table instead. Then you can use one of these statemnts to update the value whenever its needed.

Be One with the Optimizer
TG

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-04-11 : 03:34:04

The load period has to be changed every month when we do an update on our DBs.
The @load_Period is used as a reference for the update period. We currently have to change the hard-coded date to correspond with the new period (which is always + 1 month to the what ever the load_period is at the time.

I guess using a table to store the load period would be much easier than altering a procedure every month.
I'm rewriting some of the procedures that the company has been using for years and I guess sometimes I get caught up in their way of thinking.

Thanks.

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources