SQL 变量；当参数不存在时声明

发布于 2024-09-30 13:53:10 字数 407 浏览 0 评论 0原文

我正在编写一个 sql 脚本，我想使用 Management Studio 来开发查询，并使用 C# 程序在生产中运行它。

我的查询包含参数，如下所示；

SELECT * FROM TABLE
WHERE id = @id

我可以在 C# 程序中输入 @id 的值，效果很好。但是，我还想声明在 Management Studio 中进行测试的默认值。所以我真的很想写一些像这样的伪代码；

if not declared @id
  declare @id int
  set @id=43
end if

SELECT * FROM TABLE   
WHERE id = @id

有什么方法可以检查变量名是否已被占用？

原文

I'm writing a sql script, and I'd like to use Management Studio to develop the query, and a C# program to run it in production.

My query contains parameters, like so;

SELECT * FROM TABLE
WHERE id = @id

I can feed in a value for @id in the C# program, and that works nicely. However, I also want to declare default values for testing in Management Studio. So I really want to write something like this pseudocode;

if not declared @id
  declare @id int
  set @id=43
end if

SELECT * FROM TABLE   
WHERE id = @id

Is there any way to check to see if a variable name has already been taken?

分享到QQ

分享到微博

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

发布评论

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

夜司空 2024-10-07 13:53:10

你无法完全按照你所追求的去做。我建议：

1）将脚本包装为存储过程并给出参数的默认值
2) 在脚本顶部包含一个注释块，然后您可以在 SSMS 中运行时取消注释：

/*
-- Default variables for testing
DECLARE @Id INTEGER
SET @Id = 43
*/
SELECT * FROM Table WHERE id = @Id

You can't do exactly what you're after. I'd suggest either:

1) wrap the script up as a sproc and give defaults for the params
2) include a comment block at the top of the script that you can then uncomment when running in SSMS:

/*
-- Default variables for testing
DECLARE @Id INTEGER
SET @Id = 43
*/
SELECT * FROM Table WHERE id = @Id

回复收藏 0 原文

温柔嚣张 2024-10-07 13:53:10

我通过在脚本中标记默认变量来取得一些进展，如下所示；

/** DEFAULTS **/

declare @id int
set @id = 43

/** END DEFAULTS **/

然后在我的 C# 程序中预处理脚本，如下所示；

script = RemoveBlock(script, "DEFAULTS");

并像这样实现功能；

public static string RemoveBlock(string script, string blockName)
{
    if (script == null) { return null; }

    var startTag = string.Format("/** {0} **/", blockName);
    var endTag = string.Format("/** END {0} **/", blockName);

    var startTagIdx = script.IndexOf(startTag);
    if (startTagIdx == -1) { return script; }

    var endTagIdx = script.IndexOf(endTag, startTagIdx + startTag.Length);
    if (endTagIdx == -1) { return script; }

    var endOfEndTag = endTagIdx + endTag.Length;

    var beforeBlock = script.Substring(0, startTagIdx);
    var afterBlock = script.Substring(endOfEndTag);

    return beforeBlock + afterBlock;
}

因此，C# 程序运行一个不带变量但带参数的版本。

I've managed to make some progress by marking out the default variables in the script, like so;

/** DEFAULTS **/

declare @id int
set @id = 43

/** END DEFAULTS **/

Then preprocessing the script in my C# program, like so;

script = RemoveBlock(script, "DEFAULTS");

And implementing the function like so;

public static string RemoveBlock(string script, string blockName)
{
    if (script == null) { return null; }

    var startTag = string.Format("/** {0} **/", blockName);
    var endTag = string.Format("/** END {0} **/", blockName);

    var startTagIdx = script.IndexOf(startTag);
    if (startTagIdx == -1) { return script; }

    var endTagIdx = script.IndexOf(endTag, startTagIdx + startTag.Length);
    if (endTagIdx == -1) { return script; }

    var endOfEndTag = endTagIdx + endTag.Length;

    var beforeBlock = script.Substring(0, startTagIdx);
    var afterBlock = script.Substring(endOfEndTag);

    return beforeBlock + afterBlock;
}

So the C# program runs a version without the variables but with parameters.

回复收藏 0 原文

~没有更多了~