public void AlterProcedure()
{
var statement = Sql.AlterProcedure("proc00", true)
.Parameters(Parameter.Int("@i01"),
Parameter.DateTime("@sd02").DefaultValue(DateTime.Parse("2016-01-01")))
.InputOutputParameters(Parameter.Int("@i03"))
.OutputParameters(Parameter.String("@s04"), Parameter.Bit("@b05"))
.ReturnValue(Parameter.Int("@retVal"))
.As(
Sql.Set("@b05", Sql.Scalar(1)),
Sql.Set("@s04", Sql.Scalar("outputString")),
Sql.Set("@i03", Sql.Name("@i01")),
Sql.Return(123)
);
var command = Utilities.GetCommand(statement);
Assert.IsNotNull(command);
Assert.AreEqual(@"IF OBJECT_ID ( N'[proc00]', N'P' ) IS NULL
BEGIN;
EXEC (N' CREATE PROCEDURE [proc00]
(
@i01 INT OUTPUT,
@sd02 DATETIME = N''2016-01-01T00:00:00'' OUTPUT,
@i03 INT OUTPUT,
@s04 NVARCHAR ( MAX ) OUTPUT,
@b05 BIT OUTPUT
)
AS
BEGIN;
SET @b05 = 1;
SET @s04 = N''outputString'';
SET @i03 = @i01;
RETURN 123;
END;
' );
END;
ELSE BEGIN;
EXEC (N' ALTER PROCEDURE [proc00]
(
@i01 INT OUTPUT,
@sd02 DATETIME = N''2016-01-01T00:00:00'' OUTPUT,
@i03 INT OUTPUT,
@s04 NVARCHAR ( MAX ) OUTPUT,
@b05 BIT OUTPUT
)
AS
BEGIN;
SET @b05 = 1;
SET @s04 = N''outputString'';
SET @i03 = @i01;
RETURN 123;
END;
' );
END;", command.CommandText);
}
public void CreateHelloWithInputWithMainTypesFunction()
{
var statement = Sql.CreateFunction("hello", true)
.InputParameters(Parameter.Text("text_value"))
.InputParameters(Parameter.Int("int_value"))
.InputParameters(Parameter.DateTime("date_time_value"))
.InputParameters(Parameter.VarChar("varchar_value", 45))
.InputParameters(Parameter.VarChar("varchar_max_value"))
.InputParameters(Parameter.Bit("bit_value"))
.InputParameters(Parameter.Real("real_value"))
.InputParameters(Parameter.Money("money_value"))
.ReturnValue(Parameter.Text())
.As(
Sql.Return(Sql.Function("CONCAT", Parameter.Text().Value("Hello "), Parameter.Text("text_value"))
)
);
var command = Provider.GetCommand(statement);
Assert.IsNotNull(command);
Assert.AreEqual("DROP FUNCTION IF EXISTS `hello`;\r\nDELIMITER $$\r\nCREATE FUNCTION `hello`\r\n(\r\n`text_value` TEXT,\r\n`int_value` INTEGER,\r\n`date_time_value` DATETIME,\r\n`varchar_value` VARCHAR ( 45 ),\r\n`varchar_max_value` VARCHAR ( 65535 ),\r\n`bit_value` BIT,\r\n`real_value` REAL,\r\n`money_value` DECIMAL\r\n)\r\nRETURNS TEXT\r\nBEGIN\r\nRETURN CONCAT( N'Hello ', text_value );\r\nEND $$;\r\nDELIMITER ;", command.CommandText);
}