validating SQL syntax
Name
Rob Verschoor
news:4035aa00$1@forums-2-dub...
> Is there a stored procedure that can validate the syntax of a SQL
statement?
Not supplied by Sybase. But as I described in my book "Tips, Tricks &
Recipes for Sybase ASE" (www.sypron.nl/ttr) you can use
execute-immediate to perform such a check. Create a procedure like
this:
create proc p
@cmd varchar(100)
as
exec(@cmd)
if @@error != 0
print "Syntax invalid"
else
print "Syntax valid"
go
This will work for most T-SQL statements. Some statements, like exec()
itself, dbcc commands, and a few others, cannot be executed inside
exec() so it would seem to be invalid syntax even though it's actually
correct (this is because exec() wasn't intended to be used this way).
You can optionally suppress the 'error message output with 'set
background' as described in my book (see page 19). However, I strongly
recommend not doing this unless you're aware of the sharp edges of
this command (described in detail in my book).
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5
Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
Name
wouldn't be executed? Would the procedure still work this way?
On 19 Feb 2004 23:01:35 -0800,
in sybase.public.ase.general
Rob Verschoor
invalidating the entire concept. Please try this yourself!
FYI, I've tried whether 'set parseonly on/off' would work, but this
does not seem to have an effect inside a stored proc.
HTH,
Rob V.
"Name" <na...@somewhere.com> wrote in message
news:403615c0$1@forums-2-dub...
ziona...@gmail.com
Thanks
ziona...@gmail.com
See my try :
use ems
go
create table dbo.toto(
z int null
)
go
create procedure titi
as
begin
declare @sql varchar(200)
select @sql="select getdate()"
exec (@sql)
end
go
create trigger tu_toto on toto for update
as
begin
/* Assume the loopback server was added as well (see mda installation requirements) */
exec loopback.ems..titi
end
go
declare @sql varchar(200)
select @sql="Update toto set z = 1"
exec (@sql)
go
ziona...@gmail.com
Blaar
> On Friday, February 20, 2004 5:45:20 PM UTC+2, Rob Verschoor wrote:
> > No - this would have the effect of not executing the exec() statement,
> > invalidating the entire concept. Please try this yourself!
> > FYI, I've tried whether 'set parseonly on/off' would work, but this
> > does not seem to have an effect inside a stored proc.
>
> > HTH,
>
> > Rob V.
>
> > > Could I put a 'set noexec on' statement before the call to 'exec' so
> > the query
> > > wouldn't be executed? Would the procedure still work this way?
>
> > > On 19 Feb 2004 23:01:35 -0800,
> > > in sybase.public.ase.general
> > > Rob Verschoor <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> > > >mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> > > >http://www.sypron.nl
> > > >Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > > >-------------------------------------------------------------
>
> Does this limitation exists also in Sybase 15 ?
>
> Thanks
The function is mentioned here:
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1502/html/nfg1502/CACCAEBF.htm