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.

I found in sa 11 isql, it's a lot easier to use exec dba.procname parmlist then the call ( parmlist) just curious why should on use call instead of exec besides the possibliility of use some expression in call parmlist. of course in sub query in call unless sa 16

asked 22 May '14, 20:40

gg99's gravatar image

accept rate: 10%

I found in sa 11 isql, it's a lot easier to use exec dba.procname parmlist then the call ( parmlist)

What exactly makes the EXEC statement a lot easier to use then a CALL statement (except that you don't need the brackets...)?

Note, CALL is a standard SQL feature and is used within the Watcom-SQL dialect whereas EXECUTE is T-SQL style. I strongly prefer the CALL variant, possibly because it's parameter list just looks like that of most classic programming languages (and that is true for most parts of the Watcom-SQL dialect...).

IMHO, the sytax to get a return value looks more convenient with CALL:

retcode = CALL MyProc(myParam1, myParam2)
EXEC retcode = MyProc myParam1, myParam2

But apparently, that's more or less a personal choice. Unless you have to be portable to T-SQL, I'd recommend the Watcom-SQL dialect as SQL Anywhere's "native dialect".

permanent link

answered 23 May '14, 03:24

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

thx. I originally preferred the call statement but I found there situation that exec i helps me to debug the called stored procedure better - I did not understand the message from call.

for example

exec dba.pf_daily_holdg_detl_variance_deflt_Dt_Dif @valuation_dt, @acct_grp_id, @subAcct_ID, @fi_securitySymOrNameOrShortDescCriteria, @valuation_dt_dif, @msg
    select @acct_grp_id, @subAcct_ID,@subAcct_ID, @valuation_dt, @valuation_dt_dif, @fi_securitySymOrNameOrShortDescCriteria, @msg

can be exec w/o error while

 call dba.pf_daily_holdg_detl_variance_deflt_Dt_Dif (@valuation_dt, @acct_grp_id, @subAcct_ID, @fi_securitySymOrNameOrShortDescCriteria, @valuation_dt_dif, @msg
    select @acct_grp_id, @subAcct_ID,@subAcct_ID, @valuation_dt, @valuation_dt_dif, @fi_securitySymOrNameOrShortDescCriteria, @msg)

will lead to

Could not execute statement syntax error near call on line 16 sqlcode=-131, odbc 3 state='42000' line 1 column 1

when I forgot to end the statement with ; most of the time except for call statement omitting the ; will lead to a quick easy under error of syntax error near,,,,

(23 May '14, 11:22) gg99
Replies hidden

Nick, surely a better message of missing ; delimiter for the call statement would be a lot more desireable. I know watcom sql dialect has a lot more features than t_sql like if then else instead of if.... begin ... end else .........

(23 May '14, 18:18) gg99

Are you expecting the call and select to be two statements? You have put the ( parentheses ) around both statements.

(20 Aug '14, 18:44) Breck Carter

The missing ';' aspect of this has more to do with the SQL Dialect differences than it does have with this being a stored procedure call (uh 'exec').

By using

   EXEC <sproc_name>  . . . .

the SQL parsers involved sees the 'EXEC' token and identifies the SQL dialect as being Transact-SQL. From that point on the parser must determine the "end of statement" boundaries based solely by the T/SQL dialect-specific syntax of the statement types involved.

Transact-SQL is different in many aspects from Standard SQL (and SQL Anywhere specifically) but for this question it does not require the use of semi-colons. And the is the sole source of the perceived *'easy of use'* you mention.

So, in short, it is not a case of one being better than the other. The two uses belong to different SQL dialects and those just have different syntactical requirements.

The use of ';' as a statement delimiter is normal with SQL Anywhere and an easy convention to follow once adopted. You can avoid it if you restrict yourself to just T/SQL valid statements, and that only provides a sub-set of functionality.

Beyond this simple explanation this topic does overlap the concept of SQL Batches which I will not go into here but you should probably visit the topic to flesh out the concepts there for yourself.


permanent link

answered 23 May '14, 12:03

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 32%

Comment Text Removed

use of ; is not much of problem for using sa but then we will be locked in sa by making migration more difficult. with the sap, we don't seem to valued as we don't buy anything directly ourselves from sap although we help our client to buy licenses.

our sybase developer accounts have not been migrated to sap and seem to be stuck in a limbo.

(20 Aug '14, 16:09) gg99
Replies hidden

Your experienced problems with the Sybase/SAP migration (which certainly are not uncommon, sadly) seem not to be related to that question so I would recommend to ask that as a different question (or perhaps better: to call your sales rep...).

(21 Aug '14, 03:37) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 22 May '14, 20:40

question was seen: 12,575 times

last updated: 21 Aug '14, 03:37