I am trying to execute a dynamic query as follows
begin
declare @tablename char(46)
declare @query char(1000)
declare @Age int
select @tablename='People'
select @query='select @Age = age from '+@tablename +' where name=xyz'
exec (@query)
end
This is giving error :
QLException :: (Must declare variable '@Securityid'., sqlcode=137,
sqlstate=ZZZZZ)
Am i doing something wrong or such thing is not possible.
Thanks

deepak.rathore@gmail.com wrote:
I am trying to execute a dynamic query as follows

begin
declare @tablename char(46)
declare @query char(1000)
declare @Age int

select @tablename='People'

select @query='select @Age = age from '+@tablename +' where name=xyz'

exec (@query)

end

This is giving error :
QLException :: (Must declare variable '@Securityid'., sqlcode=137,
sqlstate=ZZZZZ)

Am i doing something wrong or such thing is not possible.

Thanks

The dynamic query acts as a mini-stored procedure. Your @Age variable
is not local to that procedure.

The code below works but probably does not give you what you wish. "The
value of the query in the variable @Age"

You may wish to concider a shared temp table.

declare @tablename char(46)
declare @query char(1000)
declare @LNAME varchar(40)

select @tablename='authors'

select @query='declare @LNAME varchar(46)
select @LNAME = au_lname from '+@tablename +' where au_lname =
"Yokomoto"
select @LNAME'

exec (@query)

<wkraatz@csc.comwrote in message
news:1132331179.811469.269660@

deepak.rathore@gmail.com wrote:
I am trying to execute a dynamic query as follows

begin
declare @tablename char(46)
declare @query char(1000)
declare @Age int

select @tablename='People'

select @query='select @Age = age from '+@tablename +' where name=xyz'

exec (@query)

end

This is giving error :
QLException :: (Must declare variable '@Securityid'., sqlcode=137,
sqlstate=ZZZZZ)

Am i doing something wrong or such thing is not possible.

Thanks

The dynamic query acts as a mini-stored procedure. Your @Age variable
is not local to that procedure.

Note that as of 15.0 (as well as the upcoming 12.5.4), this has changed: as
of these versions, you *can* access local variables declared outisde the
exec-immediate from inside the exec-immediate.

HTH,

Rob

Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YUR.SPAM.sypron.nl.NT.FR.ME
http://www.sypron.nl
Sypron B.V., PBox 10695, 2501HR Den Haag, The Netherlands

"Rob Verschoor" <rob@wrote in
message news:b2d7c$4382d09b$54754033$11025@news.chello.nl
<wkraatz@csc.comwrote in message
news:1132331179.811469.269660@
>>
>deepak.rathore@gmail.com wrote:
>I am trying to execute a dynamic query as follows
>>
>begin
>declare @tablename char(46)
>declare @query char(1000)
>declare @Age int
>>
>select @tablename='People'
>>
>select @query='select @Age = age from '+@tablename +' where name=xyz'
>>
>exec (@query)
>>
>end
>>
>This is giving error :
>QLException :: (Must declare variable '@Securityid'., sqlcode=137,
>sqlstate=ZZZZZ)
>>
>Am i doing something wrong or such thing is not possible.
>>
>Thanks
>>
>The dynamic query acts as a mini-stored procedure. Your @Age variable
>is not local to that procedure.
>>
>
Note that as of 15.0 (as well as the upcoming 12.5.4), this has changed:
as
of these versions, you *can* access local variables declared outisde the
exec-immediate from inside the exec-immediate.

Very interesting. But I didn't see any references to this at TechWave nor
in an ASE 15 PPT from Jeff Tallman. It is not in the "New Features"
documentation nor "Reference Manual: Commands".

HTH,

Rob

Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YUR.SPAM.sypron.nl.NT.FR.ME
http://www.sypron.nl
Sypron B.V., PBox 10695, 2501HR Den Haag, The Netherlands

--

"Carl Kayser" <kayser_c@bls.govwrote in message
news:dlv006$jg6$1@blsnews.bls.gov
--
Note that as of 15.0 (as well as the upcoming 12.5.4), this has changed:
as
of these versions, you *can* access local variables declared outisde the
exec-immediate from inside the exec-immediate.

Very interesting. But I didn't see any references to this at TechWave nor
in an ASE 15 PPT from Jeff Tallman. It is not in the "New Features"
documentation nor "Reference Manual: Commands".

See my Techwave 2005 presentation at

HTH,

Rob

Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YUR.SPAM.sypron.nl.NT.FR.ME
http://www.sypron.nl
Sypron B.V., PBox 10695, 2501HR Den Haag, The Netherlands

Thanks everyone
If its true, this is quit useful enhancement.
3 cheers 4 developers !!!!!!!
Rob, I hope 15.0 is released

Is sybase version 15.0 same as 12.5.3
Alleaset , I tried this feature in 12.5.3 (released on Jul 8 2005),
and it does not work
@@version
Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux
Intel/Enterprise Jul 8 02:32:50 2005
Any pointers

<deepak.rathore@gmail.comwrote in message news:1132913038.462621.176730@
Is sybase version 15.0 same as 12.5.3

Alleaset , I tried this feature in 12.5.3 (released on Jul 8 2005),
and it does not work

@@version
Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linux
Intel/Enterprise Jul 8 02:32:50 2005

Any pointers

As I mentioned earlier: the enhancement to access local variables declared outside exec() from inside the exec() is part of ASE 15.0 and ASE 12.5.4 (so not in 12.5.3). 12.5.4 is planned for 2006.

HTH,

Rob

Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YUR.SPAM.sypron.nl.NT.FR.ME
http://www.sypron.nl
Sypron B.V., PBox 10695, 2501HR Den Haag, The Netherlands