SQL 局部变量
我使用 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我想这会做你想做的。
I think this will do what you want.
这应该准确地告诉您您需要什么: http:// /msdn.microsoft.com/en-us/library/aa259186(v=sql.80).aspx
this should tell you exactly what you need: http://msdn.microsoft.com/en-us/library/aa259186(v=sql.80).aspx