# PROCEDURE WITH NO BODY, works with babel but not with sql
#create procedure sp_test AS BEGIN END;

# PROCEDURE WITH NO PARAMS
create table temp_sp2(a int);
CREATE PROCEDURE sp_test AS BEGIN insert into temp_sp2 values(1); END;
storedproc#!#prep#!#sp_test#!#
SELECT * FROM temp_sp2;
drop table temp_sp2;
drop Procedure sp_test;


# PROCEDURE WITH INPUT PARAMETER
#drop table temp_sp;
create table temp_sp(a int);


Create Procedure stored_proc1 (@a int) As Begin insert into temp_sp values(@a) End;
# NOT WORKING FOR BABEL,RAISED JIRA 444
storedproc#!#prep#!#stored_proc1#!#int|-|a|-|-100|-|input

# MISMATCH IN RETURN VALUES
exec stored_proc1 -200
exec stored_proc1 0
exec stored_proc1 -1
exec stored_proc1 2

# filed Jira on this, doesnt work for babel
#exec stored_proc1 2.2


SELECT * FROM temp_sp;
DROP table temp_sp;
DROP Procedure stored_proc1

# input parameter
CREATE PROCEDURE sp_test1 (@a  INT) AS BEGIN SET @a=100; Select @a as a; END;
exec sp_test1 2
Declare @a int;Set @a=1; exec sp_test1 @a;select @a as a;
storedproc#!#prep#!#sp_test1#!#int|-|a|-|1|-|input
storedproc#!#prep#!#sp_test1#!#int|-|a|-|10|-|input
DROP PROCEDURE sp_test1

# TESTING OUT PARAMETERS FOR ALL THE DATATYPES

# int
CREATE PROCEDURE sp_test1 (@a  INT OUTPUT) AS BEGIN SET @a=100; Select @a as a; END;
exec sp_test1 2
Declare @a int;Set @a=1; exec sp_test1 @a;select @a as a;
storedproc#!#prep#!#sp_test1#!#int|-|a|-|1|-|output
storedproc#!#prep#!#sp_test1#!#int|-|a|-|1|-|inputoutput
DROP PROCEDURE sp_test1

# smallint
CREATE PROCEDURE sp_test2 (@a SMALLINT OUTPUT) AS BEGIN SET @a=100; Select @a as a; END;
exec sp_test2 2
Declare @a smallint;Set @a=1; exec sp_test2 @a;select @a as a;
storedproc#!#prep#!#sp_test2#!#smallint|-|a|-|10|-|output
storedproc#!#prep#!#sp_test2#!#smallint|-|a|-|10|-|inputoutput
DROP PROCEDURE sp_test2

# bigint
CREATE PROCEDURE sp_test3 (@a BIGINT OUTPUT) AS BEGIN SET @a=100; Select @a as a; END;
exec sp_test3 2
Declare @a bigint;Set @a=1; exec sp_test3 @a;select @a as a;
storedproc#!#prep#!#sp_test3#!#bigint|-|a|-|10|-|output
storedproc#!#prep#!#sp_test3#!#bigint|-|a|-|10|-|inputoutput
DROP PROCEDURE sp_test3

# tinyint
CREATE PROCEDURE sp_test4 (@a tinyint OUTPUT) AS BEGIN SET @a=100; Select @a as a; END;
exec sp_test4 4
Declare @a bigint;Set @a=1; exec sp_test4 @a;select @a as a;
storedproc#!#prep#!#sp_test4#!#tinyint|-|a|-|10|-|output
storedproc#!#prep#!#sp_test4#!#tinyint|-|a|-|10|-|inputoutput
DROP PROCEDURE sp_test4

# float
CREATE PROCEDURE sp_test5 (@a float OUTPUT) AS BEGIN SET @a=100.12; Select @a as a; END;
exec sp_test5 2.1
Declare @a float;Set @a=1.1; exec sp_test5 @a;select @a as a;
storedproc#!#prep#!#sp_test5#!#float|-|a|-|10.1|-|output
storedproc#!#prep#!#sp_test5#!#float|-|a|-|10.1|-|inputoutput
DROP PROCEDURE sp_test5

# varchar
CREATE PROCEDURE sp_test6 (@a varchar OUTPUT) AS BEGIN SET @a='helloworld'; Select @a as a; END;
exec sp_test6 'hello'
Declare @a varchar;Set @a='hello'; exec sp_test6 @a;select @a as a;
storedproc#!#prep#!#sp_test6#!#varchar|-|a|-|hello|-|output
storedproc#!#prep#!#sp_test6#!#varchar|-|a|-|hello|-|inputoutput
DROP PROCEDURE sp_test6

# char BABEL-705
#CREATE PROCEDURE sp_test7 (@a char OUTPUT) AS BEGIN SET @a='b'; Select @a as a; END;
#exec sp_test7 'a'
#Declare @a varchar;Set @a='h'; exec sp_test7 @a;select @a as a;
#storedproc#!#prep#!#sp_test7#!#char|-|a|-|t|-|output
#storedproc#!#prep#!#sp_test7#!#char|-|a|-|t|-|inputoutput
#DROP PROCEDURE sp_test7

# nchar BABEL-708
#CREATE PROCEDURE sp_test8 (@a nchar OUTPUT) AS BEGIN SET @a='helloworld'; Select @a as a; END;
#exec sp_test8 'a'
#Declare @a nchar;Set @a='hello'; exec sp_test8 @a;select @a as a;
#storedproc#!#prep#!#sp_test8#!#nchar|-|a|-|t|-|output
#storedproc#!#prep#!#sp_test8#!#nchar|-|a|-|t|-|inputoutput
#DROP PROCEDURE sp_test8

# nvarchar
CREATE PROCEDURE sp_test9 (@a nvarchar OUTPUT) AS BEGIN SET @a='helloworld'; Select @a as a; END;
exec sp_test9 'hello'
Declare @a nvarchar;Set @a='hello'; exec sp_test9 @a;select @a as a;
storedproc#!#prep#!#sp_test9#!#varchar|-|a|-|hello|-|output
storedproc#!#prep#!#sp_test9#!#varchar|-|a|-|hello|-|inputoutput
DROP PROCEDURE sp_test9

# numeric 
#CREATE PROCEDURE sp_test10 (@a numeric(10,4) OUTPUT) AS BEGIN SET @a=100.41; Select @a as a; END;
#exec sp_test10 2.000
#Declare @a numeric(10,4);Set @a=10.04; exec sp_test10 @a;select @a as a;
#storedproc#!#prep#!#sp_test10#!#numeric|-|a|-|123|-|10|-|3|-|output
#storedproc#!#prep#!#sp_test10#!#numeric|-|a|-|123|-|10|-|2|-|inputoutput
#DROP PROCEDURE sp_test10

# decimal 
CREATE PROCEDURE sp_test11 (@a decimal(10,4) OUTPUT) AS BEGIN SET @a=100.41; Select @a as a; END;
exec sp_test11 2.00
Declare @a decimal(10,4);Set @a=10.04; exec sp_test11 @a;select @a as a;
storedproc#!#prep#!#sp_test11#!#decimal|-|a|-|123|-|10|-|3|-|output
storedproc#!#prep#!#sp_test11#!#decimal|-|a|-|123|-|10|-|2|-|inputoutput
DROP PROCEDURE sp_test11

# binary 
#CREATE PROCEDURE sp_test12 (@a binary OUTPUT) AS BEGIN SET @a=0x121; Select @a as a; END;
#exec sp_test12 0x122
#Declare @a binary;Set @a=0x121; exec sp_test12 @a;select @a as a;
#storedproc#!#prep#!#sp_test12#!#binary|-|a|-|0x122|-|output
#storedproc#!#prep#!#sp_test12#!#binary|-|a|-|0x122|-|inputoutput
#DROP PROCEDURE sp_test12

# varbinary BABEL-701
#CREATE PROCEDURE sp_test13 (@a varbinary OUTPUT) AS BEGIN SET @a=0x121; Select @a as a; END;
#exec sp_test13 0x122
#Declare @a varbinary;Set @a=0x122; exec sp_test13 @a;select @a as a;
#storedproc#!#prep#!#sp_test13#!#varbinary|-|a|-|0x122|-|output
#storedproc#!#prep#!#sp_test13#!#varbinary|-|a|-|0x122|-|inputoutput
#DROP PROCEDURE sp_test13

# date
CREATE PROCEDURE sp_test14 (@a date output) AS BEGIN SET @a='1999-1-3'; Select @a as a; END;
exec sp_test14 '9999-12-31'
Declare @a DATE;Set @a='9999-12-31'; exec sp_test14 @a;select @a as a;
storedproc#!#prep#!#sp_test14#!#DATE|-|a|-|9999-12-31|-|output
storedproc#!#prep#!#sp_test14#!#DATE|-|a|-|9999-12-31|-|inputoutput
DROP PROCEDURE sp_test14

# time
CREATE PROCEDURE sp_test15 (@a time(4) OUTPUT) AS BEGIN SET @a='11:25:07.123'; Select @a as a; END;
exec sp_test15 '12:45:37.123'
Declare @a Time;Set @a='12:45:37.123'; exec sp_test15 @a;select @a as a;
storedproc#!#prep#!#sp_test15#!#Time|-|a|-|12:45:37.123|-|3|-|output
storedproc#!#prep#!#sp_test15#!#Time|-|a|-|12:45:37.123|-|3|-|inputoutput
DROP PROCEDURE sp_test15

# dateime
CREATE PROCEDURE sp_test16 (@a datetime output) AS BEGIN SET @a='2004-05-18 13:59:59.995'; Select @a as a; END;
exec sp_test16 '2000-02-28 23:59:59.995'
Declare @a DATETIME;Set @a='2000-02-28 23:59:59.995'; exec sp_test16 @a;select @a as a;
storedproc#!#prep#!#sp_test16#!#DATETIME|-|a|-|2000-02-28 23:59:59.995|-|output
storedproc#!#prep#!#sp_test16#!#DATETIME|-|a|-|2000-02-28 23:59:59.995|-|inputoutput
DROP PROCEDURE sp_test16

# datetime2 # Mismatch BABEL-708
CREATE PROCEDURE sp_test17 (@a datetime2(5) OUTPUT) AS BEGIN SET @a='2014-10-2 1:45:37.123456'; Select @a as a; END;
exec sp_test17 '2016-10-23 12:45:37.123456'
Declare @a Datetime2;Set @a='2016-10-23 12:45:37.123456'; exec sp_test17 @a;select @a as a;
storedproc#!#prep#!#sp_test17#!#Datetime2|-|a|-|2016-10-23 12:45:37.123456|-|5|-|output
storedproc#!#prep#!#sp_test17#!#Datetime2|-|a|-|2016-10-23 12:45:37.123456|-|5|-|inputoutput
DROP PROCEDURE sp_test17

# smalldatetime
CREATE PROCEDURE sp_test18 (@a smalldatetime output) AS BEGIN SET @a='2010-02-03 12:58:23'; Select @a as a; END;
exec sp_test18 '2005-02-19 02:58:23'
Declare @a SMALLDATETIME;Set @a='2000-12-13 12:58:23'; exec sp_test18 @a;select @a as a;
storedproc#!#prep#!#sp_test18#!#SMALLDATETIME|-|a|-|2000-12-13 12:58:23|-|output
storedproc#!#prep#!#sp_test18#!#SMALLDATETIME|-|a|-|2000-12-13 12:58:23|-|inputoutput
DROP PROCEDURE sp_test18

# UID
#CREATE PROCEDURE sp_test19 (@a uniqueidentifier OUTPUT) AS BEGIN SET @a='ce8af10a-2709-43b0-9e4e-a02753929d17'; Select @a as a; END;
#exec sp_test19 '5b7c2e8d-6d90-411d-8e19-9a81067e6f6c'
#Declare @a uniqueidentifier;Set @a='5b7c2e8d-6d90-411d-8e19-9a81067e6f6c'; exec sp_test19 @a;select @a as a;
#storedproc#!#prep#!#sp_test19#!#uniqueidentifier|-|a|-|5b7c2e8d-6d90-411d-8e19-9a81067e6f6c|-|output
#storedproc#!#prep#!#sp_test19#!#uniqueidentifier|-|a|-|5b7c2e8d-6d90-411d-8e19-9a81067e6f6c|-|inputoutput
#DROP PROCEDURE sp_test19

# ALTER STATEMENT FOR babel fails
#Alter procedure sp_test (@a int OUTPUT) AS Begin Set @a=100; Select @a as a; END;

DROP PROCEDURE sp_test20
CREATE PROCEDURE sp_test20 ( @one int, @two int ) AS BEGIN SELECT @one, @two; END;
DECLARE @one int = 100; DECLARE @two int = 200; EXECUTE sp_test20 @one = @one, @two = @two;
DECLARE @one int = 100; DECLARE @two int = 200; EXECUTE sp_test20 @one = @two, @two = @one;
storedproc#!#prep#!#sp_test20#!#int|-|one|-|100|-|input#!#int|-|two|-|200|-|input
DROP PROCEDURE sp_test20

DROP PROCEDURE sp_test21
CREATE PROCEDURE sp_test21 ( @one varchar(30), @two int ) AS BEGIN SELECT @one, @two; END;
EXECUTE sp_test21 @one = "hello", @two = 1;
#EXECUTE sp_test21 @two = 1, @one = "hello"; ##### Babel 2392
storedproc#!#prep#!#sp_test21#!#varchar|-|one|-|hello|-|input#!#int|-|two|-|200|-|input
DROP PROCEDURE sp_test21

CREATE PROCEDURE dbo.sp_test22 ( @one int, @two int ) AS BEGIN SELECT @one, @two; END;
DECLARE @one int = 100; DECLARE @two int = 200; EXECUTE sp_test22 @one = @one, @two = @two;
storedproc#!#prep#!#sp_test22#!#int|-|one|-|100|-|input#!#int|-|two|-|200|-|input
storedproc#!#prep#!#dbo.sp_test22#!#int|-|one|-|100|-|input#!#int|-|two|-|200|-|input
DROP PROCEDURE dbo.sp_test22

# Test (23): Stored Procedure on a non-default schema
CREATE SCHEMA testschema

CREATE PROCEDURE testschema.sp_test23 ( @one int, @two int ) AS BEGIN SELECT @one, @two; END;
DECLARE @one int = 100; DECLARE @two int = 200; EXECUTE testschema.sp_test23 @one = @one, @two = @two;
storedproc#!#prep#!#testschema.sp_test23#!#int|-|one|-|100|-|input#!#int|-|two|-|200|-|input

# Should fail because sp_test23 does not exist in default schema
storedproc#!#prep#!#sp_test23#!#int|-|one|-|100|-|input#!#int|-|two|-|200|-|input

# create one with the same name as previous one but different schema
CREATE PROCEDURE dbo.sp_test23 ( @one varchar(30), @two int ) AS BEGIN SELECT @one, @two; END;
EXECUTE sp_test23 @one = "hello", @two = 1;
storedproc#!#prep#!#sp_test23#!#varchar|-|one|-|hello|-|input#!#int|-|two|-|200|-|input
storedproc#!#prep#!#dbo.sp_test23#!#varchar|-|one|-|hello|-|input#!#int|-|two|-|200|-|input

DROP PROCEDURE testschema.sp_test23
DROP PROCEDURE dbo.sp_test23
DROP SCHEMA testschema
# Test (23): End

# Test (24): Test Stored procedures with reordered parameter list
CREATE PROCEDURE sp_test24 (@a INT, @b INT OUTPUT) AS BEGIN SET @b=100; SET @a=1000; Select @a as a, @b as b; END;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @b=@b OUT, @a=@a;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @b=@b, @a=@a OUT;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @b=@b OUT, @a=@a OUT;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @b=@b, @a=@a;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @a=@a, @b=@b;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @a=@a, @b=@b OUT;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @a=@a OUT, @b=@b;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @a=@a OUT, @b=@b OUT;select @a as a, @b as b;
Declare @a int;Declare @b int;Set @a=20;Set @b=10; exec sp_test24 @a OUT, @b OUT;select @a as a, @b as b;
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|output
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|output#!#int|-|b|-|10|-|input
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|input
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|output#!#int|-|b|-|10|-|output
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|inputoutput#!#int|-|b|-|10|-|inputoutput
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|inputoutput
storedproc#!#prep#!#sp_test24#!#int|-|a|-|20|-|inputoutput#!#int|-|b|-|10|-|input

DROP PROCEDURE sp_test24

CREATE PROCEDURE sp_test25 (@a INT, @b INT OUTPUT, @c INT OUTPUT, @d INT) AS BEGIN SET @a=100; SET @b=200; SET @c=300; SET @d=400; Select @a as a, @b as b, @c as c, @d as d; END;
Declare @a int;Declare @b int;Declare @c int;Declare @d int;Set @a=20;Set @b=10;Set @c=5;Set @d=30; exec sp_test25 @b=@b OUT, @c=@c OUT, @a=@a, @d=@d; Select @a as a, @b as b, @c as c, @d as d;
Declare @a int;Declare @b int;Declare @c int;Declare @d int;Set @a=20;Set @b=10;Set @c=5;Set @d=30; exec sp_test25 @b=@b, @c=@c, @a=@a, @d=@d; Select @a as a, @b as b, @c as c, @d as d;
Declare @a int;Declare @b int;Declare @c int;Declare @d int;Set @a=20;Set @b=10;Set @c=5;Set @d=30; exec sp_test25 @c=@c OUT, @b=@b OUT, @a=@a, @d=@d; Select @a as a, @b as b, @c as c, @d as d;
Declare @a int;Declare @b int;Declare @c int;Declare @d int;Set @a=20;Set @b=10;Set @c=5;Set @d=30; exec sp_test25 @c=@c, @b=@b, @a=@a OUT, @d=@d OUT; Select @a as a, @b as b, @c as c, @d as d;
storedproc#!#prep#!#sp_test25#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|inputoutput#!#int|-|c|-|10|-|input#!#int|-|d|-|10|-|inputoutput
storedproc#!#prep#!#sp_test25#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|output#!#int|-|c|-|10|-|input#!#int|-|d|-|10|-|output
storedproc#!#prep#!#sp_test25#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|output#!#int|-|c|-|10|-|input#!#int|-|d|-|10|-|inputoutput
storedproc#!#prep#!#sp_test25#!#int|-|a|-|20|-|input#!#int|-|b|-|10|-|inputoutput#!#int|-|c|-|10|-|input#!#int|-|d|-|10|-|output

DROP PROCEDURE sp_test25
# Test (24): End

# money
CREATE PROCEDURE sp_test_money (@a money OUTPUT) AS BEGIN  SET @a=12345.6789; Select @a as a; END;
exec sp_test_money 1234.56
Declare @a money; Set @a=9876.54; exec sp_test_money @a;select @a as a;
storedproc#!#prep#!#sp_test_money#!#money|-|a|-|1234.56|-|output
storedproc#!#prep#!#sp_test_money#!#money|-|a|-|1234.56|-|inputoutput
DROP PROCEDURE sp_test_money

# smallmoney
CREATE PROCEDURE sp_test_smallmoney (@a smallmoney OUTPUT) AS BEGIN  SET @a=214.3256;Select @a as a; END;
exec sp_test_smallmoney 123.45
Declare @a smallmoney;Set @a=98.76; exec sp_test_smallmoney @a;select @a as a;
storedproc#!#prep#!#sp_test_smallmoney#!#smallmoney|-|a|-|123.45|-|output
storedproc#!#prep#!#sp_test_smallmoney#!#smallmoney|-|a|-|123.45|-|inputoutput
DROP PROCEDURE sp_test_smallmoney

# Test both money and smallmoney parameters together
CREATE PROCEDURE sp_test_both_money (@m money OUTPUT, @sm smallmoney OUTPUT) AS BEGIN SET @m = 12345.6789; SET @sm = 214.3256; SELECT @m as money_val, @sm as smallmoney_val;END;
Declare @m money, @sm smallmoney; Set @m = 9876.54; Set @sm = 98.76; exec sp_test_both_money @m OUTPUT, @sm OUTPUT; select @m as money_val, @sm as smallmoney_val;
storedproc#!#prep#!#sp_test_both_money#!#money|-|m|-|9876.54|-|output#!#smallmoney|-|sm|-|98.76|-|output
storedproc#!#prep#!#sp_test_both_money#!#money|-|m|-|9876.54|-|inputoutput#!#smallmoney|-|sm|-|98.76|-|inputoutput
DROP PROCEDURE sp_test_both_money