|
Syntax error in the stored procedure string('select * from table where ctname='variable' CREATE TABLE "GROUPO"."Departments" (
"DepartmentID" INTEGER NOT NULL,
"DepartmentName" CHAR(40) NOT NULL,
"DepartmentHeadID" INTEGER NULL,
CONSTRAINT "DepartmentsKey" PRIMARY KEY ( "DepartmentID" ASC )
) IN "system";
COMMENT ON TABLE "GROUPO"."Departments" IS 'contains the names and heads of the various departments in the sporting goods company';
CREATE PROCEDURE DynamicResult(
IN TableName CHAR(128),
IN Restriction LONG VARCHAR DEFAULT NULL )
BEGIN
DECLARE Command LONG VARCHAR;
SET Command = string('select ', '''TABLEJSON''','+','''TABLEJSON'''
,'+','(SELECT top 1 trans_no FROM ' || TableName+' for json raw) '
,'+ ','(SELECT '+'''ftString'''+' as type,cname as name,255 as size, '+'''true'''+' required FROM sys.SYSCOLUMNS where '
|| Restriction + ' for json raw)'
IF ISNULL( Restriction,'') <> '' THEN
SET Command = Command || ' WHERE ' || Restriction;
END IF;
EXECUTE IMMEDIATE WITH RESULT SET ON Command;
END;
result error: string('select ', '''TABLEJSON''','+','''TABLEJSON'''
,'+','(SELECT top 1 trans_no FROM ' || TableName+' for json raw) '
,'+ ','(SELECT '+'''ftString'''+' as type,cname as name,255 as size, '+'''true'''+' required FROM sys.SYSCOLUMNS where '
|| Restriction + ' for json raw)'
The effect achieved is the way the statement runs: select string((SELECT * FROM Departments FOR json raw ) ,'stesttxt',(select 'ftString' as type,cname as name,255 as size, 'true' required FROM sys.SYSCOLUMNS where ' tname='Departments' for json raw)) error at tname='Departments', It should be expressed as how to add strings to the running statement, How to make the single quotes in the running statement not be escaped to empty and keep the single quotes |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 05 Jun '21, 09:19
question was seen: 542 times
last updated: 07 Jun '21, 11:43
Consider coding SQL neatly, so ordinary people can read and understand what you have coded.
Consider using fewer techniques to do one thing; e.g., stick with STRING() for string concatenation, don't use STRING, + and || all in one statement... that's confusing.
Show us what you want BY EXAMPLE. E.g., show us THE EXACT SELECT statement you want SQL Anywhere to execute when you CALL DynamicResult ( 'xxx', '' );
( if you can't show us what you want, EXECUTE IMMEDIATE will never work )
Please explain "TABLEJSON, TABLEJSON". Are you trying to implement this stuff? https://www.mssqltips.com/sqlservertip/6462/compare-sql-server-databases--part-2/
Perhaps you can show an example of what the final statement should look like based on sample input values. Currently, I can see several issues with the why that the statement will be constructed. Much of this would be obvious if you simple select the value of COMMAND after you build it. I will also note that you need to handle the case where RESTRICTION is NULL. First obvious issue is that the set command = string... is missing a closing parathesis and semicolon. With those corrections the value of command will be like:
select 'TABLEJSON'+'TABLEJSON'+(SELECT top 1 trans_no FROM Departments for json raw) + (SELECT 'ftString' as type,cname as name,255 as size, 'true' required FROM sys.SYSCOLUMNS where for json raw)
This is clearly not valid syntax. More help can be offered if you were to provide the statement that you intend to build.