全写 cast(left(p_sTrueTel,7) as int) or cast(substring(p_sTrueTel,1,7) as int) or convert(int,substring(p_sTrueTel,1,7)) or convert(int,left(p_sTrueTel,7))
我先这么定义; declare @l_sProvince as Varchar(20) declare @l_sCity as varchar(20) declare @l_sCode varchar(5) 然后做SELECT Province,City,Code INTO @l_sProvince,@l_sCity, @l_sCode FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3操作,怎么老是提示Server: Msg 170, Level 15, State 1, Procedure PROC_ADD_TRUETEL, Line 75 Line 75: Incorrect syntax near '@l_sProvince'.这个错误啊?高手指点一下!谢谢了!
上述语法错误。declare @l_sProvince as Varchar(20),@l_iABCH0H1H2H3 varchar(100) declare @l_sCity as varchar(20) declare @l_sCode varchar(5)SELECT @l_sProvince=Province, @l_sCity=City, @l_sCode=Code FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3
一般这样的错误是什么原因啊?Incorrect syntax near the keyword 'BEGIN'.
原因很多:Begin附近有语法错误。
我这个@l_iABCH0H1H2H3 定义为numeric
看了好久,不知道错在哪里1!大家帮忙看看! BEGIN set @l_iABCH0H1H2H3 = convert(int,substring(@p_sTrueTel,1,7)) SELECT @l_sProvince =Province, @l_sCity = City, @l_sCode = Code FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3 --EXCEPTION if @@error<>0 set @p_iResult = -1 else set @p_iResult = 2 END
每次都提示那个begin "Incorrect syntax near the keyword 'BEGIN'."错误!搞不懂啊.
CREATE PROCEDURE PROC_ADD_TRUETEL ( @p_sOpFrom varchar(40), @p_sOpUser varchar(40), @p_sTrueTel varchar(20), @p_sPassword varchar(20), @p_iServiceState int, @p_iBillState int, @p_iResult int OUT ) as declare @l_iCount as numeric declare @l_iABCH0H1H2H3 as numeric declare @l_sProvince as Varchar(20) declare @l_sCity as varchar(20) declare @l_sCode varchar(5) BEGIN --判断真实号码是否存在,存在则返回1 set @l_iCount=0 BEGIN SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel -- EXCEPTION IF @@ERROR <> 0 set @l_iCount = -1 else set @l_iCount = 0 RETURN END IF @l_iCount>0 set @p_iResult=1 RETURN END IF BEGIN set @l_iABCH0H1H2H3 = convert(int,substring(@p_sTrueTel,1,7)) SELECT @l_sProvince =Province, @l_sCity = City, @l_sCode = Code FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3 --EXCEPTION if @@error<>0 set @p_iResult = -1 else set @p_iResult = 2 END BEGIN INSERT INTO T_TRUETEl(TrueTel,Password,ServiceState,BillState,Province,City,Code) VALUES(@p_sTrueTel,@p_sPassword,@p_iServiceState,@p_iBillState,@l_sProvince,@l_sCity,@l_sCode) COMMIT --EXCEPTION if @@error <> 0 set @p_iResult = -1 RETURN END set @p_iResult=0 BEGIN INSERT INTO T_SERVICEOP(OpFrom,OpUser,OpType,TrueTel,OpDesc) VALUES(@p_sOpFrom,@p_sOpUser,0,@p_sTrueTel,'添加真实号码');
--EXCEPTION if @@error <> 0 COMMIT else ROLLBACK RETURN END BEGIN INSERT INTO T_OPERATELOG(OpFrom,OpUser,OpType,OpDest,OpDesc) VALUES(@p_sOpFrom,@p_sOpUser,10,@p_sTrueTel,'添加真实号码');
--EXCEPTION if @@error <> 0 COMMIT else ROLLBACK RETURN END--EXCEPTION if @@error <> 0 RETURN else ROLLBACK set @p_iResult = -1 RETURN END GO
1、没必要的begin end 去掉 2、一旦执行到return语句就会返回整个存储过程,所以return显然太多了,你的存储过程有效代码如下,其它的都执行不到 CREATE PROCEDURE PROC_ADD_TRUETEL ( @p_sOpFrom varchar(40), @p_sOpUser varchar(40), @p_sTrueTel varchar(20), @p_sPassword varchar(20), @p_iServiceState int, @p_iBillState int, @p_iResult int OUT ) as declare @l_iCount as numeric declare @l_iABCH0H1H2H3 as numeric declare @l_sProvince as Varchar(20) declare @l_sCity as varchar(20) declare @l_sCode varchar(5) BEGIN --判断真实号码是否存在,存在则返回1 set @l_iCount=0 BEGIN SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel -- EXCEPTION IF @@ERROR <> 0 set @l_iCount = -1 else set @l_iCount = 0 RETURN END3、SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel 应该改为 SELECT @l_iCount=COUNT(*) FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel 4、numeric应该写成numeric(18,2) 或者 numeric(10)这样的格式 5、没有END IF语句,估计你的错误提示是因为这个
Convert()函数如:
cast('123' as int)convert(int,'123')
-->
left(p_sTrueTel,7)
or
substring(p_sTrueTel,1,7)一楼错误了
cast(left(p_sTrueTel,7) as int)
or
cast(substring(p_sTrueTel,1,7) as int)
or
convert(int,substring(p_sTrueTel,1,7))
or
convert(int,left(p_sTrueTel,7))
declare @l_sProvince as Varchar(20)
declare @l_sCity as varchar(20)
declare @l_sCode varchar(5)
然后做SELECT Province,City,Code INTO @l_sProvince,@l_sCity, @l_sCode FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3操作,怎么老是提示Server: Msg 170, Level 15, State 1, Procedure PROC_ADD_TRUETEL, Line 75
Line 75: Incorrect syntax near '@l_sProvince'.这个错误啊?高手指点一下!谢谢了!
declare @l_sCity as varchar(20)
declare @l_sCode varchar(5)SELECT
@l_sProvince=Province,
@l_sCity=City,
@l_sCode=Code
FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3
BEGIN
set @l_iABCH0H1H2H3 = convert(int,substring(@p_sTrueTel,1,7))
SELECT
@l_sProvince =Province,
@l_sCity = City,
@l_sCode = Code
FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3
--EXCEPTION
if @@error<>0
set @p_iResult = -1
else
set @p_iResult = 2
END
(
@p_sOpFrom varchar(40),
@p_sOpUser varchar(40),
@p_sTrueTel varchar(20),
@p_sPassword varchar(20),
@p_iServiceState int,
@p_iBillState int,
@p_iResult int OUT
)
as
declare @l_iCount as numeric
declare @l_iABCH0H1H2H3 as numeric
declare @l_sProvince as Varchar(20)
declare @l_sCity as varchar(20)
declare @l_sCode varchar(5)
BEGIN
--判断真实号码是否存在,存在则返回1
set @l_iCount=0
BEGIN
SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel
-- EXCEPTION
IF @@ERROR <> 0
set @l_iCount = -1
else
set @l_iCount = 0
RETURN
END IF @l_iCount>0
set @p_iResult=1
RETURN
END IF
BEGIN
set @l_iABCH0H1H2H3 = convert(int,substring(@p_sTrueTel,1,7))
SELECT
@l_sProvince =Province,
@l_sCity = City,
@l_sCode = Code
FROM T_PHONESECTION WHERE ABCH0H1H2H3=@l_iABCH0H1H2H3
--EXCEPTION
if @@error<>0
set @p_iResult = -1
else
set @p_iResult = 2
END BEGIN
INSERT INTO T_TRUETEl(TrueTel,Password,ServiceState,BillState,Province,City,Code) VALUES(@p_sTrueTel,@p_sPassword,@p_iServiceState,@p_iBillState,@l_sProvince,@l_sCity,@l_sCode)
COMMIT
--EXCEPTION
if @@error <> 0
set @p_iResult = -1
RETURN
END
set @p_iResult=0
BEGIN
INSERT INTO T_SERVICEOP(OpFrom,OpUser,OpType,TrueTel,OpDesc) VALUES(@p_sOpFrom,@p_sOpUser,0,@p_sTrueTel,'添加真实号码');
--EXCEPTION
if @@error <> 0
COMMIT
else
ROLLBACK
RETURN
END
BEGIN
INSERT INTO T_OPERATELOG(OpFrom,OpUser,OpType,OpDest,OpDesc) VALUES(@p_sOpFrom,@p_sOpUser,10,@p_sTrueTel,'添加真实号码');
--EXCEPTION
if @@error <> 0
COMMIT
else
ROLLBACK
RETURN
END--EXCEPTION
if @@error <> 0
RETURN
else
ROLLBACK
set @p_iResult = -1
RETURN
END
GO
2、一旦执行到return语句就会返回整个存储过程,所以return显然太多了,你的存储过程有效代码如下,其它的都执行不到
CREATE PROCEDURE PROC_ADD_TRUETEL
(
@p_sOpFrom varchar(40),
@p_sOpUser varchar(40),
@p_sTrueTel varchar(20),
@p_sPassword varchar(20),
@p_iServiceState int,
@p_iBillState int,
@p_iResult int OUT
)
as
declare @l_iCount as numeric
declare @l_iABCH0H1H2H3 as numeric
declare @l_sProvince as Varchar(20)
declare @l_sCity as varchar(20)
declare @l_sCode varchar(5)
BEGIN
--判断真实号码是否存在,存在则返回1
set @l_iCount=0
BEGIN
SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel
-- EXCEPTION
IF @@ERROR <> 0
set @l_iCount = -1
else
set @l_iCount = 0
RETURN
END3、SELECT COUNT(*) INTO l_iCount FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel
应该改为
SELECT @l_iCount=COUNT(*) FROM T_TRUETEL WHERE TrueTel=@p_sTrueTel
4、numeric应该写成numeric(18,2) 或者 numeric(10)这样的格式
5、没有END IF语句,估计你的错误提示是因为这个