Results 1 to 2 of 2

Thread: problem with dynamic csql

  1. #1
    Sushruth Nanduri Guest

    problem with dynamic csql


    I have a prolem in SQL6.5.
    I needed to generate a dynamic sql statement and execute it at run time in my
    stored procedure.
    This was never a problem in sql 7.0 because of sp_executesql and it can accept strings as parameter and these strings can be up to varchar(4000).
    But in 6.5 the maximum length that I can define is varchar(255).
    The string that I am generaing uses optimizer hints and will exceed this number.
    Is there anyother way to deal with this?
    On the other hand there is no sp_execute SP in 6.5.
    I can use only exec (@string).

    Thanks
    Sush.

  2. #2
    Greg G. Guest

    problem with dynamic csql (reply)

    I know of only one way to do this in SQL 6.5. You will need to create many variables then concat them in the EXEC statement. FOR Example:

    SET NOCOUNT ON
    DECLARE @string varchar(255),
    @string2 varchar(255)

    SELECT @string = 'SELECT * FROM tablename '
    SELECT @string2 = ' WHERE column_name = something '

    EXEC (@string + @string2)

    I hope this helps. SQL 7 is much easier. It is nice to create large variables when needed.


    Thanks
    Greg
    Maxim Group


    ------------
    Sushruth Nanduri at 1/22/01 4:16:29 PM


    I have a prolem in SQL6.5.
    I needed to generate a dynamic sql statement and execute it at run time in my
    stored procedure.
    This was never a problem in sql 7.0 because of sp_executesql and it can accept strings as parameter and these strings can be up to varchar(4000).
    But in 6.5 the maximum length that I can define is varchar(255).
    The string that I am generaing uses optimizer hints and will exceed this number.
    Is there anyother way to deal with this?
    On the other hand there is no sp_execute SP in 6.5.
    I can use only exec (@string).

    Thanks
    Sush.

Posting Permissions

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