Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored procedure syntax

9 posts in General Discussion Last posting was on 2005-04-29 11:13:20.0Z
Rick Langel Posted on 2005-04-27 16:10:06.0Z
From: "Rick Langel" <langelrj@hotmail.com>
Newsgroups: ianywhere.public.general
Subject: Stored procedure syntax
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
X-Original-NNTP-Posting-Host: 170.54.59.167
Message-ID: <426fb95b$1@forums-2-dub>
X-Original-Trace: 27 Apr 2005 09:10:03 -0800, 170.54.59.167
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Apr 2005 09:10:05 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Apr 2005 09:10:06 -0700
X-Trace: forums-1-dub 1114618206 10.22.108.75 (27 Apr 2005 09:10:06 -0700)
X-Original-Trace: 27 Apr 2005 09:10:06 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4387
Article PK: 8202

I'm trying to use a stored procedure that was originally written for ASE on
my ASA 9.02 database, and am finding out that the syntax is different.
Within the stored procedure a variable is declared that will hold a
dynamically built SQL statement to be executed later:

DECLARE @SQLStatement varchar(16384)


After we build the sql, we try to call it with

EXEC (@SQLStatement)

But the parens are a bad thing, so I took them out, but then I get an error
at run time that Procedure '@SQLStatement' is not found. I've looked but
can't find any whitepapers or tech documents on ASA sp syntax, can anyone
point me to one or can you tell me what I need to change about my sp?
Thanks.

-Rick


Chris Keating (iAnywhere Solutions) Posted on 2005-04-27 17:49:19.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <Spam_NoThanks_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <426fb95b$1@forums-2-dub>
Subject: Re: Stored procedure syntax
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
X-Original-NNTP-Posting-Host: keating-xp2.sybase.com
Message-ID: <426fd095$1@forums-2-dub>
X-Original-Trace: 27 Apr 2005 10:49:09 -0800, keating-xp2.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Apr 2005 10:49:10 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Apr 2005 10:49:19 -0700
X-Trace: forums-1-dub 1114624159 10.22.108.75 (27 Apr 2005 10:49:19 -0700)
X-Original-Trace: 27 Apr 2005 10:49:19 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4389
Article PK: 8201

Use EXECUTE not EXEC

"Rick Langel" <langelrj@hotmail.com> wrote in message
news:426fb95b$1@forums-2-dub...
>
> I'm trying to use a stored procedure that was originally written for ASE
> on my ASA 9.02 database, and am finding out that the syntax is different.
> Within the stored procedure a variable is declared that will hold a
> dynamically built SQL statement to be executed later:
>
> DECLARE @SQLStatement varchar(16384)
>
>
> After we build the sql, we try to call it with
>
> EXEC (@SQLStatement)
>
> But the parens are a bad thing, so I took them out, but then I get an
> error at run time that Procedure '@SQLStatement' is not found. I've
> looked but can't find any whitepapers or tech documents on ASA sp syntax,
> can anyone point me to one or can you tell me what I need to change about
> my sp? Thanks.
>
> -Rick
>
>
>


Rick Langel Posted on 2005-04-27 18:56:02.0Z
From: "Rick Langel" <langelrj@hotmail.com>
Newsgroups: ianywhere.public.general
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub>
Subject: Re: Stored procedure syntax
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-Original-NNTP-Posting-Host: 170.54.59.167
Message-ID: <426fe03f$1@forums-2-dub>
X-Original-Trace: 27 Apr 2005 11:55:59 -0700, 170.54.59.167
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 27 Apr 2005 11:56:00 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 27 Apr 2005 11:56:02 -0700
X-Trace: forums-1-dub 1114628162 10.22.108.75 (27 Apr 2005 11:56:02 -0700)
X-Original-Trace: 27 Apr 2005 11:56:02 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4391
Article PK: 8204

Thanks, and it's still not working, which probably means there is another
syntax difference that I haven't found yet. Time to try out the debugger...



"Chris Keating (iAnywhere Solutions)" <Spam_NoThanks_keating@iAnywhere.com>

wrote in message news:426fd095$1@forums-2-dub...
> Use EXECUTE not EXEC
>
>
> "Rick Langel" <langelrj@hotmail.com> wrote in message
> news:426fb95b$1@forums-2-dub...
>>
>> I'm trying to use a stored procedure that was originally written for ASE
>> on my ASA 9.02 database, and am finding out that the syntax is different.
>> Within the stored procedure a variable is declared that will hold a
>> dynamically built SQL statement to be executed later:
>>
>> DECLARE @SQLStatement varchar(16384)
>>
>>
>> After we build the sql, we try to call it with
>>
>> EXEC (@SQLStatement)
>>
>> But the parens are a bad thing, so I took them out, but then I get an
>> error at run time that Procedure '@SQLStatement' is not found. I've
>> looked but can't find any whitepapers or tech documents on ASA sp syntax,
>> can anyone point me to one or can you tell me what I need to change about
>> my sp? Thanks.
>>
>> -Rick
>>
>>
>>
>
>


Breck Carter [TeamSybase] Posted on 2005-04-28 10:47:46.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure syntax
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <bpf1719l7fdhrpno2m738mmvm8vra6vdm4@4ax.com>
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub> <426fe03f$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 208.254.244.99
X-Original-Trace: 28 Apr 2005 03:47:41 -0700, 208.254.244.99
Lines: 54
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 28 Apr 2005 03:47:43 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 28 Apr 2005 03:47:46 -0700
X-Trace: forums-1-dub 1114685266 10.22.108.75 (28 Apr 2005 03:47:46 -0700)
X-Original-Trace: 28 Apr 2005 03:47:46 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4396
Article PK: 8209

Did you try EXECUTE ( with the parentheses )?

Breck


On 27 Apr 2005 11:56:02 -0700, "Rick Langel" <langelrj@hotmail.com>

wrote:

>
>Thanks, and it's still not working, which probably means there is another
>syntax difference that I haven't found yet. Time to try out the debugger...
>
>
>
>"Chris Keating (iAnywhere Solutions)" <Spam_NoThanks_keating@iAnywhere.com>
>wrote in message news:426fd095$1@forums-2-dub...
>> Use EXECUTE not EXEC
>>
>>
>> "Rick Langel" <langelrj@hotmail.com> wrote in message
>> news:426fb95b$1@forums-2-dub...
>>>
>>> I'm trying to use a stored procedure that was originally written for ASE
>>> on my ASA 9.02 database, and am finding out that the syntax is different.
>>> Within the stored procedure a variable is declared that will hold a
>>> dynamically built SQL statement to be executed later:
>>>
>>> DECLARE @SQLStatement varchar(16384)
>>>
>>>
>>> After we build the sql, we try to call it with
>>>
>>> EXEC (@SQLStatement)
>>>
>>> But the parens are a bad thing, so I took them out, but then I get an
>>> error at run time that Procedure '@SQLStatement' is not found. I've
>>> looked but can't find any whitepapers or tech documents on ASA sp syntax,
>>> can anyone point me to one or can you tell me what I need to change about
>>> my sp? Thanks.
>>>
>>> -Rick
>>>
>>>
>>>
>>
>>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Nick Elson Posted on 2005-04-28 12:20:31.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub> <426fe03f$1@forums-2-dub> <bpf1719l7fdhrpno2m738mmvm8vra6vdm4@4ax.com>
Subject: Re: Stored procedure syntax
Lines: 124
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <4270d50f$1@forums-1-dub>
Date: 28 Apr 2005 05:20:31 -0700
X-Trace: forums-1-dub 1114690831 10.25.98.216 (28 Apr 2005 05:20:31 -0700)
X-Original-Trace: 28 Apr 2005 05:20:31 -0700, nicelson-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4397
Article PK: 8207

I would suggest going right to EXECUTE IMMEDIATE if just to
verify there is nothing else going on.

Historically (and currently with SYNTAX 1)

EXEC[ute] @parm_out = <procname> [[@parm_i] ...]

was legal T/SQL as far as ASE was concerned but substituting in
a variable for <procname> was not.

To execute a string of characters as if it was SQL you need to submit
that en mass as either

execute( sql-string-expression ) [T/SQL]

or
execute immediate sql-string-expression; [W/SQL]

where sql-string-expression is one string and has a valid
SQL batch inside of it.

Note that in both cases the string expression should really be
a full SQL expression including any CALL or EXECUTE (T/SQL syntax 1);
The latter being optional in some restricted cases with T/SQL
[EXEC is option only for the first statement in a SQL batch].

AS SUCH, THIS WORKS:

create procedure sa_exec_test as
print 'Hello'
go

begin
declare @sqlstrexpr varchar(255)
select @sqlstrexpr='sa_exec_test'
execute( @sqlstrexpr )
end
go

SO TOO DOES:
. . .
select @sqlstrexpr='exec sa_exec_test'
. . .
or
. . .
select @sqlstrexpr='call sa_exec_test'
. . .

BUT THEN AGAIN SO DOES (the preferable?) W/SQL

begin
declare sqlstrexpr varchar(255);
set sqlstrexpr='call sa_exec_test';
execute immediate sqlstrexpr;
end;


Help any?

"Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:bpf1719l7fdhrpno2m738mmvm8vra6vdm4@4ax.com...
> Did you try EXECUTE ( with the parentheses )?
>
> Breck
>
>
> On 27 Apr 2005 11:56:02 -0700, "Rick Langel" <langelrj@hotmail.com>
> wrote:
>
>>
>>Thanks, and it's still not working, which probably means there is another
>>syntax difference that I haven't found yet. Time to try out the
>>debugger...
>>
>>
>>
>>"Chris Keating (iAnywhere Solutions)"
>><Spam_NoThanks_keating@iAnywhere.com>
>>wrote in message news:426fd095$1@forums-2-dub...
>>> Use EXECUTE not EXEC
>>>
>>>
>>> "Rick Langel" <langelrj@hotmail.com> wrote in message
>>> news:426fb95b$1@forums-2-dub...
>>>>
>>>> I'm trying to use a stored procedure that was originally written for
>>>> ASE
>>>> on my ASA 9.02 database, and am finding out that the syntax is
>>>> different.
>>>> Within the stored procedure a variable is declared that will hold a
>>>> dynamically built SQL statement to be executed later:
>>>>
>>>> DECLARE @SQLStatement varchar(16384)
>>>>
>>>>
>>>> After we build the sql, we try to call it with
>>>>
>>>> EXEC (@SQLStatement)
>>>>
>>>> But the parens are a bad thing, so I took them out, but then I get an
>>>> error at run time that Procedure '@SQLStatement' is not found. I've
>>>> looked but can't find any whitepapers or tech documents on ASA sp
>>>> syntax,
>>>> can anyone point me to one or can you tell me what I need to change
>>>> about
>>>> my sp? Thanks.
>>>>
>>>> -Rick
>>>>
>>>>
>>>>
>>>
>>>
>>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Rick Langel Posted on 2005-04-28 21:08:10.0Z
From: "Rick Langel" <langelrj@hotmail.com>
Newsgroups: ianywhere.public.general
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub> <426fe03f$1@forums-2-dub> <bpf1719l7fdhrpno2m738mmvm8vra6vdm4@4ax.com> <4270d50f$1@forums-1-dub>
Subject: Re: Stored procedure syntax
Lines: 134
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-Original-NNTP-Posting-Host: 170.54.59.167
Message-ID: <427150b6$1@forums-2-dub>
X-Original-Trace: 28 Apr 2005 14:08:06 -0700, 170.54.59.167
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 28 Apr 2005 14:08:07 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 28 Apr 2005 14:08:10 -0700
X-Trace: forums-1-dub 1114722490 10.22.108.75 (28 Apr 2005 14:08:10 -0700)
X-Original-Trace: 28 Apr 2005 14:08:10 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4408
Article PK: 8220

EXECUTE worked, but there was another syntax change about setting the value
of the variable. This sp is working now. Thanks for the help.

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:4270d50f$1@forums-1-dub...
>I would suggest going right to EXECUTE IMMEDIATE if just to
> verify there is nothing else going on.
>
> Historically (and currently with SYNTAX 1)
>
> EXEC[ute] @parm_out = <procname> [[@parm_i] ...]
>
> was legal T/SQL as far as ASE was concerned but substituting in
> a variable for <procname> was not.
>
> To execute a string of characters as if it was SQL you need to submit
> that en mass as either
>
> execute( sql-string-expression ) [T/SQL]
>
> or
> execute immediate sql-string-expression; [W/SQL]
>
> where sql-string-expression is one string and has a valid
> SQL batch inside of it.
>
> Note that in both cases the string expression should really be
> a full SQL expression including any CALL or EXECUTE (T/SQL syntax 1);
> The latter being optional in some restricted cases with T/SQL
> [EXEC is option only for the first statement in a SQL batch].
>
> AS SUCH, THIS WORKS:
>
> create procedure sa_exec_test as
> print 'Hello'
> go
>
> begin
> declare @sqlstrexpr varchar(255)
> select @sqlstrexpr='sa_exec_test'
> execute( @sqlstrexpr )
> end
> go
>
> SO TOO DOES:
> . . .
> select @sqlstrexpr='exec sa_exec_test'
> . . .
> or
> . . .
> select @sqlstrexpr='call sa_exec_test'
> . . .
>
> BUT THEN AGAIN SO DOES (the preferable?) W/SQL
>
> begin
> declare sqlstrexpr varchar(255);
> set sqlstrexpr='call sa_exec_test';
> execute immediate sqlstrexpr;
> end;
>
>
> Help any?
>
> "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com> wrote in
> message news:bpf1719l7fdhrpno2m738mmvm8vra6vdm4@4ax.com...
>> Did you try EXECUTE ( with the parentheses )?
>>
>> Breck
>>
>>
>> On 27 Apr 2005 11:56:02 -0700, "Rick Langel" <langelrj@hotmail.com>
>> wrote:
>>
>>>
>>>Thanks, and it's still not working, which probably means there is another
>>>syntax difference that I haven't found yet. Time to try out the
>>>debugger...
>>>
>>>
>>>
>>>"Chris Keating (iAnywhere Solutions)"
>>><Spam_NoThanks_keating@iAnywhere.com>
>>>wrote in message news:426fd095$1@forums-2-dub...
>>>> Use EXECUTE not EXEC
>>>>
>>>>
>>>> "Rick Langel" <langelrj@hotmail.com> wrote in message
>>>> news:426fb95b$1@forums-2-dub...
>>>>>
>>>>> I'm trying to use a stored procedure that was originally written for
>>>>> ASE
>>>>> on my ASA 9.02 database, and am finding out that the syntax is
>>>>> different.
>>>>> Within the stored procedure a variable is declared that will hold a
>>>>> dynamically built SQL statement to be executed later:
>>>>>
>>>>> DECLARE @SQLStatement varchar(16384)
>>>>>
>>>>>
>>>>> After we build the sql, we try to call it with
>>>>>
>>>>> EXEC (@SQLStatement)
>>>>>
>>>>> But the parens are a bad thing, so I took them out, but then I get an
>>>>> error at run time that Procedure '@SQLStatement' is not found. I've
>>>>> looked but can't find any whitepapers or tech documents on ASA sp
>>>>> syntax,
>>>>> can anyone point me to one or can you tell me what I need to change
>>>>> about
>>>>> my sp? Thanks.
>>>>>
>>>>> -Rick
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>> --
>> SQL Anywhere Studio 9 Developer's Guide
>> Buy the book:
>> http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
>> bcarter@risingroad.com
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>
>


Breck Carter [TeamSybase] Posted on 2005-04-29 11:11:49.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure syntax
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <hh547159o0t50sd2ik0m2aljf79er5tn66@4ax.com>
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub> <426fe03f$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 208.254.244.99
X-Original-NNTP-Posting-Host: 208.254.244.99
Date: 29 Apr 2005 04:11:49 -0700
X-Trace: forums-1-dub 1114773109 208.254.244.99 (29 Apr 2005 04:11:49 -0700)
X-Original-Trace: 29 Apr 2005 04:11:49 -0700, 208.254.244.99
Lines: 54
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4411
Article PK: 8221

Did you try EXECUTE ( with parentheses )? That's the syntax documented
in the Help; see "EXECUTE IMMEDIATE" Syntax 2.

Breck

On 27 Apr 2005 11:56:02 -0700, "Rick Langel" <langelrj@hotmail.com>

wrote:

>
>Thanks, and it's still not working, which probably means there is another
>syntax difference that I haven't found yet. Time to try out the debugger...
>
>
>
>"Chris Keating (iAnywhere Solutions)" <Spam_NoThanks_keating@iAnywhere.com>
>wrote in message news:426fd095$1@forums-2-dub...
>> Use EXECUTE not EXEC
>>
>>
>> "Rick Langel" <langelrj@hotmail.com> wrote in message
>> news:426fb95b$1@forums-2-dub...
>>>
>>> I'm trying to use a stored procedure that was originally written for ASE
>>> on my ASA 9.02 database, and am finding out that the syntax is different.
>>> Within the stored procedure a variable is declared that will hold a
>>> dynamically built SQL statement to be executed later:
>>>
>>> DECLARE @SQLStatement varchar(16384)
>>>
>>>
>>> After we build the sql, we try to call it with
>>>
>>> EXEC (@SQLStatement)
>>>
>>> But the parens are a bad thing, so I took them out, but then I get an
>>> error at run time that Procedure '@SQLStatement' is not found. I've
>>> looked but can't find any whitepapers or tech documents on ASA sp syntax,
>>> can anyone point me to one or can you tell me what I need to change about
>>> my sp? Thanks.
>>>
>>> -Rick
>>>
>>>
>>>
>>
>>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Breck Carter [TeamSybase] Posted on 2005-04-29 11:13:20.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure syntax
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <bl5471tuuhdlinpmli1dhme4ijal5tduuu@4ax.com>
References: <426fb95b$1@forums-2-dub> <426fd095$1@forums-2-dub> <426fe03f$1@forums-2-dub> <hh547159o0t50sd2ik0m2aljf79er5tn66@4ax.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 208.254.244.99
X-Original-NNTP-Posting-Host: 208.254.244.99
Date: 29 Apr 2005 04:13:20 -0700
X-Trace: forums-1-dub 1114773200 208.254.244.99 (29 Apr 2005 04:13:20 -0700)
X-Original-Trace: 29 Apr 2005 04:13:20 -0700, 208.254.244.99
Lines: 61
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4412
Article PK: 8226

Oops, I should press "get new messages" before "reply".

Breck Senior Moment

On 29 Apr 2005 04:11:49 -0700, "Breck Carter [TeamSybase]"

<NOSPAM__bcarter@risingroad.com> wrote:

>Did you try EXECUTE ( with parentheses )? That's the syntax documented
>in the Help; see "EXECUTE IMMEDIATE" Syntax 2.
>
>Breck
>
>On 27 Apr 2005 11:56:02 -0700, "Rick Langel" <langelrj@hotmail.com>
>wrote:
>
>>
>>Thanks, and it's still not working, which probably means there is another
>>syntax difference that I haven't found yet. Time to try out the debugger...
>>
>>
>>
>>"Chris Keating (iAnywhere Solutions)" <Spam_NoThanks_keating@iAnywhere.com>
>>wrote in message news:426fd095$1@forums-2-dub...
>>> Use EXECUTE not EXEC
>>>
>>>
>>> "Rick Langel" <langelrj@hotmail.com> wrote in message
>>> news:426fb95b$1@forums-2-dub...
>>>>
>>>> I'm trying to use a stored procedure that was originally written for ASE
>>>> on my ASA 9.02 database, and am finding out that the syntax is different.
>>>> Within the stored procedure a variable is declared that will hold a
>>>> dynamically built SQL statement to be executed later:
>>>>
>>>> DECLARE @SQLStatement varchar(16384)
>>>>
>>>>
>>>> After we build the sql, we try to call it with
>>>>
>>>> EXEC (@SQLStatement)
>>>>
>>>> But the parens are a bad thing, so I took them out, but then I get an
>>>> error at run time that Procedure '@SQLStatement' is not found. I've
>>>> looked but can't find any whitepapers or tech documents on ASA sp syntax,
>>>> can anyone point me to one or can you tell me what I need to change about
>>>> my sp? Thanks.
>>>>
>>>> -Rick
>>>>
>>>>
>>>>
>>>
>>>
>>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Joshua Savill Posted on 2005-04-27 16:49:58.0Z
From: "Joshua Savill" <jsavill@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <426fb95b$1@forums-2-dub>
Subject: Re: Stored procedure syntax
Lines: 88
Organization: iAnywhere Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: jsavill-pc.sybase.com
X-Original-NNTP-Posting-Host: jsavill-pc.sybase.com
Message-ID: <426fc2b6$1@forums-1-dub>
Date: 27 Apr 2005 09:49:58 -0700
X-Trace: forums-1-dub 1114620598 10.25.98.205 (27 Apr 2005 09:49:58 -0700)
X-Original-Trace: 27 Apr 2005 09:49:58 -0700, jsavill-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4388
Article PK: 8203

Rick,

Here are some areas of ASA documentation that you should review:

ASA SQL Reference
SQL Statements
CREATE PROCEDURE statement

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000367.htm

ASA SQL Reference
SQL Statements
CREATE PROCEDURE statement [T-SQL]

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000368.htm

ASA SQL Reference
SQL Statements
CALL statement

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000346.htm

ASA SQL Reference
SQL Statements
EXECUTE statement [T-SQL]

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000412.htm

ASA SQL User's Guide
Using Procedures, Triggers, and Batches

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000664.htm

ASA SQL User's Guide
Importing and Exporting Data
Migrating databases to Adaptive Server Anywhere

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000588.htm

ASA SQL User's Guide
Importing and Exporting Data
Migrating databases to Adaptive Server Anywhere
Using the sa_migrate stored procedures

Online:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000590.htm

The section that will help the most is the EXECUTE statement [T-SQL]. Reply
to the newsgroup if you have any more questions or are still having
problems.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Rick Langel" <langelrj@hotmail.com> wrote in message
news:426fb95b$1@forums-2-dub...
>
> I'm trying to use a stored procedure that was originally written for ASE
> on my ASA 9.02 database, and am finding out that the syntax is different.
> Within the stored procedure a variable is declared that will hold a
> dynamically built SQL statement to be executed later:
>
> DECLARE @SQLStatement varchar(16384)
>
>
> After we build the sql, we try to call it with
>
> EXEC (@SQLStatement)
>
> But the parens are a bad thing, so I took them out, but then I get an
> error at run time that Procedure '@SQLStatement' is not found. I've
> looked but can't find any whitepapers or tech documents on ASA sp syntax,
> can anyone point me to one or can you tell me what I need to change about
> my sp? Thanks.
>
> -Rick
>
>
>