Concat NULL yields NULL inside a Function

[Fabricio Dos Santos Antunes]

I need to use a function inside my database that need have the option Concat NULL yields NULL ON, however my database doesn't use that, so , I need to ensure that'll be ON when the function executes. But seens not possible to define that inside the function ( Set .. On, is not allowed ). There's any way to do that? :)

[Simon_Whale]

assuming that you are using SQL server have a read of this. http://stackoverflow.com/questions/5702190/concatenate-null-value-columns-in-tsql[^] the first answer shows you how to do this with version prior to sql server 2012 and with sql server 2012.

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

[Mycroft Holmes]

So code around it, ISULL(FieldName,'') will do for text fields

Never underestimate the power of human stupidity RAH

[Richard Deeming]

Microsoft have already stated[^] that the option to turn CONCAT_NULL_YIELDS_NULL off will be removed in a future version. You should probably be looking to update the database so that it works with this setting turned on.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Fabricio Dos Santos Antunes]

Yeah, we should start think about it here, thank you. The problem is that we're using that on the return of a XML. The error are on the XML return. to solve, before call the function we got to use this.

SET CONCAT_NULL_YIELDS_NULL ON;
GO

This is our function

CREATE FUNCTION Function_StripHTML(@Text NVARCHAR(MAX), @BreakLine BIT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @XML AS XML

IF @BreakLine = 1
BEGIN
	SET @Text= REPLACE(REPLACE(REPLACE(@Text,'  

', CHAR(13)), '
',CHAR(13)), '
',CHAR(13))
END
ELSE
BEGIN
SET @Text= REPLACE(REPLACE(@Text, '
','
'), '
','
')
END

SET @XML = CAST(('' + REPLACE(REPLACE(dbo.Function\_RemoveInvalidCharacters(@Text), '&', '&'), '<', '')+'') AS XML)

SELECT
	@Text=
	(
		SELECT
			CASE WHEN LEFT(N.value('.', 'VARCHAR(MAX)'), 3) = 'br>' THEN
				'<' + N.value('.', 'VARCHAR(MAX)')
			ELSE
				STUFF
				(
					N.value('.', 'VARCHAR(MAX)')
					, 1
					, CHARINDEX('>', N.value('.', 'VARCHAR(MAX)'))
					, ''
				)
			END
		FROM
			@XML.nodes('x') AS T(N)
		  FOR XML PATH('')
		, TYPE
	).value('.', 'VARCHAR(MAX)')

RETURN LTRIM(RTRIM(@Text))

END
GO

I'm sorry if the code looks confuse, was made for a coworker here. :-O