使用 select inside coalesce 设置变量

发布于 2024-09-16 01:00:56 字数 407 浏览 12 评论 0原文

如何修复存储过程的这一部分？

select 将返回 1、0 或 null。如果 select 返回 1 或 0，我希望将 @override 设置为该值。如果它返回 null，那么我希望将 @override 设置为 1。

我的语法有问题；我被告知“'select'附近的语法不正确”和“')'附近的语法不正确”。

这是sql：

set @override = (coalesce(select override from groupPreferences g inner join
preferences p on g.preferenceId = p.preferenceId where groupId = 13
and description = 'myDescription'), 1))

原文

How do I fix up this part of my stored procedure?

The select will either return 1, 0, or null. If the select returns 1 or 0, I want @override to be set to that value. If it returns null, then I want @override to be set to 1.

Something is wrong with my syntax; I am told "incorrect syntax near 'select'" and "incorrect sytax near ')'".

Here is the sql:

set @override = (coalesce(select override from groupPreferences g inner join
preferences p on g.preferenceId = p.preferenceId where groupId = 13
and description = 'myDescription'), 1))

分享到QQ

分享到微博

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

发布评论

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

梦冥 2024-09-23 01:00:56

set @override = (coalesce((select override from groupPreferences g inner join
preferences p on g.preferenceId = p.preferenceId where groupId = 13
and description = 'myDescription'), 1))

set @override = (coalesce((select override from groupPreferences g inner join
preferences p on g.preferenceId = p.preferenceId where groupId = 13
and description = 'myDescription'), 1))

回复收藏 0 原文

冷︶言冷语的世界 2024-09-23 01:00:56

我会选择这样可读的东西：

select @override = override 
from groupPreferences g 
    inner join preferences p on g.preferenceId = p.preferenceId 
where groupId = 13
    and description = 'myDescription'

SET @override = ISNULL(@override, 1)

但你可以这样做：

SELECT @override = ISNULL((select override 
from groupPreferences g 
    inner join preferences p on g.preferenceId = p.preferenceId 
where groupId = 13
    and description = 'myDescription'), 1)

I'd go for something readable like this:

select @override = override 
from groupPreferences g 
    inner join preferences p on g.preferenceId = p.preferenceId 
where groupId = 13
    and description = 'myDescription'

SET @override = ISNULL(@override, 1)

But you can do:

SELECT @override = ISNULL((select override 
from groupPreferences g 
    inner join preferences p on g.preferenceId = p.preferenceId 
where groupId = 13
    and description = 'myDescription'), 1)

回复收藏 0 原文