SQL 局部变量

发布于 2024-11-28 19:46:33 字数 1101 浏览 8 评论 0原文

我使用 SQL Server 2005 并且有一个这样的查询:

INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE
WHEN (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name') IS NULL THEN NULL
ELSE (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name')
END
AS 'sched_search_result',
   'Sample Value',
   'Sample Value'

这工作正常。它将值插入到[subject]表中,第一个值[sch_id]是通过搜索的CASE语句从[schedule]表中获取的。

我想避免代码冗余,因此我尝试将 SELECT 语句的结果存储在局部变量中,并使用 ISNULL 函数对其求值。看起来像这样:

DECLARE @sched INT
INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE ISNULL((SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name'), 0)
WHEN 0 THEN NULL
ELSE @sched
END
AS 'sched_search_result',
   'Sample value',
   'Sample value'

但我的代码不起作用。 SQL Server 2005 表示错误来自我使用局部变量 (@sched) 的行。我的查询有什么问题?

I use SQL Server 2005 and I have a query like this :

INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE
WHEN (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name') IS NULL THEN NULL
ELSE (SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name')
END
AS 'sched_search_result',
   'Sample Value',
   'Sample Value'

This works fine. It inserts values inside [subject] table, The first value [sch_id] is taken from [schedule] table through a searched CASE statement.

I want to avoid code redundancy, so I tried to store the result of the SELECT statement in a local variable and evaluate it using an ISNULL function. Looks like this :

DECLARE @sched INT
INSERT INTO [subject] ([sch_id],
                       [subj_from],
                       [subj_to],
)
SELECT
CASE ISNULL((SELECT @sched = [sch_id]
             FROM [schedule]
             WHERE [sch_name] = 'Searched Schedule Name'), 0)
WHEN 0 THEN NULL
ELSE @sched
END
AS 'sched_search_result',
   'Sample value',
   'Sample value'

But my code doesn't work. SQL Server 2005 says the error is coming from the line where I used the local variable (@sched). What is wrong in my query?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

悲歌长辞 2024-12-05 19:46:33

我想这会做你想做的。

INSERT INTO [subject] ([sch_id], [subj_from], [subj_to])
SELECT (
          SELECT [sch_id]
          FROM [schedule]
          WHERE [sch_name] = 'Search Schedule Name'
       ), 
      'from value',
      'to value'

I think this will do what you want.

INSERT INTO [subject] ([sch_id], [subj_from], [subj_to])
SELECT (
          SELECT [sch_id]
          FROM [schedule]
          WHERE [sch_name] = 'Search Schedule Name'
       ), 
      'from value',
      'to value'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文