Results 1 to 8 of 8

Thread: stop null return

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    Why not simply use the IsNull() function? Or the more progressive Coalesce() function?

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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

  6. #6
    Join Date
    Feb 2003
    Posts
    1,048
    Ah, I see the difference now. Thanks!!

  7. #7
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    Many thanks MAK, just what I was looking for.

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •