Question

SQL编写一个自定义函数或者存储过程，能够实现参数化查询的功能。

Answer 1

-- Description: 根据输入的中继及区域,确定查询的数据
-- =============================================
alter PROCEDURE 中继话务量查询
--drop PROCEDURE 中继话务量查询
--CREATE PROCEDURE 中继话务量查询
-- Add the parameters for the stored procedure here
@开始日期 datetime,
@结束日期 datetime,
@查询区域 int,
@查询中继 int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if @查询区域=99
begin /* 查询全部区域 */
if @查询中继=0
begin
SELECT exch_id, trk_in, date, total_dur, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (date <= @结束日期)
end
else
begin
SELECT exch_id, trk_in, date, total_dur, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (trk_in =@查询中继 ) and
(date <= @结束日期)
end
end
else
begin
if @查询中继=0 /* 查询全部中继*/
begin
SELECT exch_id, trk_in, date, total_dur, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (date <= @结束日期) AND (exch_id = @查询区域)
end
else
begin
SELECT exch_id, trk_in, date, total_dur, bill_num
FROM trk_in_日汇总2007
WHERE (date >= @开始日期) AND (trk_in =@查询中继 ) and
(date <= @结束日期) AND (exch_id = @查询区域)
end
end
END
GO