验证存储过程中的手机号码 [英] Validating the mobile number in stored procedure

问题描述

如果手机号码为空或为null或0无法在tb_common_sms表中插入记录。



该存储过程如下

< pre lang =SQL> USE [HIMT_Testing]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo]。[WS_Course_waitlist]
as
开始
声明 @ Mob_num varchar （ 15 ）

if （len（ltrim（rtrim（< span class =code-sdkkeyword> @ Mob_num ）））> 9 ）和 @ Mob_num <> ; ' ' 和 @ Mob_num <> ' NULL'
开始

insert into tb_common_sms

选择 a.stud_id，s.stud_mobile，' 亲爱的候选人，您的' + RTRIM（a.cmn_minor_code）+ ' 课程已确认为' + convert （ varchar （ 12 ），cbm.cbm_batch_start_dt ， 106 ）+ ' 。等候名单ID：' + 转换（ varchar （ 200 ），a.Waitlist_Id）+ ' 。 B，getdate（），'在线' ，' a '
来自course_waitlist a，co_batch_master cbm，batch_Seats大众，学生s
其中s.stud_id = a.stud_id和a。 cr_active<> ' d ' 和vw.avil_seats> 0和
a.Batch_id = cbm.cbm_batch_id和a.batch_id = vw.cbm_batch_id
结束
结束

当我执行存储过程时



在tbcommon_sms中，表记录未插入。学生表中有手机号码。



但是当我执行存储过程时，在tb中没有插入常见的短信表记录。



我正在验证手机号码是空的还是空或0记录不能插入tb公共短信表中。



if学生表中有移动号码，使用存储过程插入常用的短信表格。



我该如何验证。

解决方案

首先，这不起作用：

  @ Mob_num <> '  NULL' 

将数字再次与字符串文字NULL进行比较，而不是检查NULL值：

  @ Mob_num   IS   NOT   NULL  

应该更好。



其次，这种验证确实是你应该在表示层做的事情，你可以向用户报告问题并让他解决问题。 Byu到达SQL时，通常已经太晚了！



最后，你的INSERT代码没有引用@Mob_num变量，所以它赢了; t get插入任何地方，即使它确实正确验证了！

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.

解决方案

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!

这篇关于验证存储过程中的手机号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！