Tech Off Thread

3 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Function not functioning

Back to Forum: Tech Off
  • User profile image
    W3bbo

    How ironic, a function not functioning Smiley So anyway, here's my SQL Function:

    ALTER  FUNCTION dbo.WhereGenerate (
    @FilterA datetime = NULL,
    @FilterB datetime = NULL,

    @FilterC tinyint = NULL, @FilterD tinyint = NULL, @FilterE tinyint = NULL, @FilterF tinyint = NULL, @FilterG tinyint = NULL,
    @FilterH bit = NULL )
    RETURNS nvarchar(4000) AS BEGIN     DECLARE @SQL nvarchar(4000)     SET @SQL = N'' -------------------------------------------------------------------------------------------     If @FilterA <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.A < ''' + @FilterA + ''' '     END     If @FilterB <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.B > ''' + @FilterB  + ''' '     END -------------------------------------------------------------------------------------------     If @FilterC <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.C = ' + @FilterC + ' '     END     If @FilterD <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.D = ' + @FilterD + ' '     END     If @FilterE <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.E = ' + @FilterE + ' '     END     If @FilterF <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.F = ' + @FilterF + ' '     END     If @FilterG <> NULL BEGIN         SET @SQL = @SQL + N'AND Foo.G = ' + @FilterG + ' '     END -------------------------------------------------------------------------------------------     IF @FilterH <> NULL BEGIN         IF @FilterH = 0 BEGIN             SET @SQL = @SQL + N'AND Foo.H = 0 '         END ELSE BEGIN             SET @SQL = @SQL + N'AND Foo.H = 1 '         END     END
        RETURN @SQL END
    Yet when I call it with these params:
    SELECT dbo.WhereGenerate(Null, Null, Null, Null, Null, 4, Null, Null)
    I get nothing returned. So... what's up?

  • User profile image
    Maurits

    You have two problems.

    1) <> NULL
    The correct way to spell that is, "IS NOT NULL" -- nulls are never == to anything, and nulls are never <> to anything.
    NULL = NULL is false, NULL <> NULL is also false.

    2) concatenation

    You can't concatenate strings and datetimes, or strings and ints, with + in SQL.

    SET @SQL = @SQL + N'AND Foo.F = ' + @FilterF + ' '
    should be
    SET @SQL = @SQL + N'AND Foo.F = ' + CAST(@FilterF AS VARCHAR) + ' '

    Note that VARCHAR is implicitly 20 unless you specify otherwise... say, with VARCHAR(50)

  • User profile image
    W3bbo

    Thanks, working now. Smiley

    I didn't think I had to Cast() it. On another function (where I used bigint, rather than tinyint) it threw an error about Casting, but not with tinyints, so I didn't think I have to.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.