MSSQL; Woulkd it be possible to execute stored procedures?

[rhodelta66]

When using stored procedures, we might be able to mimic the PostgresSQL output and thus achieve more with MSSQL while the SQLPage framework would not (yet) support certain functionalities.

like so:

CREATE PROCEDURE Delete_todo
@IDToDelete INT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeletedIDs TABLE (ID INT);

DELETE FROM YourTableName
OUTPUT DELETED.ID INTO @DeletedIDs
WHERE ID = @IDToDelete;

IF EXISTS (SELECT 1 FROM @DeletedIDs)
    SELECT ID FROM @DeletedIDs;
ELSE
    SELECT NULL AS ID;

END

[lovasoa]

Do you get an error when trying to execute custom procedures today?

[rhodelta66]

With this sql file:

Delete_todo $todo_id ;

 -- I also  tried: exec Delete_todo $todo_id ; 
-- and:  exec Delete_todo  @IDToDelete=$todo_id ; 

SELECT 'redirect' AS component, 
           './index.sql' AS link;	

I get this error:

The SQLPage parser couldn't understand the SQL file. Parsing failed. Please check for syntax errors:
--DELETE FROM todos WHERE id = CAST($todo_id AS INT) --AND $confirm = 'yes';
Delete_todo $todo_id ;
⬆️

Backtrace
sql parser error: Expected: an SQL statement, found: Delete_todo at Line: 6, Column: 1
SQLPage could not parse and prepare this SQL statement
Error We are sorry, but an error occurred while generating this page. You should contact the site's administrator.

Error in query number 1:

The SQLPage parser couldn't understand the SQL file. Parsing failed. Please check for syntax errors:
--DELETE FROM todos WHERE id = CAST($todo_id AS INT) --AND $confirm = 'yes';
Delete_todo $todo_id ;
⬆️

Backtrace
sql parser error: Expected: an SQL statement, found: $todo_id at Line: 6, Column: 13
SQLPage could not parse and prepare this SQL statement

[lovasoa]

Delete_todo $todo_id ;

This is not the syntax to execute a stored procedure in mssql.

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-ver16

[rhodelta66]

That's why I also wrote this:

"""
-- I also tried: exec Delete_todo $todo_id ;
-- and: exec Delete_todo @IDToDelete=$todo_id ;
"""

[lovasoa]

Did you try with the latest sqlpage version ?
The mssql stored procedure syntax parsing code was updated, see #655

[rhodelta66]

Great! Thanks for your work Op wo 27 nov 2024 23:39 schreef Ophir LOJKINE ***@***.***>:

…

Did you try with the latest sqlpage version ? The mssql stored procedure syntax parsing code was updated, see #655 <#655> — Reply to this email directly, view it on GitHub <#527 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHS2S7FSKHU3GLLGEUMALWD2CZC3DAVCNFSM6AAAAABMK2BOPCVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMZZHE4DKMI> . You are receiving this because you authored the thread.Message ID: ***@***.***>