SQL Server Performance Forum – Threads Archive
Null + ‘4’
I debug a sp in Query Analyzer. SET QUOTED_IDENTIFIER OFFGO
SET ANSI_NULLS OFF
GO alter proc test as
Declare
@test Char(25)
select @test = null
select @testout=’4′
select @[email protected] [email protected] print @test Return 0 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Why does it not print ‘4’ to me?
read the definition of null
what did you think null meant
quote:Originally posted by mliunsb
I debug a sp in Query Analyzer.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO alter proc test as
Declare
@test Char(25)
select @test = null
select @testout=’4′
select @[email protected] [email protected] print @test Return 0 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Why does it not print ‘4’ to me?
GO
SET ANSI_NULLS OFF
GO alter proc test as
Declare
@test Char(25)
select @test = null
select @testout=’4′
select @[email protected] [email protected] print @test Return 0 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Why does it not print ‘4’ to me?
ANSI_NULLS does not affect string concatenation. Check the setting of CONCAT_NULL_YIELDS_NULL:
When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, ‘string’ + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is ‘string’. Keith Payne
Technical Marketing Solutions
www.tms-us.com
Or you could use ISNULL to replace a possible null with an empty string: SET @test = ISNULL(@Test, ”) + ‘4’ By the way, when you declare a variable in T-SQL, it doesn’t get a default value. This is different from programming languages like VB, where a String variable already starts out as an empty string. In T-SQL, every variable is null until you assign a value.
well… unless you declare input values in a SP but unlike how he is doing it…. alter proc test (
@test Char(25) = NULL
@testout=’4′
) as
select @[email protected] [email protected] print @test
select @test Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
sorry…
alter proc test (
@test Char(25) = NULL
@testout=’4′
) as
select @test=isnull(@test,”) + @testout print @test
select @test Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
Defaults on procedure parameters are unpredictable. Here’s the deal when calling a procedure: If you do not mention the parameter, then the default value is used.
If you supply anything as the parameter, then the default is NOT used. So even with a default, you must check for NULLs in procedure parameters.
]]>