Help with Coalesce and Int column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Coalesce and Int column

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-05-11 : 15:57:37

Hello,
I have a stored proc which accepts some parameters. Except for one Int value, the rest are varchar. I am using the Coalesce function to determine if a NULL parameter is passed in as:
...AND Height = COALESCE(@height, Height)....

The problem is that no rows are returned if I include this last statement as part of my WHERE clause. If I can don't pass in other parameters...which are the varchar types..by including the Height in my WHERE and not passing in a height parameter, I do not get the row. However, if I call the stored proc in the same way and remove the Height part of the WHERE clause, I get a row. Is there something that I need to do differently to handle int types and COALESCE?

Here is my stored proc:

CREATE PROCEDURE AdminSearch
(
@FirstName [varchar](50) = Null,
@MiddleName [varchar](50) = Null,
@LastName [varchar](50) = Null,
@Gender [char] (1) = Null,
@Birthdate [datetime] = Null,
@EyeColor [varchar](10)=Null,
@Haircolor [varchar](10)=Null,
@Height [int] = Null,
@Degree [VarChar](50)=Null
)
AS
SELECT IsNull(FirstName + SPACE(1), '') + isNull(MiddleName + SPACE(1), '') + IsNull(LastName + SPACE(1), '') As Name,
isNull(Degree, '') as Degree, IsNull(Gender, '') as Gender,
IsNull(Convert(char(2), DateDiff(year,Birthdate, Getdate()), 101), '') as Age,
IsNull(EyeColor, '') as eyeColor, isnull(Haircolor, '') as HairColor, isnull(Height, '') as height
FROM users WHERE FirstName Like @Firstname + '%' AND LastName Like @LastName + '%' AND MiddleName = COALESCE(@MiddleName, MiddleName)
AND Gender = COALESCE(@Gender, Gender) AND EyeColor = COALESCE(@EyeColor, EyeColor) AND HairColor = COALESCE(@HairColor, Haircolor)
AND Degree = COALESCE(@Degree, Degree) AND Height= COALESCE(@Height, Height)

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-05-11 : 17:22:11

One further piece of info...I notice that when the table columns in the query do contain a NULL value...then the query doesn't return any rows that it should.

For what I am trying to accomplish...would it better to change my Stored Proc to a dynamic sql query instead?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-11 : 18:41:28

quote:

One further piece of info...I notice that when the table columns in the query do contain a NULL value...then the query doesn't return any rows that it should.

For what I am trying to accomplish...would it better to change my Stored Proc to a dynamic sql query instead?

dhw -- you have a logic problem to tackle even before we get to the COALESCE issues.

If your data can have a NULL in a column you are searching ... and the way to indicate that you don't want to filter by a particular column is to pass in NULL as a paramter value (or allow it to default to Null) -- then how do you search just for columns that are Null? You can't!

Does this make sense?

- Jeff

Edited by - jsmith8858 on 05/11/2003 18:42:21

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-05-11 : 20:26:38

jeff...

yes, i see your point. And...what I have since tried to accomplish is using a solution similar to the dynamic sql articles I read from this site. However..I cannot get them to work. What I figured that I would do....is create a dynamic WHERE clause and only include columns if the parameter was not null.

I tried the following....and though the Sql statement looks okay when I issue a Print command, the exec always fails! Also, I should state that the FirstName and LastName are always passed in and I need to use the LIKE operator....which is causing me problems because of the need to add the '%' to the value.

CREATE PROCEDURE SearchUsers
(
@FirstName [varchar](50) = Null,
@LastName [varchar](50) = Null,
@MiddleName [varchar](50) = Null,
@Gender [char] (1) = Null,
@Birthdate [datetime] = Null,
@EyeColor [varchar](10)=Null,
@Haircolor [varchar](10)=Null,
@Height [int] = Null,
@Degree [VarChar](50)=Null
)
AS
DECLARE @SQL varchar (4000)

Set @SQL = 'SELECT IsNull(FirstName + SPACE(1), Space(0)) + isNull(MiddleName + SPACE(1), Space(0)) + IsNull(LastName + SPACE(1), Space(0)) As Name,
isNull(Degree, Space(0)) as Degree, IsNull(Gender, Space(0)) as Gender,
IsNull(Convert(char(2), DateDiff(year,Birthdate, Getdate()), 101), Space(0)) as Age,
IsNull(EyeColor, Space(0)) as eyeColor, isnull(Haircolor, Space(0)) as HairColor, isnull(Height, Space(0)) as height
FROM users '

Set @sql = @Sql + 'WHERE FirstName Like ' + char(39) + @Firstname + '%' + char(39)
Set @Sql = @sql + ' AND LastName Like ' + char(39) + @LastName + '%' + char(39)

IF @MiddleName is not null
set @Sql = @Sql + ' AND MiddleName Like ' + char(39) + @MiddleName + '%' + char(39)

IF @Gender IS NOT NULL
set @Sql = @Sql + ' AND Gender = ' + char(39) + @Gender + char(39)

IF @Birthdate IS NOT NULL -- need to conver to datetime!
set @Sql = @Sql + ' AND BirthDate = ' + char(39) + @BirthDate + char(39)

IF @EyeColor IS NOT NULL
set @Sql = @Sql + ' AND EyeColor = ' + char(39) + @EyeColor + char(39)

IF @Haircolor IS NOT NULL
set @Sql = @Sql + ' AND HairColor = ' + char(39) + @Haircolor + char(39)

IF @Height IS NOT NULL
set @Sql = @Sql + ' AND Height = ' + char(39) + @Height + char(39)

IF @Degree IS NOT NULL
set @Sql = @Sql + ' AND Degree = ' + char(39) + @Degree +char(39)

Exec @SQL
GO

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-05-12 : 10:25:35

Must have had some typo or something....because I re-wrote the stored proc (using the dynamic sql method) and now it works!

dhw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources