Professional Web Applications Themes

Maximum Length of String For Dynamic Stored Procedures - Microsoft SQL / MS SQL Server

I am current using dynamic stored procedures to gather information for my reports. It is not possible to use normal stored procedures as most of the criteria are dynamically build at runtime. The following is the structure of my stored procedures: CREATE PROCEDURE MyProc (criteria1 varchar(255), criteria2 varchar(255), criteria3 varchar(255)....) AS -- Create a variable SQLStatement DECLARE SQLStatement nvarchar(3900) -- Logic in building the SQLStatement SQLStatement = "select table1.field1, table2.field2, table2..... From table1 left join table on (table1.field1 =table2.field1) where " + criteria1 + "AND" + criteria2......... -- Execute the SQL statement EXEC(SQLStatement) The above structure will work fine if ...

  1. #1

    Default Maximum Length of String For Dynamic Stored Procedures

    I am current using dynamic stored procedures to gather information for
    my reports. It is not possible to use normal stored procedures as most
    of the criteria are dynamically build at runtime.

    The following is the structure of my stored procedures:

    CREATE PROCEDURE MyProc
    (criteria1 varchar(255),
    criteria2 varchar(255),
    criteria3 varchar(255)....)
    AS

    -- Create a variable SQLStatement
    DECLARE SQLStatement nvarchar(3900)

    -- Logic in building the SQLStatement
    SQLStatement = "select table1.field1, table2.field2, table2.....
    From table1 left join table on (table1.field1 =table2.field1)
    where "
    + criteria1 + "AND" + criteria2.........

    -- Execute the SQL statement
    EXEC(SQLStatement)


    The above structure will work fine if there are not many tables within
    the joins and the criteria string is not too large. But I am currently
    running in the problem that the max length of SQLstatement variable
    is only 3900 and I am unable to define a ntext variable within the
    store procedure.

    Currently we have already build a lot of the reports criteria using
    the above method. What would be the best workaround to this problem?
    Is there any workaround that require the least modification?

    Thanks
    Lawrence Yuen
    Lawrence Guest

  2. #2

    Default Re: Maximum Length of String For Dynamic Stored Procedures

    Did you ever really tried to do it ? As far as I know this thing doesn't
    work - EXEC(...) parameter may be no greater than 8000 chars length

    "Dejan Sarka" <si> wrote in
    message news:phx.gbl... 
    8,000 
    and 
    the 


    Alex Guest

  3. #3

    Default Re: Maximum Length of String For Dynamic Stored Procedures

    Thanks Dejan!

    Running EXEC('name_of_8000_char_string' +
    'another_name_of_8000_char_string') will work!

    Just for interest...
    This method will not work for sp_executesql right?

    Thanks!

    Lawrence Yuen

    "Dejan Sarka" <si> wrote in message news:<phx.gbl>... [/ref]
    Lawrence Guest

  4. #4

    Default Re: Maximum Length of String For Dynamic Stored Procedures

    > Just for interest... 

    Right.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org


    Dejan Guest

Similar Threads

  1. Limit on maximum String length?
    By Heman Robinson in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 19th, 05:34 PM
  2. CFML List Maximum Length
    By cfcoder2 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: July 9th, 09:12 PM
  3. maximum input buffer length exceeded: 1048576
    By Raquel Marques in forum PERL Miscellaneous
    Replies: 1
    Last Post: September 5th, 05:03 PM
  4. How to get length of string? length() problems
    By Mitchua in forum PERL Modules
    Replies: 5
    Last Post: July 17th, 12:08 AM
  5. ORA-01450: maximum key length (3166) exceeded...
    By Giovanni Azua in forum Oracle Server
    Replies: 1
    Last Post: December 20th, 08:51 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139