All Forums General
joger 9 posts Joined 01/15
07 Jul 2015
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.

joger 9 posts Joined 01/15
07 Jul 2015

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

Fred 1096 posts Joined 08/04
07 Jul 2015

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.)
 

joger 9 posts Joined 01/15
08 Jul 2015

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
 
 
 

You must sign in to leave a comment.