ITKeyword,专注技术干货聚合推荐

注册 | 登录

使用Visual Studio 2008 自带的 Sql server 2005

dongzi87 分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:使用VS2008怎么连接自带的SQL Server2005的Express版本数据库

一、使用 Windows 认证模式 SqlConnection strConnection = new SqlConnection("Server=(local)//SQLExpress; Integrated Security=SSPI;"); // 连接到默认数据

up vote 1 down vote favorite

In SQL Server 2008 R2, in a stored procedure I have 2 DECLARE and a recursive CTE in a script:

DECLARE @MaxActivityDate DATETIME = (SELECT MAX(ActivityDate) FROM tbl)
DECLARE @MinActivityDate DATETIME = (SELECT MIN(ActivityDate) FROM tbl)

-- Generate all the dates between the minimum and the maximum
WITH DateRange (DateValue) AS
(
     SELECT @MinActivityDate DateValue
     UNION ALL
         SELECT DateValue + 1
         FROM DateRange
         WHERE DateValue + 1 <= @MaxActivityDate
)

SELECT DateValue
FROM DateRange
[...]

I now need to use this code in another stored procedure. I don't want any code duplication. How can I convert this into a function to use in both scripts?

sql-server tsql recursion sql-server-2008-r2 sql-function share | improve this question edited Nov 4 '15 at 21:25 marc_s 486k 104 944 1108 asked Nov 4 '15 at 21:10 Adam 704 12 29      Be careful when turning this into a function - it is "hiding" the fact that there's a database access in this function, and calling this code repeatedly inside a larger SELECT (returning a large number of rows) could cause horribly bad performance. –  marc_s Nov 4 '15 at 21:39      I've hidden some code from the question (to simplify the question) that adjusts the MIN, MAX numbers so that they're never 1 year apart. –  Adam Nov 4 '15 at 21:42      mssqltips.com/sqlservertip/2800/… –  JamieD77 Nov 4 '15 at 22:29 add a comment  | 

1 Answer 1

active oldest votes up vote 2 down vote

Be very careful with this type of cte. It is hidden RBAR all its own, even without being part of a larger query. This is the same issue as using a cte to count. Here is an awesome article explaining this issue. http://www.sqlservercentral.com/articles/T-SQL/74118/

For a better approach you should use a tally table. Here is one such article that goes deeper into this technique. http://www.sqlservercentral.com/articles/T-SQL/62867/

Here is a set based inline table valued function for doing this kind of thing.

推荐:[翻译]使用C#创建SQL Server的存储过程(Visual Studio 2005 + SQL Server 2005)

     摘要: 通常,开发人员使用的是T-SQL来创建SQL Server的存储过程、函数和触发器。 而现在的SQL Server 2005已经完全支持.NET通用语言运行时(CLR)了。 这就

create function GetDateRange
(
    @MinActivityDate datetime
    , @MaxActivityDate DATETIME
) RETURNS TABLE AS
    RETURN

    WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

    select DATEADD(DAY, N - 1, @MinActivityDate) as DateValue
    from cteTally t
    where t.N <= DATEDIFF(DAY, @MinActivityDate, @MaxActivityDate)

Now using this is super simple. Here is one way you could use this in your current example.

select dr.DateValue
from GetDateRange(myDate.MinDate, myDate.MaxDate) dr
cross apply 
(
    select MIN(ActivityDate) as MinDate
        , MAX(ActivityDate) as MaxDate
    from tbl
) myDate
share | improve this answer answered Nov 4 '15 at 22:31 Sean Lange 14.8k 1 12 27 add a comment  | 

Your Answer

  draft saved draft discarded

Sign up or log in

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Post as a guest

Name Email discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged sql-server tsql recursion sql-server-2008-r2 sql-function or ask your own question.

推荐:从远程Sql Server 2005服务器中导出数据到本地(本地数据库版本:Sql Server 2005)

今天,做了个测试:服务器的导入与导出数据.及脚本的生成.感觉挺好玩的.也挺简单的.现在把注意事项写下: 1.如果在本地已经有一个与服务器上相同结构的表.并且此表

up vote 1 down vote favorite In SQL Server 2008 R2, in a stored procedure I have 2 DECLARE and a recursive CTE in a script: DECLARE @MaxActivityDate DATETIME = (SELECT MAX(Activity

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。