Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2007
    Posts
    59

    Unanswered: How to create any SQL and execute it in a procedure?

    Hi friends

    I am writting 1 procedure to disable all the triggers in a database.
    I can get the name of table and trigger name related to it.
    But i am not getting how to create that sql and execute it in procedure.
    i have set the dboption 'ddl in tran' to true

    Actually i can generate the sql using select statement and variable
    bu i am not able to execute it in the procedure.

    pl help

    waiting for the reply

    Regards

  2. #2
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    try:
    Code:
    declare @cmd varchar(1024)
    
    select @cmd = "select name from sysobjects where type='U' order by name"
    
    exec (@cmd)
    Greetz,

    Bastiaan Olij

  3. #3
    Join Date
    Aug 2007
    Posts
    59
    Thanks boss

    it works

    thanks again


    Regards

  4. #4
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    No problem

    Sorry for the short reply btw, I was in a bit of a rush
    Greetz,

    Bastiaan Olij

  5. #5
    Join Date
    Aug 2007
    Posts
    59
    no need of sorry

  6. #6
    Join Date
    Aug 2007
    Posts
    59
    hi
    1 more question.

    Suppose i am taking the count of any table using this code, can i store the output to any variable ?
    or can i use this code as:

    declare @cmd int
    declare @cmd1 char(50)

    select @cmd1 = "select count(1) from mytable"
    select @cmd = exec (@cmd1)


    Regards

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your syntax is wrong.

    Syntax:
    Code:
    [exec[ute]] [@return_status  = ]
        [[[server .]database.]owner.]procedure_name[;number] 
            [[@parameter_name =] value  | 
                [@parameter_name =] @variable [output]
            [,[@parameter_name =] value  | 
                [@parameter_name =] @variable [output]...]] 
        [with recompile]
    
    OR:
    
    exec[ute] ("string" | char_variable 
        [+ "string" | char_variable]...)
    Code:
    declare @i int
    exec ('select @i=9')
    select @i
    Last edited by pdreyer; 02-08-08 at 03:18.

  8. #8
    Join Date
    Aug 2007
    Posts
    59
    hi
    i dont get how the syntax is wrong

    i used this syntax :

    ----

    declare @cmd1 int
    declare @cmd2 varchar(50)

    select cmd2 = " select @cmd1 = count(1) from mytable "

    exec ( @cmd2 )

    ------

    when i executed this syntax in a procedure it gives null output.
    i am expecting count of table "mytable" to be stored into the variable @cmd2
    please clarify.



    Regards

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by niranjan555
    i dont get how the syntax is wrong
    Quote Originally Posted by niranjan555
    select @cmd = exec (@cmd1)
    Quote Originally Posted by niranjan555
    i used this syntax :

    ----

    declare @cmd1 int
    declare @cmd2 varchar(50)

    select cmd2 = " select @cmd1 = count(1) from mytable "

    exec ( @cmd2 )

    ------

    when i executed this syntax in a procedure it gives null output.
    That looks OK but on older versions of Sybase (which I think you are using) it will produce an error when you exec ( @cmd2 )
    Must declare variable '@cmd1'.

    In older versions you'll have to use a temp table
    e.g.
    Code:
    create table #t1(i int)
    exec ('insert into #t1 select 9')
    select i from #t1
    drop table #t1

  10. #10
    Join Date
    Aug 2007
    Posts
    59
    i am using 12.5.3

    i have declared the variable as @cmd1 int

    it gives the null values in output

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by niranjan555
    i am using 12.5.3
    You'll have to use the temp table method

  12. #12
    Join Date
    Aug 2007
    Posts
    59
    hi

    i used the tempdb
    and it works fine

    thanks


    Regards

Posting Permissions

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