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

asked 05 Jun, 09:19

mfkpie8's gravatar image

mfkpie8
228556266
accept rate: 14%

edited 05 Jun, 23:20

2

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/

(06 Jun, 07:39) Breck Carter
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.

(07 Jun, 11:43) Chris Keating
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×185

question asked: 05 Jun, 09:19

question was seen: 70 times

last updated: 07 Jun, 11:43