Passing DateAdd Parameters Via Sproc

Hello
I am kinda stumped with this one and would appreciate any suggestions.
I want to pass two parameters (@.period_unit and @.period_type) so that the
sproc
can calculate the exiry date which is inserted into the table. Below is some
psedo code
for the sproc in question. I envisaged calling the sproc as such ...
EXEC MYPROC 20, 100, 200, 'year', 1, 1
or
EXEC MYPROC 20, 100, 200, 'month', 6, 1
However, when I try and save the sproc I get the error
"invalid parameter 1 specified for dateadd". If I "hard code" the
@.date_expires_utc variable to year, it works fine meaning that the
dateadd function accepts the value @.period_unit as it's second
parameter but doesn't accept @.period_type as it's first parameter.
Go figure! Anyway, any and all suggestions most welcome and encouraged.
PS: I realize that I could pass the creation and expiry date from the
calling code
but I would prefer the sproc to calculate these dates.
Thanks Peter
---
CREATE TABLE MYTABLE (
coupon_id integer identity(1, 1) not null,
member_id integer not null,
transaction_id integer not null,
status char(1) not null,
date_created_utc smalldatetime not null,
date_expires_utc smalldatetime not null
)
CREATE PROCEDURE MYPROC
@.count integer,
@.member_id integer,
@.transaction_id integer,
@.period_unit integer,
@.period_type varchar(20),
@.status char(1)
AS
SET NOCOUNT ON
DECLARE @.date_created_utc smalldatetime
DECLARE @.date_expires_utc smalldatetime
DECLARE @.counter integer
SET @.date_created_utc = GetUtcDate()
SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
@.date_created_utc)
SET @.counter = 1
WHILE (@.counter <= @.count)
BEGIN
INSERT INTO MYTABLE (
[member_id],
[transaction_id],
[status],
[date_created_utc],
[date_expires_utc] )
VALUES (
@.member_id,
@.transaction_id,
@.status,
@.date_created_utc,
@.date_expires_utc )
SET @.counter = @.counter + 1
END
SELECT coupon_id
FROM MYTABLE
WHERE transaction_id = @.transaction_id
ORDER BY coupon_id
---
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Hi, Peter
Instead of:
SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
@.date_created_utc)
You can use:
IF @.period_unit='year'
SET @.date_expires_utc = DateAdd(year, @.period_unit,
@.date_created_utc)
IF @.period_unit='month'
SET @.date_expires_utc = DateAdd(month, @.period_unit,
@.date_created_utc)
[...]
Razvan|||Razvan
Thanks for that. It is, in fact, exactly what I ended up doing although it
seems a little messy.
I still find it a little strange that the dateadd function accepts an sproc
parameter as it's own second
parameter but not as its first parameter.
Regards
Peter
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139386825.679863.20140@.f14g2000cwb.googlegroups.com...
> Hi, Peter
> Instead of:
> SET @.date_expires_utc = DateAdd(@.period_unit, @.period_unit,
> @.date_created_utc)
> You can use:
> IF @.period_unit='year'
> SET @.date_expires_utc = DateAdd(year, @.period_unit,
> @.date_created_utc)
> IF @.period_unit='month'
> SET @.date_expires_utc = DateAdd(month, @.period_unit,
> @.date_created_utc)
> [...]
> Razvan
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--