declare @t table (i int identity(1,1) not null , name nvarchar(100),country nvarchar(100) )
declare @na nvarchar(100)
insert @t (name)values(‘大众’),(‘丰田’),(‘日产’),(‘吉利’),(‘别克’),(‘本田’),(‘比亚迪’)
if exists(select name from @t)
begin
select @na=name from @t
if @na in (‘吉利’,’比亚迪’)
print ‘国产’
else
print ‘合资’
end
如上，这个只显示最后一条，不能批量处理，
编写函数单条结果处理，加入参数。
create function funcname
(
@arg nvarchar(100) —传入参数
)
returns nvarchar(100)
as
begin
declare @t table (i int identity(1,1) not null , name nvarchar(100),country nvarchar(100) )
declare @na nvarchar(100)
insert @t (name)values(‘大众’),(‘丰田’),(‘日产’),(‘吉利’),(‘别克’),(‘本田’),(‘比亚迪’)
if exists(select name from @t)
begin
declare @c nvarchar(100)
select @na=name from @t where name=@arg
if @na in (‘吉利’,’比亚迪’)
set @c=’国产’
else
set @c=’合资’
end
return @c
end
函数返回表值
create function funcname
(
@arg nvarchar(100) —传入参数
)
returns @t table(
i int,
name varchar(100)
)
as
begin
….
return
end
批量处理，游标
declare @t table (i int identity(1,1) not null , name nvarchar(100),country nvarchar(100) )
declare @na nvarchar(100)
insert @t (name)values(‘大众’),(‘丰田’),(‘日产’),(‘吉利’),(‘别克’),(‘本田’),(‘比亚迪’)
–select * from @t
if exists(select name from @t)
begin
declare curs cursor for
select name from @t
open curs
fetch next from curs into @na
while(@@FETCH_STATUS=0)
begin
if @na in(‘吉利’,’比亚迪’)
print @na+’,国产’
–update @t set country=’国产’ where name=@na
else
print @na+’,合资’
–update @t set country=’合资’ where name=@na
fetch next from curs into @na
end
close curs
deallocate curs
end
–select * from @t
如下
