Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008

    Unanswered: Stored procedure - multi line string in set statement


    I have somewhat of a large query that I need to put into a variable so I can prepare it.

    What I'd like to do, so I don't lose all the formatting, is say something like this:

    SET var = "select col1, col2, col3,nullif('','')
    from table
    where col1 in (1,2,3,4,5,6)";

    Data Studio Developer gets angry with me because of the multi line aspect of this.

    I Think this works

    SET var = "select col1, col2, col3,nullif('','')" ||
    " from table" ||
    " where col1 in (1,2,3,4,5,6)";

    Is there an easier and less sloppy way to do this?


    In case you're curious about the entire issue:

    I'm first dynamically building a key list. But for the sake of my question:

    SET keylist = "1,2,3,4,5";

    SET keys = "(" || keylist || ")";

    Then injecting keys into the select statement stored in "var".

    Then I'm going to prepare it and run it to return rows. But keep in mind, my main question is how to set a string variable to multiple rows of text easily.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    No, there is no other way to setup the string.


Posting Permissions

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