Issue with Stored procedure

[Former Member]

Hi,

I am trying to create a stored procedure and am trying to execute it in 2 different ways.. One works and the other throws a syntax error.

Can you please explain why the first one has issues and the second one works fine? Also please suggest what the correction in first one should be.

[justin_molenaur2 · Accepted solution]

I think you are trying to compare a table type in you where clause, which may be incompatible. Can you try using a scalar variable instead? See sample here.

varYear = VARCHAR(8);

BEGIN

SELECT YEAR(CURRENT_DATE)

INTO varYear

FROM DUMMY;

<REST OF LOGIC>

Regards,

Justin

[Former Member]

Thanks guys. Issue resolved.

[former_member182302]

Hi Sammy,

You cannot compare with Table Variable in your WHERE Clause, instead you have to use SCALAR Variable

As Justin rightly mentioned you should SCALAR Variable.

DECLARE var1 INTEGER;

SELECT YEAR(CURRENT_DATE) INTO var1 FROM DUMMY;

YEAR1 = select "YEAR","HIKE" from HIKE_YEAR
WHERE YEAR = :var1;

Regards,

Krishna Tangudu