I am trying to create the following procedure in Aginity workbench with DBMS PostgreSQL 8.0.2
CREATE PROCEDURE Step1_data_main
(
@startdate NVARCHAR(MAX),
@enddate NVARCHAR(MAX),
@season NVARCHAR(MAX)
) AS
SELECT level1_idnt,day_dt, sum(sls_qty) as sum_units,sum(sls_amnt_price) as sum_sales
FROM md1.loc_sku_dy_act_pos_full_v2
WHERE seasn_cd = '+@season+' and day_dt >= ''+@startdate+'' and day_dt <= ''+@enddate+''
GROUP_BY level1_idnt, day_dt;
END
EXEC Step1_data_main
'2015-03-01 00:00:00',
'2015-09-30 00:00:00',
'2'
GO
However I am getting the following error
syntax error at or near "PROCEDURE"
Any guidance on this will be greatly appreciated
EDIT
I am working in Aginity Workbench with PostgreSQL I had previously incorrectly said I am using mysql workbench.
Pavel Zimogorov :
This should work for you: \n\nCREATE PROCEDURE `Step1_data_main`( \n startdate NVARCHAR(1000),\n enddate NVARCHAR(1000),\n season NVARCHAR(1000)\n)\nbegin\n\nSELECT level1_idnt,day_dt, sum(sls_qty) as sum_units,sum(sls_amnt_price) as sum_sales\nFROM md1.loc_sku_dy_act_pos_full_v2 \nWHERE seasn_cd = cast(season as UNSIGNED) and \n day_dt >= cast(startdate as datetime) and \n day_dt <= cast(enddate as datetime)\nGROUP BY level1_idnt, day_dt;\n\nEND$$\n",
2016-04-21T11:01:55