-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|