Click here to Skip to main content
14,328,524 members
Rate this:
Please Sign up or sign in to vote.
See more:
If mobile number is empty or null or 0 cannot insert record in tb_common_sms table.

for that stored procedure as follows
     USE [HIMT_Testing]
     GO
  
     SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  ALTER procedure [dbo].[WS_Course_waitlist]
   as
 begin
         declare @Mob_num varchar(15)

     if (len(ltrim(rtrim(@Mob_num))) > 9) and @Mob_num <> '' and @Mob_num <> 'NULL'
         begin	

        insert into tb_common_sms

      select a.stud_id,s.stud_mobile,'Dear Candidate, your' + RTRIM(a.cmn_minor_code) + ' Course has been confirmed for '+ convert(varchar(12),cbm.cbm_batch_start_dt,106) + '. Waitlist id: '+ convert(varchar(200), a.Waitlist_Id) + '. B,getdate(),'Online','a'
      from course_waitlist a,co_batch_master cbm,batch_Seats VW,student s
      where s.stud_id= a.stud_id and a.cr_active <> 'd' and vw.avil_seats > 0 and
      a.Batch_id = cbm.cbm_batch_id and a.batch_id=vw.cbm_batch_id
        end
end


When i execute the stored procedure

In tbcommon_sms table record is not insertd. the mobile number is there in the student table.

But when i execute the stored procedure, in tb common sms table record is not inserted.

I am validating if mobile number is empty or null or 0 record not to be inserted in tb common sms table.

if mobile number is there in student table, tb common sms table reocrd to be inserted using stored procedure.

for that how can i validate.
Posted
Updated 28-Jun-15 23:54pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

First off, this won't work:
@Mob_num <> 'NULL'
That compares the number agains teh string literal "NULL", rather than checking for a NULL value:
@Mob_num IS NOT NULL
Should work better.

Secondly, this kind of validation is really something you should be doing in the presentation layer, where you can report a problem to the user and get him to fix it. Byu the time it reaches SQL, it's normally far too late!

Finally, your INSERT code doesn't refer to the @Mob_num variable, so it won;t get inserted anywhere, even if it does validate correctly!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100