Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Question Unanswered: sp_execute_sql, SELECT statement > 4000 chars - HELP!

    The problem with the statement (below) snipped from a stored procedure Ive been using is that previously it worked fine, however more joins have been added to the procedure, and the SELECT query now exceeds 4000 chars.

    I've been trying to get it to work, by removing sp_executesql, and using EXEC on its own, e.g.
    EXEC @status2 = (@sql_select + @sql_from + @sql_where + @sql_order_by),
    N'@known_id INT
    ,@sort_column NVARCHAR(30)....

    but my syntax is wrong and I can't figure it out. Can anyone help please? Thank you thank you in advance....:-)


    <snip>
    DECLARE @sql_stmt NVARCHAR (4000)
    SELECT @sql_stmt = (@sql_select + @sql_from + @sql_where + @sql_order_by)

    EXEC @status2 = sp_executesql @sql_stmt,
    N'@known_id INT
    ,@sort_column NVARCHAR(30)
    ,@first_var SMALLINT
    ,@second_var SMALLINT'

    ,@known_id
    ,@sort_column
    ,@first_var
    ,@second_var

    <end snip>

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    For example,

    set @sql_select = 'SELECT * FROM TABLE1'
    exec (@sql_select)

    works. It's sp_executesql that sets the status code.

    Do you want to skip sp_executesql just to bring down the length of the SQL string?

  3. #3
    Join Date
    Nov 2002
    Posts
    2
    I cant reduce the number of characters to less than 4000 so apparently I need to execute the statement using EXEC instead of sp_executesql (which wont take concatenated strings). I need to use Unicode here.

    But I just cant seem to get the syntax right.

Posting Permissions

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