SQL 局部变量

发布于 2024-11-28 19:46:33 字数 1101 浏览 9 评论 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?

分享到QQ

分享到微博

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

发布评论

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

悲歌长辞 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'

回复收藏 0 原文