怎么向函数中传递参数
日期：2014-05-18 浏览次数：20602 次
如何向函数中传递参数?
对于一个连结了多个表,语句很长(几十行)的查询中, 如何向函数中传递参数?
有没有简单的方法?
如下面的语句.
select top 10 a.id_chpch_b, a.id_pingm,
...... --此处有10行内容
b.date_chk, c.pingm, c.dw, d.shul_xd, e.dind_bh
from btr_chpch_m a
left outer join btr_chpch_b b on a.id_chpch_b=b.id_chpch_b
left outer join OPENROWSET('SQLOLEDB','server';'sa';'my1234','SELECT * FROM yx_erp.dbo.bmt_pingm') as c on a.id_pingm=c.id_pingm
left outer join btr_dind_m d on a.id_dind_m=d.id_dind_m
left outer join btr_dind_b e on d.id_dind_b=e.id_dind_b
left outer join OPENROWSET('SQLOLEDB','server';'sa';'my1234','SELECT * FROM yx_erp.dbo.bmt_keh') as f on e.id_keh=f.id_keh
where c.id_pingm is not null
------解决方案--------------------
对于一个连结了多个表,语句很长(几十行)的查询中, 如何向函数中传递参数?
有没有简单的方法?
如下面的语句.
select top 10 a.id_chpch_b, a.id_pingm,
...... --此处有10行内容
b.date_chk, c.pingm, c.dw, d.shul_xd, e.dind_bh
from btr_chpch_m a
left outer join btr_chpch_b b on a.id_chpch_b=b.id_chpch_b
left outer join OPENROWSET('SQLOLEDB','server';'sa';'my1234','SELECT * FROM yx_erp.dbo.bmt_pingm') as c on a.id_pingm=c.id_pingm
left outer join btr_dind_m d on a.id_dind_m=d.id_dind_m
left outer join btr_dind_b e on d.id_dind_b=e.id_dind_b
left outer join OPENROWSET('SQLOLEDB','server';'sa';'my1234','SELECT * FROM yx_erp.dbo.bmt_keh') as f on e.id_keh=f.id_keh
where c.id_pingm is not null
------解决方案--------------------
- SQL code
--'12/26/1999'为参数
A. 计算 ISO 周的标量值用户定义函数
下例中，用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数，必须在调用该函数前唤醒调用 SET DATEFIRST 1。
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
下面是函数调用。注意 DATEFIRST 设置为 1。
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。
ISO Week
----------------
52
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
