Null checking in backend - SQL Server Forums

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

SQL Server Development (2000)

Null checking in backend

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 06:25:02

Hello,

Whether checking for null in front end or checking it in sql is good? Or both we have to do?

Like

create procedure sp_Test1
@name as nvarchar(100)
as
begin
if @Name<>Null
Select @Sql = 'select * from student Where Name=@Name
End

Kristen
Test

22859 Posts

Posted - 2006-05-30 : 06:27:41

WHERE name = @name

will return zero rows if "@name" is null (assuming default ANSI NULLs setting etc.)

Would that do? If so no check is required as there WHERE clause will "fail"

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-30 : 06:33:24

If you call that procedure from front end, then validate NULL there and send only valid data as parameter value

Madhivanan

Failing to plan is Planning to fail

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 06:41:15

Hello Kristen,

No, No, I was asking a general question only, which one will be good...whether checking it in back end will take time or not.?
Regards
Ceema

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-05-30 : 06:47:27

Hello Madhivanan,

Thank you so much.

Regards
Ceema

Kristen
Test

22859 Posts

Posted - 2006-05-30 : 08:12:46

"whether checking it in back end will take time or not.?"

OK, on that basis then the check is worthwhile. If will take very little time to do the IF check, whereas the actual SELECT statement will need some time to make the query plan etc. - even if it comes back with zero rows.

However, your IF will mean that there is NO resultset if @NAME is null, and your application will need to be adjusted therefore. So it may be better to just do the SELECT (which will make a resultset with zero rows) if that is what the application is expecting.

Note that you should not use "=" or "<>" with NULL and the correct syntax is:

if @Name IS NOT Null

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources