difference in exec vs call storeproc?

[Former Member]

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

[VolkerBarth]

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)
--vs.
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".

[Former Member]

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,,,,

[Former Member]

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 .........

[Breck_Carter]

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