create function fun_getStu2(@birth dateTime='1987-1-1',@sex int)
returns @myTable table
(
s_id int,
s_name varchar(10),
s_sex varchar(10),
s_birth datetime
)
as
begin
declare @sqlStr nvarchar(200)
set @sqlStr='insert into @myTable select s_id,s_name,case s_sex when 1 then ''男''
when 0 then ''女'' end as s_sex,s_birth from student where 1=1';
if(@birth is not null)
set @sqlStr=@sqlStr+' and s_birth>'+cast(@birth as varchar(20))
if(@sex is not null)
set @sqlStr=@sqlStr+' and s_sex='+cast(@sex as varchar(10));
exec sp_executesql @sqlStr,@myTable
return
end
#2. sp_executesql中也不能传入表变量
你的需求还是用存储过程做吧
create proc proc_getStu2
@birth dateTime='1987-1-1',
@sex INT
AS
begin
declare @myTable table
(
s_id int,
s_name varchar(10),
s_sex varchar(10),
s_birth datetime
)
declare @sqlStr nvarchar(200)
set @sqlStr=N'select s_id,s_name,case s_sex when 1 then N''男''
when 0 then N''女'' end as s_sex,s_birth from student where 1=1';
if(@birth is not null)
set @sqlStr=@sqlStr+N' and s_birth>'''+cast(@birth as varchar(20))+''''
if(@sex is not null)
set @sqlStr=@sqlStr+N' and s_sex='+cast(@sex as varchar(10)); INSERT @myTable(s_name)
exec sp_executesql @sqlStr
SELECT * FROM @myTable
end--TEST
EXEC proc_getStu2 @sex =1
这是什么意思
应该是INSERT @myTable