CONCAT_NULL_YIELDS_NULL OFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CONCAT_NULL_YIELDS_NULL OFF

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-22 : 20:20:28

Though the CONCAT_NULL_YIELDS_NULL is OFF. It still gives me null when I concat. For Null I need to get ""

SET CONCAT_NULL_YIELDS_NULL OFF
go

alter PROCEDURE [dbo].dvx_5_Export_ExportedData

AS
SELECT
('"' + o.code + '"') as custnum
,('"' + o.prospect + '"') as prospect
FROM Orders o

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-22 : 21:15:53

No need to use that since you can just do this:

alter PROCEDURE [dbo].dvx_5_Export_ExportedData

AS
SELECT o.code as custnum, o.prospect as prospect
FROM Orders o

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:02:21

quote:
Originally posted by mapidea

Though the CONCAT_NULL_YIELDS_NULL is OFF. It still gives me null when I concat. For Null I need to get ""

SET CONCAT_NULL_YIELDS_NULL OFF
go

alter PROCEDURE [dbo].dvx_5_Export_ExportedData

AS
SELECT
('"' + COALESCE(o.code,'') + '"') as custnum
,('"' + COALESCE(o.prospect,'') + '"') as prospect
FROM Orders o

you dont need to tamper with CONCAT NULL YIELDS NULL setting. just use COALESCE to convert NULLs to '' like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-23 : 11:32:01

Thanks a lot for your reply.

I can always use ISNULL or COALESCE. But I wanted to know why is SET CONCAT_NULL_YIELDS_NULL OFF not functioning

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-23 : 11:45:14

There is a difference between the value of the SET CONCAT_NULL_YIELDS_NULL when you execute the ALTER PROCEDURE and when you actually execute stored procedure. From BOL: "Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER."

If you want this setting to be active when the procedure it executed, you can either set the value before the call or explicitly set the value within the stored procedure. If you opt for the latter, the value will revert to its original value when you return, much like a local variable going out of scope.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources