Add StoredProcedure

[piboistudios]

In SQL Server, SPs can manipulate data, whereas what other DBs call Procedures MSSQL calls Functions, and those are basically just parametrized queries referenced by name.

Stored Procedures can compose functions and queries and also return a resultset, the general use is much like a function, so, I think the only real difference would be the calling convention.
In SQL Server, it's

EXEC path.to.SP 
@param1=5,
@param2='foo'
-- or just
EXEC path.to.SP 5, 'foo'

I'm willing to put in the work to do this as I am planning to write an ODBC driver soon (for HashLink).

What's the best path to implement without duplicating a bunch of code? It's basically going to require all the same inputs and give the same output as Procedures (both at runtime and in macro).

With the added restriction (if it doesn't already exist) that you can't compose SP calls (I don't know if tink SQL allows procedure calls in subqueries but yeah, no go with SPs)

[benmerckx]

If you can fit it into a 'call':

tink_sql/src/tink/sql/Query.hx

Line 17 in 2602205

CallProcedure<Row:{}>(call:CallOperation<Row>):Query<Db, RealStream<Row>>;

tink_sql/src/tink/sql/Query.hx

Lines 49 to 53 in 2602205

	typedef CallOperation<Row:{}> = {
	name:String,
	arguments:Array<Expr<Dynamic>>,
	?limit:Limit,
	}

You can then format it accordingly in the sqlserver formatter:

tink_sql/src/tink/sql/format/MySqlFormatter.hx

Lines 147 to 156 in 2602205

	override function call<Row:{}>(op:CallOperation<Row>):Statement
	return sql('CALL')
	.add(op.name)
	.parenthesis(
	separated(
	op.arguments.map(function (arg)
	return expr(arg)
	)
	)
	);

[piboistudios]

The way I see it now, there will be a lot of confusing naming overlap that will get cumbersome.

I'm thinking of using new metadata (while still allowing old one @:procedure so as to not breaking stuff)

But new meta:
@:func - what is now @:procedure
@:proc - stored procedure

(or if you prefer, use something like @:function and @:storedProc so older projects won't look funny having @:proc and @:procedure where procedure is something different)

Then I can just add a field ?type:CallType; to CallOperation where CallType is just some dumb enum:

enum CallType {
  Proc;
  Func;
}

From there, in the formatter, I can decide whether it's a stored procedure or a function, etc.. and, I think since it's optional, it shouldn't break any existing code (though, I don't know if MySQL also has SPs, probably does, but if so, I guess in the future it may be worthwhile to implement type for other formatters).

EDIT: Also, I think the SQL parameter name part is optional, so I don't think there's a need for any added complexity there either.

[benmerckx]

I think @kevinresol is the best person to weigh in here. In any case I wouldn't worry about breaking some metadata, it's easy to update and I feel adding more drivers will result in breaking changes anyway.

[kevinresol]

Is it like Sub and Function in VB6? The former returns nothing while the former returns something?

[piboistudios]

No an SP can actually return multiple things whereas a function can only return one (hence SP isn't meant to be called within SQL by other SQL, but I wouldn't worry about that outside the restriction that it can't be used in sub queries. No one really uses SPs with multiple returns really.. it's clumsy and pointless but possible).

[kevinresol]

So yeah, CallProcedure and @:procedure is what you need.

[kevinresol]

Functions are dealt with Expr.ECall, just like any built-in SQL functions like IF(cond, t, f)