Execute MSSQL Stored Procedure with Parameters

[kevinr432]

II wanted to call an MSSQL stored procedure from sqlpage. To test, I created a very simple stored procedure with one input parameter and a simple out. It works when called from SQL Enterprise manager. But I cannot figure out how to call it from sql page. Does anyone have any ideas?

Here is how I call it from SQL Enterprise Manager
execute SampleStoredProcedure 'This is a test!'

Here is how I tried to call it from sql page:
Execute SampleStoredProcedure $InputMessage;

See attachment.

Thank you,
Kevin Reagan

[lovasoa]

Hello, and thank you for reporting this !

The SQL parser inside SQLPage currently does not support stored procedure calls with parameters in sql server. I will report this upstream and we will try to have this implemented.

In the mean time, calling stored procedures without parameters, and user-defined functions works, across all supported databases.

In your example case, you can create a user-defined function:

-- Create the function
CREATE FUNCTION SampleUserDefinedFunction
(
    @input VARCHAR(100)
)
RETURNS VARCHAR(200)
AS
BEGIN
    RETURN 'Your input was: ' + @input
END;

try online

and then use it in a select statement

select dbo.SampleUserDefinedFunction($InputMessage);

try online

[lovasoa]

Reported to the datafusion sql parser project as apache/datafusion-sqlparser-rs#1489

[lovasoa]

I proposed a fix to the our sql parser; now waiting to get it approved: apache/datafusion-sqlparser-rs#1490

[kevinr432]

The function works perfectly. I don't know why I didn't think to try that. Thank you so much!

[kevinr432]

Ultimately, I wanted to update tables in the stored procedure. It seems that functions cannot update tables, so I think I will have to wait until the ability to call stored procedures with parameters is available.

[lovasoa]

What you can do already is use a table to pass parameters to your function. This can also serve as an event log.

insert into events values ('param 1', 'param2');
execute process_events;

[kevinr432]

That is a very good idea. I think I can work with that until the execute storedprocedure with parameters becomes available. Ultimately I want the put all of my complex editing and validation logic in the stored procedure and return “Success” or “Failed for XXXX reasons”