Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    24

    Unanswered: How Dynamic sql work?

    OK, I know how to use a var for the stored procedure as:
    1>declare @aa = "sp_who"
    2> execute @aa
    3> go

    but how do I use a var for a sql statement?

    1> declare @aa varchar(255)
    2> select @aa="from sysobjects"
    3> select * '@aa'
    4>go

    or
    1> declare @aa varchar(255)
    2> select @aa="sysobjects"
    3> select * from @aa
    4> go

    ?? Any trick? or where can I find this kind of information on any web?

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You would do something like:

    declare @c varchar(25)
    select @c = "select * from " + @aa
    exec (@c)
    Thanks,

    Matt

  3. #3
    Join Date
    Sep 2002
    Posts
    24

    Angry

    Originally posted by MattR
    You would do something like:

    declare @c varchar(25)
    select @c = "select * from " + @aa
    exec (@c)

    NO go, pal! you can only use exec for a stored procedure!
    I got 'select * from sysobjects' is not a valid identifier! in SQL Advantage. Any try?

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    hailieu

    The problem is that you are trying to use "dynamic SQL" from an inappropriate tool (eg. isql or SQL Advantage), these tools are simple clients and not scripting languages. Any scripting tool (eg. perl or /bin/sh) handles your example quite simply.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    Sep 2002
    Posts
    24

    Post

    OK, I forgot to said that I am only use ASE v11.9.2.

    So...

  6. #6
    Join Date
    Sep 2002
    Posts
    24
    I find this on isug.com website and tried it and it doesn't work!

    sp_configure "enable cis",1
    go

    create procedure test @aa int output, @bb int output
    as
    begin
    select @aa = 3
    select @bb = 9
    select name,id, uid from sysobjects
    end
    go

    create table #my_temp_table (
    name varchar(30),
    id int,
    uid smallint,
    aa int null,
    bb int null) external procedure
    at "local.dbname.dbo.test"
    go


    I got a 'CREATE (NEW) TABLE' is not legal when the remote
    object (local.dbname.dbo.test) has been as an RPC.
    (Using ASE v11.9.2)

    Any solution?

  7. #7
    Join Date
    May 2002
    Posts
    12
    Dynamic sql/execf(@sql) is not supported in 11.9.2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •