我似乎无法在没有错误的情况下执行存储过程代码，例如：
Msg 156，Level 15，State 1，Procedure task5proc，Line 37[Batch Start Line 37]
关键字"and"附近的语法不正确。Msg 156，Level 15，State 1，Procedure task5proc，Line 40[Batch Start Line 37]
关键字'else'附近的语法不正确。Msg 102，Level 15，State 31，Procedure task5proc，Line 71[Batch Start Line 37]
附近的语法不正确
此处的代码：
create table dbo.task5
(
id int identity,
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
maincolumn varchar(50)
)
insert into task5
values (null, null, null, null, '1-AS,2-34,3-DF,4-fG'),
(null, null, null, null, '3-AS,4-fG'),
(null, null, null, null, '1-sd,3-df,4-ds'),
(null, null, null, null, '1-25,2-ww,3-d,4-ss'),
(null, null, null, null, '1-sd,2-AS,4-fG')
select * from dbo.task5
go
create or alter proc dbo.task5proc (@maxcount int)
as
begin
declare @counter int, @var varchar(50)
set @counter = 1
set @var = (select maincolumn from dbo.task5 where id = @counter)
declare @give1 varchar(10), @give2 varchar(10), @give3 varchar(10), @give4 varchar(10)
while @counter <= @maxcount
begin
begin
if charindex('1-', @var , 1) = 0
set @give1 = 'N/P'
else
set @give1 = SUBSTRING(@var , charindex('1-', @var , 1)+2, charindex(',', @var , 1)-3)
end
begin
if (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) != 0
set @give2 = substring(@var, (charindex('2-', @var , 1)+2), (charindex(',', @var, charindex(',', @var,1)+1))-(charindex('2-', @var , 1)+2))
else if (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) = 0
set @give2 = SUBSTRING(@var , charindex('2-', @var , 1)+2, charindex(',', @var , 1)-3)
else
set @give2 = 'N/P'
end
begin
if (charindex('3-', @var,1)) != 0 and (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) != 0
set @give3 = substring(@var, (charindex('3-', @var,1)+2), charindex(',', @var,charindex(',', @var,(charindex(',',@var,1) +1)) +1) - (charindex('3-', @var,1)+2))
else if (charindex('3-', @var,1)) != 0 and (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) = 0
set @give3 = substring(@var, (charindex('3-', @var , 1)+2), (charindex(',', @var, charindex(',', @var,1)+1))-(charindex('2-', @var , 1)+2))
else if (charindex('3-', @var,1)) != 0 and (charindex('1-', @var , 1)) !=0 and and charindex('2-', @var , 1) = 0
set @give3 = substring(@var, (charindex('3-', @var , 1)+2), (charindex(',', @var, charindex(',', @var,1)+1))-(charindex('1-', @var , 1)+2))
else
set @give3 = 'N/P'
end
begin
if (charindex('4-', @var,1)) != 0 and (charindex('3-', @var,1)) != 0 and (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) != 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - charindex(',', @var,charindex(',', @var,(charindex(',',@var,1) +1)) +1))
else if (charindex('4-', @var,1)) != 0 and (charindex('3-', @var , 1)) = 0 and charindex('2-', @var , 1) != 0 and charindex('1-', @var , 1) != 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - charindex(',', @var,(charindex(',',@var,1) +1)))
else if (charindex('4-', @var,1)) != 0 and (charindex('2-', @var , 1)) =0 and charindex('1-', @var , 1) != 0 and charindex('3-', @var , 1) != 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - charindex(',', @var,(charindex(',',@var,1) +1)))
else if (charindex('4-', @var,1)) != 0 and (charindex('2-', @var , 1)) !=0 and charindex('1-', @var , 1) = 0 and charindex('3-', @var , 1) != 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - charindex(',', @var,(charindex(',',@var,1) +1)))
else if (charindex('4-', @var,1)) != 0 and (charindex('3-', @var , 1)) !=0 and charindex('2-', @var , 1) = 0 and charindex('1-', @var , 1) = 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - (charindex(',',@var,1) +1))
else if (charindex('4-', @var,1)) != 0 and (charindex('3-', @var , 1)) =0 and charindex('2-', @var , 1) != 0 and charindex('1-', @var , 1) = 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - (charindex(',',@var,1) +1))
else if (charindex('4-', @var,1)) != 0 and (charindex('3-', @var , 1)) =0 and charindex('2-', @var , 1) = 0 and charindex('1-', @var , 1) != 0
set @give4 = right(@var, (charindex('4-', @var, 1) + 2) - (charindex(',',@var,1) +1))
else
set @give4= 'N/P'
end
update task5(col1, col2, col3, col4)
set col1 = @give1, col2 = @give2, col3 = @give3, col4 = @give4
where id = @counter
set @counter = @counter + 1
set @var = (select maincolumn from dbo.task5 where id = @counter)
end
end
go
exec task5proc(select count(*) from dbo.task5)
你没有任何intelliSense吗？第57排有一个双and:
else if (charindex('3-', @var,1)) != 0
and (charindex('1-', @var , 1)) !=0
and and /*<--- THIS WONT WORK*/ charindex('2-', @var , 1) = 0
set @give3 = substring(@var, (charindex('3-', @var , 1)+2), (charindex(',', @var, charindex(',', @var,1)+1))-(charindex('1-', @var , 1)+2))