SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Stored Procedures and SET options


SQL Server Stored Procedures and SET options

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4958 Visits: 2153
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
Google Plus: +Nakul
j-squared
j-squared
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 406
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. Smile
tom.w.brannon
tom.w.brannon
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2523 Visits: 1467
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
ken.trock
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 1736
Thanks for this. I've always wondered how to get the current state of these SET options; @@Options!

Ken
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35222 Visits: 886
This was a good article. It reminded me of some of the issues I have run into in the past.
John Walker
John Walker
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4662 Visits: 293
Nice explanation, thanks for sharing this informative article with us
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search