SQL Server Stored Procedures and SET options

  • Nakul Vachhrajani

    SSChampion

    Points: 10160

    Comments posted to this topic are about the item SQL Server Stored Procedures and SET options

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • j-squared

    SSC Veteran

    Points: 297

    Good article, Nakul, with thorough tests. Thanks for posting.

    One small quibble: ansi_warnings is actually respected inside stored procedures. The behavior you demonstrated occurs because SQL Server doesn't raise errors (regardless of the ansi_warnings setting) when you assign a too-long value to a char/varchar variable - this is true in both ad-hoc queries and stored procedures - and it handles assigning the values of stored procedure & UDF parameters the same way as assigning variables.

    Here's a quick test to see that even with ansi_warnings on, assigning a too-long string to a varchar variable produces no error in ad-hoc SQL:

    set ansi_warnings on

    -- this won't generate an error

    declare @Small varchar(3)

    set @Small = 'Long Text'

    select @Small -- selects 'Lon'

    -- this will generate a "String or binary data would be truncated" error

    declare @test table (

    Small varchar(3)

    )

    insert into @test (

    Small

    )

    values (

    'Long Text'

    )

    So when you declare your stored procedure with a parameter that's the same length as a corresponding column in the table you'll insert/update in, the too-long value gets silently truncated to the length allowed by the parameter (the same as assigning a variable), and then is short enough to fit into the column without an error.

    You're right that you can perform the length checks in the client application code, and in fact that's pretty much necessary if the application wants to present the user with friendly validation messages instead of SQL errors, but you can still use ansi_warnings as a fallback when you're using stored procedures: just declare the sproc's parameters to be one character longer than is allowed in the corresponding columns. Here's an example:

    set ansi_warnings on

    GO

    create procedure dbo.TruncationTest (

    -- the parameter is one character longer than the "Small" column

    @String varchar(4)

    )

    as begin

    declare @test table (

    Small varchar(3)

    )

    insert into @test (

    Small

    )

    values (

    @String

    )

    end

    GO

    -- this will generate a "String or binary data would be truncated" error

    exec dbo.TruncationTest 'Long Text'

    Hope you find that helpful. 🙂

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    Thank you for this post! I found a small problem in the first block of code. PRINT 'ANSI_WARNINGS is OFF'

    is not executed because SELECT @userOptions = @@OPTIONS; is executed before ANSI_WARNINGS is reset.

  • ken.trock

    SSCertifiable

    Points: 5147

    Thanks for this. I've always wondered how to get the current state of these SET options; @@Options!

    Ken

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    This was a good article. It reminded me of some of the issues I have run into in the past.

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    Nice explanation, thanks for sharing this informative article with us

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply