Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Question Unanswered: Changin SP functionality

    Suppose there is an stored proc. called SP1, and there is another one called SP2, and from SP1 you want to modify the functionality of SP2, for example before SP2 inserted 100 registers in a table, now you want to update instead of insert, or print the message "Hello world" instead of "Hello man!" or whatever,i.e. change the SP2 text and the next time i run this SP2 it executes the changes i make from SP1, how can i do this.

    How can you help me please!

    ThanX!!!!

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    One option would be - use of Dynamic SQL in SP2 controlled by parameters passed while executing SP2 from SP1.

    HTH.

  3. #3
    Join Date
    Nov 2003
    Posts
    52

    Thumbs up

    Hi mkalsi

    where can i find documentation about Dynamic SQL or what is it????

    ThanX!!!!

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Here is a sample...

    select @SelectCl1 = " select distinct account_no = case when a.account_no < 1000000 then ('0' + convert(varchar(12),a.account_no)) else convert(varchar(12),a.account_no) end,",
    @SelectCl2 = " account_title, branch_id = 'NYC', site_id = 'GV', account_type = '" + @p_AccountType + "', ccy_code_account",
    @Condition3 = " where a.account_no = c.account_no",
    @Condition4 = " and a.account_no between " + @c_StartAcct + " and " + @c_EndAcct

    if (@p_AccountName Is Not Null AND @c_PK Is Not Null)
    select @Condition6 = " and a.account_title >= '" + rtrim(@TitleKey) + "'"

    exec (@SelectCl1 +
    @SelectCl2 +
    @FromCl1 +
    @Condition1 +
    @Condition2 +
    @Condition3 +
    @Condition4 +
    @Condition5 +
    @Condition6 +
    @Condition7 +
    @Condition8 +
    @OrderBy
    )

  5. #5
    Join Date
    Nov 2003
    Posts
    52

    Thumbs up

    Hi thanX mkalsi you're suggestions help me but not at all, the text i want to modify is the static text code of SP2, for example i wanna search in the text code of SP2 (and also in others) if the word "HELLO" exists, if it does, then i wanna change it for another word directly in the syscomments system table and the next time i execute this SP then it compiles before the execution.

    Greetings!!!

  6. #6
    Join Date
    Nov 2002
    Posts
    207
    A sample would help. Thanx.

Posting Permissions

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