stop null return

hi,

can anyone tell me how i can replicate the Access function NZ please?

i want to use it with charindex when parsing text strings

many thanks

create function dbo.NZ (@a sql_variant,@y varchar(20))
returns sql_variant
as
begin
declare @myvar sql_variant
if isnull(@a,1) = 1
begin
set @myvar=@y
end
else
begin
set @myvar=@a
end
return @myvar
end
go
--Usage:
select dbo.NZ(NULL,'haha') as results
--results = haha
select dbo.NZ(123,'haha') as results
--results = 123
select dbo.NZ('ABCDE','haha') as results
--results ABCDE

OR


Create function dbo.NZ (@a sql_variant,@y sql_variant)
returns sql_variant
as
begin
declare @myvar sql_variant
if isnull(@a,1) = 1
begin
set @myvar=@y
end
else
begin
set @myvar=@a
end
return @myvar
end
go
--Usage:
select dbo.NZ(NULL,'haha') as results
--results = haha
select dbo.NZ(123,'haha') as results
--results = 123
select dbo.NZ('ABCDE','haha') as results
--results ABCDE
select dbo.NZ('ABCDE',0) as results
--results ABCDE
select dbo.NZ(NULL,1) as results
--results 1

Why not simply use the IsNull() function? Or the more progressive Coalesce() function?

--Using ISNULL
declare @x int
set @x=NULL
select isnull(@x,'not found')

--results
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'not found' to a column of data type int.


--USING NZ
declare @x int
set @x=NULL
select dbo.NZ(@x,'not found')

--results
not found

Ah, I see the difference now. Thanks!!

Many thanks MAK, just what I was looking for.

No Worries.