SQL Server stored procedure parameter conversion

[INTARYNX]

I am trying to execute a SQL Server stored procedure with a parameter:

SELECT 'table' AS component;
EXEC Test.dbo.TestStoredProcedure $param;

The variable $param contains the right value, but when SQLPage tries to execute the stored procedure, it tries to cast the parameter. However, SQL Server (2017) doesn't allow expressions in the EXECUTE statement, and SQLPage returns the following error:

In "test.sql": The following error occurred while executing an SQL statement:

Error returned from database: Incorrect syntax near '@p1'.
The SQL statement sent by SQLPage was:

EXECUTE Test.dbo.TestStoredProcedure CAST(@p1 AS VARCHAR(MAX));

The error is different from what was reported here: #655

Is there a way to put the $param value in a SQL Server variable before the execution of the stored procedure?

I've tried the following, but it doesn't work:

SELECT 'table' AS component;
SET @p VARCHAR(MAX) = $param;
EXEC Test.dbo.TestStoredProcedure @p;

[JoeJordan1]

This is the main blocker I have to using SQL Page fully - there is so much I want to be able to do using stored procedures, but casting the parameters prevents this. If this could be worked on next that would be amazing 🙏

[lovasoa]

Hi @INTARYNX & @JoeJordan1, great question!

This feature would definitely be useful, and we agree it’s something worth having in SQLPage. However, it does require a fair amount of development effort to implement correctly while not breaking compatibility with existing sqlpage applications using other databases.

If you'd be interested in sponsoring this feature to help make it happen, feel free to contact me directly at [email protected].

Thanks again for your feedback!