create proc pr_in
(
@UserName varchar(50),
@RealName varchar(30),
@Gprovince varchar(10),
@Gcity varchar(10),
@Gcounty varchar(20),
@Student_no varchar(20),
@Gschool varchar(20),
@Ggrade varchar(20),
@Gclass varchar(20),
@StudentID varchar(10)
)
as
declare @aid int
declare @Gid int
declare @cid int
if (@UserName <>'')
begin
if not exists(select 1 from User_Info where UserName=@UserName)
begin
insert into User_Info (UserName,NickName,Password,State,Children)values(@UserName,@UserName,'12345678',0,0)
select @aid=IDENT_CURRENT('User_Info')
insert into User_Fields (Uid,Uprovince,Ucity,Ucounty) values(@aid,@Gprovince,@Gcity,@Gcounty)
set @Gid=(select Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass)
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@aid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
update User_Info set Children=Children+1 where Uid=@aid
end
else
begin
set @cid=(select Uid from User_Info where UserName=@UserName)
if not exists(select 1 from User_Children where Uid=@cid and RealName=@RealName)
begin
update User_Info set Children=Children+1 where Uid=@cid
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
end
end
endexec pr_in '110','youmou','guang','zhan','xia','1111','bei','yiniai','jiyin','1100'最后一行插入为什么没有获取到@cid的值
还是就是我直接赋值的话也不进行插入的 是不是哪里格式不对了
求看光光
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
(
@UserName varchar(50),
@RealName varchar(30),
@Gprovince varchar(10),
@Gcity varchar(10),
@Gcounty varchar(20),
@Student_no varchar(20),
@Gschool varchar(20),
@Ggrade varchar(20),
@Gclass varchar(20),
@StudentID varchar(10)
)
as
declare @aid int
declare @Gid int
declare @cid int
if (@UserName <>'')
begin
if not exists(select 1 from User_Info where UserName=@UserName)
begin
insert into User_Info (UserName,NickName,Password,State,Children)values(@UserName,@UserName,'12345678',0,0)
select @aid=IDENT_CURRENT('User_Info')
insert into User_Fields (Uid,Uprovince,Ucity,Ucounty) values(@aid,@Gprovince,@Gcity,@Gcounty)
set @Gid=(select Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass)
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@aid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
update User_Info set Children=Children+1 where Uid=@aid
end
else
begin
set @cid=(select Uid from User_Info where UserName=@UserName)
if not exists(select 1 from User_Children where Uid=@cid and RealName=@RealName)
begin
update User_Info set Children=Children+1 where Uid=@cid
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
end
end
endexec pr_in '110','youmou','guang','zhan','xia','1111','bei','yiniai','jiyin','1100'最后一行插入为什么没有获取到@cid的值
还是就是我直接赋值的话也不进行插入的 是不是哪里格式不对了
求看光光
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
用set赋值的话,若查询不到则赋值为null如果你直接赋值进行插入的失败的话,要看具体错误信息来分析,一般常见的违反某些约束,或者有触发器干扰
你完全可以在调试的时候加几个print 来测试一下begin
set @cid=(select Uid from User_Info where UserName=@UserName)
PRINT @UserName
PRINT @cid
if not exists(select 1 from User_Children where Uid=@cid and RealName=@RealName)
begin
update User_Info set Children=Children+1 where Uid=@cid
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
end
end
end
create proc pr_in
(
@UserName varchar(50),
@RealName varchar(30),
@Gprovince varchar(10),
@Gcity varchar(10),
@Gcounty varchar(20),
@Student_no varchar(20),
@Gschool varchar(20),
@Ggrade varchar(20),
@Gclass varchar(20),
@StudentID varchar(10)
)
as
declare @aid int
declare @Gid int
declare @cid int
if (@UserName <>'')
begin
if not exists(select 1 from User_Info where UserName=@UserName)
begin
insert into User_Info (UserName,NickName,Password,State,Children)values(@UserName,@UserName,'12345678',0,0)
select @aid=IDENT_CURRENT('User_Info')
insert into User_Fields (Uid,Uprovince,Ucity,Ucounty) values(@aid,@Gprovince,@Gcity,@Gcounty)
set @Gid=(select Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass)
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@aid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
update User_Info set Children=Children+1 where Uid=@aid
end
else
BEGIN
--复值点1
--set @cid=(select Uid from User_Info where UserName=@UserName)
--BEGIN:换成
select @cid=Uid from User_Info where UserName=@UserName
PRINT CAST(ISNULL(@cid,-1) AS VARCHAR(20);
--END
if not exists(select 1 from User_Children where Uid=@cid and RealName=@RealName)
begin
update User_Info set Children=Children+1 where Uid=@cid
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno)
values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
end
end
endexec pr_in '110','youmou','guang','zhan','xia','1111','bei','yiniai','jiyin','1100'不知道这样更改对你有帮助吗?我没有表结构无法测试。你试试看吧
insert into User_Children
2、你确定程序已经走到了else那段?
在在存储过程中定义一个变量 @proc_cid
select @proc_cid=@cid;
把@proc_cid存入数据库中
(
@UserName varchar(50),
@RealName varchar(30),
@Gprovince varchar(10),
@Gcity varchar(10),
@Gcounty varchar(20),
@Student_no varchar(20),
@Gschool varchar(20),
@Ggrade varchar(20),
@Gclass varchar(20),
@StudentID varchar(10)
)
as
declare @aid int
declare @Gid int
declare @cid int
if (@UserName <>'')
begin
if not exists(select 1 from User_Info where UserName=@UserName)
begin
insert into User_Info (UserName,NickName,Password,State,Children)values(@UserName,@UserName,'12345678',0,0)
select @aid=IDENT_CURRENT('User_Info')
insert into User_Fields (Uid,Uprovince,Ucity,Ucounty) values(@aid,@Gprovince,@Gcity,@Gcounty)
select @Gid=Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@aid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
update User_Info set Children=Children+1 where Uid=@aid
end
else
beginselect @cid=Uid from User_Info where UserName=@UserName
select @Gid=Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass
if not exists(select 1 from User_Children where Uid=@cid and RealName=@RealName)
begin
insert into User_Children(Uid,RealName,StudentID,Cprovince,Ccity,Ccounty,Cschool,Cgrade,Cclass,Gid,GroupEdu,Studentno) values(@cid,@RealName,@StudentID,@Gprovince,@Gcity,@Gcounty,@Gschool,@Ggrade,@Gclass,@Gid,0,@Student_no)
update User_Info set Children=Children+1 where Uid=@cid
end
end
enddrop proc pr_in改成select了 提示Gid没值 没执行插入
(
@UserName VARCHAR(50) ,
@RealName VARCHAR(30) ,
@Gprovince VARCHAR(10) ,
@Gcity VARCHAR(10) ,
@Gcounty VARCHAR(20) ,
@Student_no VARCHAR(20) ,
@Gschool VARCHAR(20) ,
@Ggrade VARCHAR(20) ,
@Gclass VARCHAR(20) ,
@StudentID VARCHAR(10)
)
AS
DECLARE @aid INT
DECLARE @Gid INT
DECLARE @cid INT
IF ( @UserName <> '' )
BEGIN
IF NOT EXISTS ( SELECT 1
FROM User_Info
WHERE UserName = @UserName )
BEGIN
INSERT INTO User_Info
( UserName ,
NickName ,
Password ,
State ,
Children
)
VALUES ( @UserName ,
@UserName ,
'12345678' ,
0 ,
0
)
SELECT @aid = IDENT_CURRENT('User_Info')
INSERT INTO User_Fields
( Uid, Uprovince, Ucity, Ucounty )
VALUES ( @aid, @Gprovince, @Gcity, @Gcounty )
SELECT @Gid = Gid
FROM Jst_Chatgroup.dbo.Group_Info1
WHERE Gschool = @Gschool
AND Gclass = @Gclass
INSERT INTO User_Children
( Uid ,
RealName ,
StudentID ,
Cprovince ,
Ccity ,
Ccounty ,
Cschool ,
Cgrade ,
Cclass ,
Gid ,
GroupEdu ,
Studentno
)
VALUES ( @aid ,
@RealName ,
@StudentID ,
@Gprovince ,
@Gcity ,
@Gcounty ,
@Gschool ,
@Ggrade ,
@Gclass ,
@Gid ,
0 ,
@Student_no
)
UPDATE User_Info
SET Children = Children + 1
WHERE Uid = @aid
END
ELSE
BEGIN
PRINT '看看有没有值'
SELECT @cid = Uid
FROM User_Info
WHERE UserName = @UserName
SELECT @Gid = Gid
FROM Jst_Chatgroup.dbo.Group_Info1
WHERE Gschool = @Gschool
AND Gclass = @Gclass
IF NOT EXISTS ( SELECT 1
FROM User_Children
WHERE Uid = @cid
AND RealName = @RealName )
BEGIN
INSERT INTO User_Children
( Uid ,
RealName ,
StudentID ,
Cprovince ,
Ccity ,
Ccounty ,
Cschool ,
Cgrade ,
Cclass ,
Gid ,
GroupEdu ,
Studentno
)
VALUES ( @cid ,
@RealName ,
@StudentID ,
@Gprovince ,
@Gcity ,
@Gcounty ,
@Gschool ,
@Ggrade ,
@Gclass ,
@Gid ,
0 ,
@Student_no
)
UPDATE User_Info
SET Children = Children + 1
WHERE Uid = @cid
END
END
END
DROP PROC pr_in
那需要你分析一下这两句是否参数存在问题?select @Gid=Gid from Jst_Chatgroup.dbo.Group_Info1 where Gschool=@Gschool and Gclass=@Gclass
再请问个东西 插入数据的时候
VALUES(@StudentID)
@StudentID 为空时 我想插入null 应该怎么写?
再请问个东西 插入数据的时候
VALUES(@StudentID)
@StudentID 为空时 我想插入null 应该怎么写?
再请问个东西 插入数据的时候
VALUES(@StudentID)
@StudentID 为空时 我想插入null 应该怎么写?--一个小实例LZ 稍微改一下应该可以实现所要的功能
CREATE TABLE A(
STUID VARCHAR(50)
)
DECLARE @STUID VARCHAR(50)
SET @STUID=''
INSERT A(STUID) VALUES ((CASE WHEN @STUID='' THEN NULL ELSE @STUID END))
@Gprovince varchar(50),
@Gcity varchar(50),
@Gcounty varchar(50),
@Gschool varchar(50),
@Ggrade varchar(50),
@Gclass varchar(50)我都给到50了
将截断字符串或二进制数据。
语句已终止。
nvarchar(50) 数据库和存储过程里面都是用这个类型的
SELECT DATALENGTH('幼儿园大班(3)班_小杨美校区')-----------
26所以你那个最少要nvarchar(52)
@Gcity varchar(50),
@Gcounty varchar(50),
@Gschool varchar(50),
@Ggrade varchar(50),
@Gclass varchar(50)
这些换成和表的类型一直,长度一致
select a,
b,
c,
d
from tb这个语句在insert 的时候发生截断,那么可以用二分法,我先注释后面c、d两列,记住insert 也要对应注释,然后执行,如果不报错,那么问题就在后面两列,然后注释a、b,取消c、d的注释,再insert,如果报错,那么把d注释,再插入,如果没报错,那么就可以定位到d了,
你先定位是哪列