華閱文章網如何驗證 SQL 語句的正確性
驗證sql語句的正確性：-- 定義你想要檢驗的SQL語句DECLARE @sql NVARCHAR(MAX)--'正確的語句'SET @sql = 'select * from Report_Test1'--'錯誤的語句'--SET @sql = 'select 1 from'DECLARE @testsql NVARCHAR(MAX), @result INTSET @testsql = N'set parseonly on; ' + @sqlEXEC @result = sp_executesql @testsql-- 如果SQL語句有誤，則@result不為0;IF @result = 0 BEGIN PRINT '正確的語法' --開始執行正確的SQL語句 --exec sp_executesql @sql ENDELSE BEGIN PRINT '錯誤的語法' --不執行任何操作 END。
SQL數據庫用戶驗證語句
SQL語句：select * from tablename where UserID='"+userid+"' and UserPwd='"+userpass+"'要不寫在存儲過程里：set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER procedure [dbo].[LoginProc]@userid char(10),@userpass char(10)asselect * from TUser where CUserID=@userid and CUserPwd=@userpassif exists(select * from sysobjects where name='ComInfoProc')drop procedure ComInfoProc。
如何驗證 SQL 語句的正確性
驗證sql語句的正確性：
-- 定義你想要檢驗的SQL語句
DECLARE @sql NVARCHAR(MAX)
--'正確的語句'
SET @sql = 'select * from Report_Test1'
--'錯誤的語句'
--SET @sql = 'select 1 from'
DECLARE @testsql NVARCHAR(MAX),
@result INT
SET @testsql = N'set parseonly on; ' + @sql
EXEC @result = sp_executesql @testsql
-- 如果SQL語句有誤，則@result不為0;
IF @result = 0
BEGIN
PRINT '正確的語法'
--開始執行正確的SQL語句
--exec sp_executesql @sql
END
ELSE
BEGIN
PRINT '錯誤的語法'
--不執行任何操作
END
SQL數據庫用戶驗證語句
SQL語句：
select * from tablename where UserID='"+userid+"' and UserPwd='"+userpass+"'
要不寫在存儲過程里：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[LoginProc]
@userid char(10),
@userpass char(10)
as
select * from TUser where CUserID=@userid and CUserPwd=@userpass
if exists(select * from sysobjects where name='ComInfoProc')
drop procedure ComInfoProc
sql 語句 驗證身份證號碼
像這樣寫個視圖就行了：create View eVMutiCard AS Select *,*,*,*,*,*,*e,*To, *fication, N'身份證長度不合常理' As Remark From employee b Where (Len(*fication) Not In (15,18) And *fication Is Not Null ) Or *fication is Null Union All Select *,*,*,*,*,*,*e,*To, *fication, N'身份證具有無效字符' As Remark From employee b Where Len(*fication) In (15,18) And Isnumeric(Case Len(*fication) When 18 Then Substring(*fication,1,17) Else *fication End) = 0 Union All Select *,*,*,*,*,*,*e,*To, *fication, N'身份證出生日期不合常理' As Remark From employee b Where Len(*fication) In (15,18) And (IsDate(Case When Len(*fication)=15 Then '19'+Substring(*fication,7,2)+'-'+Substring(*fication,9,2)+'-'+Substring(*fication,11,2) Else Substring(*fication,7,4)+'-'+Substring(*fication,11,2)+'-'+Substring(*fication,13,2) End)=0 Or Not ( (Case When Len(*fication)=15 Then '19'+Substring(*fication,7,2)+'-'+Substring(*fication,9,2)+'-'+Substring(*fication,11,2) Else Substring(*fication,7,4)+'-'+Substring(*fication,11,2)+'-'+Substring(*fication,13,2) End) Between '1900-01-01' And '2079-06-06')) Union All Select *,*,*,*,*,*,*e,*To, *fication, N'身份證校驗位不正確（第18位與校驗不符）' As Remark From employee b Where (Len(*fication) = 18 And substring(*fication,18,19) *ckIDCardCode(*fication) And *fication Is Not Null） 其中跟據國家規定的計算公式，計算18位身份證檢驗位的*ckIDCardCode如下：CREATE function GetCheckIDCardCode(@sfzh char(18)) returns char(1) as begin declare @r varchar(2) declare @i int if len(@sfzh) 18 set @r = 0 else set @i = cast(substring(@sfzh,1,1) as int) * 7 +cast(substring(@sfzh,2,1) as int) * 9 +cast(substring(@sfzh,3,1) as int) * 10 +cast(substring(@sfzh,4,1) as int) * 5 +cast(substring(@sfzh,5,1) as int) * 8 +cast(substring(@sfzh,6,1) as int) * 4 +cast(substring(@sfzh,7,1) as int) * 2 +cast(substring(@sfzh,8,1) as int) * 1 +cast(substring(@sfzh,9,1) as int) * 6 +cast(substring(@sfzh,10,1) as int) * 3 +cast(substring(@sfzh,11,1) as int) * 7 +cast(substring(@sfzh,12,1) as int) * 9 +cast(substring(@sfzh,13,1) as int) * 10 +cast(substring(@sfzh,14,1) as int) * 5 +cast(substring(@sfzh,15,1) as int) * 8 +cast(substring(@sfzh,16,1) as int) * 4 +cast(substring(@sfzh,17,1) as int) * 2 set @i = @i - @i/11 * 11 set @r = cast((case @i when 0 then 1 when 1 then 0 when 2 then 11 when 3 then 9 when 4 then 8 when 5 then 7 when 6 then 6 when 7 then 5 when 8 then 4 when 9 then 3 when 10 then 2 else '' end) as char) if (@r = 11) set @r='X' else set @r = @r set @r = '' + @r +'' return @r end。
sql 語句 驗證身份證號碼
幫你搜了一下，參考參考。
主要驗證SQL數據庫中已輸入的15位 及18位 身份證號碼的位數、出生年月日是否正確，可以過濾出大部分的輸入錯誤。or (len（身份證號）=18 and (Substring（身份證號，7,2）'20' or (Substring（身份證號，11,2）>12)or (Substring（身份證號，11,2） in (01,03,05,07,08,10,12) and Substring（身份證號，13,2）>31)or (Substring（身份證號，11,2） in (04,06,09,11) and Substring（身份證號，13,2）>30)or (Substring（身份證號，11,2）=02 and Substring（身份證號，13,2）>29）））---------------------- 下面是針對 15位 及18位 身份證號碼性別的驗證語句 -------------------- Access 不支持 Substring 查詢，可以替換為 mid 查詢。
select 序號，姓名，身份證號，性別from 身份表where (((len（身份證號）=15) and (Substring（身份證號，15,1） in (1,3,5,7,9)) and 性別'男'）or ((len（身份證號）=15) and (Substring（身份證號，15,1） in (2,4,6,8,0)) and 性別'女'））or (((len（身份證號）=18) and (Substring（身份證號，17,1） in (1,3,5,7,9)) and 性別'男'）or ((len（身份證號）=18) and (Substring（身份證號，17,1） in (2,4,6,8,0)) and 性別'女'））---------------------- 下面是針對 15位 及18位 身份證號碼位數與出生年月日的驗證 -------------------- Access 不支持 Substring 查詢，可以替換為 mid 查詢。select 序號，姓名，身份證號，性別from 身份表where (len（身份證號）15 and len（身份證號）18)or (len（身份證號）=15 and ((Substring（身份證號，9,2）>12)or (Substring（身份證號，11,2） > 31)or (Substring（身份證號，9,2） in (01,03,05,07,08,10,12) and Substring（身份證號，11,2）>31)or (Substring（身份證號，9,2） in (04,06,09,11) and Substring（身份證號，11,2）>30)or (Substring（身份證號，9,2）=02 and Substring（身份證號，11,2）>29)))。
