none
Can generated varchar parameter size on prepared statements be fixed? RRS feed

  • Question

  • Hello,

    we are going through the process of overriding update behavior for our LINQ classes by specifying a stored procedure instead of using runtime generated update statements because our DBA wants to minimize the load on prepared statement cache.

     

    The original problem we had with auto-generated update statements is that the SET clause was different every time based on columns changed. This can result in too many permutations of prepared update statements and our DBA did not like it.

     

    Now that I override the Update behavior by pointing it to a stored proc, LINQtoSQL generates a prepared statement that calls the stored proc. However, if the table contains a varchar column, the generated parameter size is different on every call.

     

    For example, I have a table with 4 columns:

    Id - int

    RowVersion - timestamp

    Col1 - int

    Col2 - varchar(50)

     

    Whenever I updated row and call SubmitChanges, the following SQL is generated.

     

    declare @p7 int
    set @p7=0
    exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[SPLINQTestUpdate] @Id = @p0, @RowVersion = @p1, @Col1 = @p2, @Col2 = @p3',N'@p0 int,@p1 timestamp,@p2 int,@p3 varchar(5),@RETURN_VALUE int output',@p0=1,@p1=0x00000000022E1DBE,@p2=1,@p3='abcaa',@RETURN_VALUE=@p7 output
    select @p7

     

    Although the column is varchar(50), the framework will generated the parameter size based on input data size. Is there a way to let the framework always specify varchar(50) so that all statements look identical except for actual parameter values?

     

    Thank you.

    -Albert

    Friday, November 14, 2008 5:23 PM