Stored proc throwing error

sridharcmk

Hi experts,

I have a requirement, need to compare data between two tables where each table contain single record and based on validation need to throw an error. I written a stored procedure and when ran it, omg it is showing errors from line 1. I am new to Vertica. Below is the skeleton of my proc. Could you please advice on where I am going wrong?

create or replace procedure proc_name() language PLvSQL AS $$
DECLARE
t1_var1 int;
t1_var2 int;
t2_var1 int;
t2_var2 int;
begin
select col1 into t1_var1,col2 into t1_var2 from tab1;
select col1 into t2_var1,col2 into t2_var2 from tab2;
if
t1_var1!=t2_var1 or
t1_var2!=t2_var2
raise exception 'count not matched'
end if;
end
$$;

appreciate your help.
Thanks,
Sri

sridharcmk

Hello Sruthi,

Appreciate your quick response. I made changes as per your reply and ran, but no luck. I am running it in DB Vizualizer.
Got below errors
[Vertica]VJDBC Missing a matching closing delimiter in statement create or replace procedure proc_name() language PLvSQL AS $$
DECLARE
t1_var1 int;
[Vertica]VJDBC ERROR: Syntax error at or near "t1_var1" so on.

SruthiA

I had used vsql earlier. if you want to create store procedure you need to add delimiter at the start and end as follows so that it will be treated as 1 statement

SruthiA

@sridharcmk : There are couple of issues in your stored procedure

1) semi colon is missing for end
2) then is missing in the if condition
3) we cannot use "into" clause to assign values for variables declared.

Please find the corrected stored procedure below

create or replace procedure proc_name() language PLvSQL AS $$
DECLARE
t1_var1 int;
t1_var2 int;
t2_var1 int;
t2_var2 int;
begin
t1_var1:= select col1 from tab1;
t1_var2:= select col2 from tab1;
t2_var1:= select col1 from tab2;
t2_var2:= select col2 from tab2;
if t1_var1!=t2_var1 or t1_var2!=t2_var2 then
raise exception 'count not matched';
end if;
end;
$$;

eonv1111=> call proc_name();
ERROR 2005: count not matched
CONTEXT: PL/vSQL procedure proc_name line 13 at RAISE
eonv1111=>

eonv1111=> select * from tab1;
col1 | col2
------+------
1 | 2
(1 row)

eonv1111=> select * from tab2;
col1 | col2
------+------
3 | 4
(1 row)

eonv1111=>

sridharcmk

Hello Sruthi,

Now it is executing fine.
I marked your answer as correct.

Thanks,
Sri

SruthiA

good to know that it is working fine.