!!!!!!!!!求写一函数!!!!!!!!!!!!

Question

--测试环境
if not object_id('tb') is null   drop table tb
Go
create table tb([date] datetime,[no] nvarchar(20),[prono] nvarchar(24),[qty1] float,[qty2] float,[qty3] float)
Insert tb
select '2010-08-19','20100819001','EMC-2020',85,65,69 union all
select '2010-08-20','20100820002','EMC-2021',97,58,54 union all
select '2010-08-21','20100821003','EMC-2022',14,45,45 union all
select '2010-08-22','20100822004','EMC-2023',24,67,23 union all
select '2010-08-23','20100823005','EMC-2024',57,34,12 union all
select '2010-08-24','20100824006','EMC-2025',75,43,78 union all
select '2010-08-25','20100825007','EMC-2026',47,93,98 union all
select '2010-08-26','20100826008','EMC-2027',44,73,35 union all
select '2010-08-27','20100827009','EMC-2028',77,74,45
go
select * from tb
--select [no],[prono],[qty1],[qty2],[qty3] from tb
--语句查询结果为:[no] [prono] [qty1] [qty2] [qty3]
20100819001 EMC-2020          85.0 65.0 69.0
20100820002 EMC-2021          97.0 58.0 54.0
20100821003 EMC-2022          14.0 45.0 45.0
20100822004 EMC-2023          24.0 67.0 23.0
20100823005 EMC-2024          57.0 34.0 12.0
20100824006 EMC-2025          75.0 43.0 78.0
20100825007 EMC-2026          47.0 93.0 98.0
20100826008 EMC-2027          44.0 73.0 35.0
20100827009 EMC-2028          77.0 74.0 45.0
怎么根据上面查询结果写这样一个函数
create      function fun_tb(@date nvarchar(10),@no nvarchar(20))
returns @tb table([no] nvarchar(20),prono nvarchar(50),qty1 float,qty2 float, qty3 float)
实现这样功能,如:
select * from fun_tb('2010-08-19','20100819001') 结果为:
[no] [prono] [qty1] [qty2] [qty3]
20100819001 EMC-2020 85.0 65.0 69.0

Answer 1

就直接select * from tb where no='20100819001'  算了

Answer 2

这个适合用存储过程，不适合用函数create proc test
@date varchar(10),
@no nvarchar(20)
asselect * from tb where convert(varchar(10),[date],120)=@date and no=@nogoexec test '2010-08-19','20100819001'

Answer 3

create function fun_tb(@date nvarchar(10),@no nvarchar(20))
returns @tb table([no] nvarchar(20),prono nvarchar(50),qty1 float,qty2 float, qty3 float)
as 
begin
  insert @tb
  select [no],[prono],[qty1],[qty2],[qty3] from tb
  where datediff(dd,[date],@date)=0
  and no=@no
  return
end
goselect * from fun_tb('2010-08-19','20100819001')
/**
no                   prono                                              qty1                   qty2                   qty3
-------------------- -------------------------------------------------- ---------------------- ---------------------- ----------------------
20100819001          EMC-2020                                           85                     65                     69(1 行受影响)
**/