Stored procedure - how to set default value for variable with data type?

General
N/A

Stored procedure - how to set default value for variable with data type?

Hello, could you please help me with this case? I'm new in creating procedures with variables and parameters.

I'm trying to create procedure which will insert values to table.

CREATE PROCEDURE db.insert_proc()

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE d DATE DEFAULT '2014-12-29';

WHILE d<current_date DO

INSERT INTO db.insert_table

SELECT * FROM db.select_table s

WHERE s.day_id=d+i;

END WHILE;

END

I'm trying to make a portions of my insert operations only with mondays (while do cycle). But that doesn't work. Where am i wrong?

Thanks.

3 REPLIES
N/A

Re: Stored procedure - how to set default value for variable with data type?

I'm sorry. The right syntax below:

CREATE PROCEDURE db.insert_proc()

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE d DATE DEFAULT '2014-12-29';

WHILE d<current_date DO

INSERT INTO db.insert_table

SELECT * FROM db.select_table s

WHERE s.day_id=d+7*i;

END WHILE;

END

Teradata Employee

Re: Stored procedure - how to set default value for variable with data type?

You set a starting date but never increment it. What is the purpose of "i"? You never change that variable's value either.

(And best practice is to use a date constant for that starting value, e.g. date'2014-12-29' rather than a character constant without the date keyword.)

N/A

Re: Stored procedure - how to set default value for variable with data type?

CREATE PROCEDURE db.insert_proc()

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE d DATE DEFAULT '2014-12-29';

WHILE d<current_date DO

INSERT INTO db.insert_table

SELECT * FROM db.select_table s

WHERE s.day_id=:d+7*:i;

SET i=i+1;

END WHILE;

END

Is it right now?

And i want to ask - should i declare variables in teradata if it be used at the ms sql server?

For example I need to execute this procedure thought the ms sql, i put it to ms sql coder

BEGIN

DECLARE @d as date DEFAULT '2014-12-29';

DECLARE @i as INT DEFAULT 1;

WHILE d<current_date DO

EXEC ('db.insert_proc()') @d, @i AT TERADATA

SET i=i+1;

END WHILE;

END

before to execute procedure should i create this? is it right?

CREATE PROCEDURE db.insert_proc()



BEGIN



INSERT INTO db.insert_table

SELECT * FROM db.select_table s

WHERE s.day_id=:d+7*:i;

END