Hi, CJ -
I don't have any experience using Adaptive Server Anywhere, but I will show you here what I've verified works with a SQL Server database.
In SQL Server Management Studio, here is an example of a script that drops, creates, grants execution rights and tests a stored procedure::
/* Destroy any existing version of the stored procedure... */
IF OBJECT_ID('sp_john') IS NOT NULL
DROP PROCEDURE dbo.sp_john
go
/* Create the stored procedure... */
CREATE PROCEDURE dbo.sp_john
(@one integer,
@two char(1),
@three varchar(20)
)
AS
BEGIN
/* The body of the stored procedure goes here...
What follows is only an example. */
DECLARE @result varchar(50)
SELECT @result = CAST(@one AS varchar(12)) + ', ' + @two + ', ' + @three
RETURN (1)
END
go
/* Grant execution rights on the stored procedure... */
GRANT EXECUTE ON dbo.sp_john TO public
go
/* Test execution of the stored procedure... */
EXEC dbo.sp_john @one = 314159, @two = 'X', @three = '(unknown)'
go
Note the "go" statements at the end of each batch.
In ISQL pane of the PB Database Painter, you need to replace the "go" statements with a semi-colon:
/* Destroy any existing version of the stored procedure... */
IF OBJECT_ID('sp_john') IS NOT NULL
DROP PROCEDURE dbo.sp_john
;
/* Create the stored procedure... */
CREATE PROCEDURE dbo.sp_john
(@one integer,
@two char(1),
@three varchar(20)
)
AS
BEGIN
/* The body of the stored procedure goes here...
What follows is only an example. */
DECLARE @result varchar(50)
SELECT @result = CAST(@one AS varchar(12)) + ', ' + @two + ', ' + @three
RETURN (1)
END
;
/* Grant execution rights on the stored procedure... */
GRANT EXECUTE ON dbo.sp_john TO public
;
/* Test execution of the stored procedure... */
EXEC dbo.sp_john @one = 314159, @two = 'X', @three = '(unknown)'
;
I hope this helps you determine a solution.
Best regards and wishes for a Happy New Year, John
We connect to Adaptive Server Anywhere db via ODBC setting in PB's DB painter. I am not aware of any vendor's editor although I came across this RazorSQL online which is a commercial licensed software.
I tried to copy/paste one SP into the ISQL Session window in the painter and compiled it. I am not sure if that's the way to do it; that's why I am asking.
CJ
Setup for sqlanywhere: https://learningintheopen.org/2019/07/24/dbeaver-sql-anywhere-database-connection-setup/
Or checkout "Flyspeed SQL", which has become pretty good over the last years and used to be free. If there's no longer a free version, it's still very cheap (like $40): https://www.activedbsoft.com/download-querytool.html