是否有一种方法可以通过在SQL Server中使用SQL参数来指定临时表中的列长度?

时间:2022-09-23 22:50:30

Imagine the following temp table is being created in SQL Server:

假设SQL Server中正在创建下面的临时表:

CREATE TABLE #SomeTable
(
    SomeColumn varchar(50)
)

Now imagine there is some VB.NET code creating this table and is also specifying the length of SomeColumn dynamically, in order to keep it synchronized with something else:

现在假设有一些VB。创建此表的NET代码,并动态指定SomeColumn的长度,以便与其他内容保持同步:

command.CommandText = $"
        CREATE TABLE #SomeTable(
           SomeColumn varchar({If(syncIntValue.HasValue, CStr(syncIntValue.Value), "MAX")})
        )"

There's a problem with this approach: it isn't using SQL parameters, and if OS settings are altered with, you could get some undesired behavior when it converts that integer to a string.

这种方法有一个问题:它没有使用SQL参数,如果修改了OS设置,那么当它将该整数转换为字符串时,您可能会得到一些不希望的行为。

Is there any way to get this to use SQL parameters? When pulling these queries out in SSMS, neither is syntactically valid:

有没有办法让它使用SQL参数?当在ssm中提取这些查询时,语法上也不正确:

declare @sizeParamName int = 5
CREATE TABLE #SomeTable(SomeColumn varchar(@sizeParamName));

---

declare @sizeParamName int = 5
CREATE TABLE #SomeTable(SomeColumn varchar((@sizeParamName)));

---

declare @sizeParamName int = 5
CREATE TABLE #SomeTable(SomeColumn varchar(select 5));

---

declare @sizeParamName int = 5
CREATE TABLE #SomeTable(SomeColumn varchar((select 5)));

That's not a good sign about the use of the SQL parameters (though not a definite), and neither is the fact that Googling this brings up other types of issues instead (such as people trying to set lengths on the parameters themselves, not on any columns).

这并不是使用SQL参数的好迹象(虽然不是确定的),google这也不会带来其他类型的问题(比如人们试图在参数本身上设置长度,而不是在任何列上)。

In the past, I've usually worked without using temp tables a whole lot, so there could be something I'm missing here. Is there a good way to do this? Is there a good way to pass a SQL parameter from C# or VB.NET to a query that is used to specify the length of a temp table?

在过去,我通常不经常使用临时表,所以这里可能缺少一些东西。有什么好办法吗?从c#或VB中传递SQL参数有什么好方法吗?NET到用于指定临时表长度的查询?


In this specific case, the reason this is being done is that there is a column inside a regular table within the database that is also varchar with a specific length, and I am wanting to use this to synchronize SomeColumn's length with that other column's length. Sometimes you can query meta-information like that from SQL Server, but in this particular case, I have mixed feelings about which way to go; and given the syntax issues above, it looks like that may also be tricky to inject into SomeColumn. Google had similar issues when trying to find a good way to synchronize column widths like that.

在这个特定的例子中,这样做的原因是在数据库中的一个常规表中有一个列,它也是具有特定长度的varchar,我希望使用这个来同步SomeColumn的长度和另一个列的长度。有时你可以从SQL Server中查询这样的元信息,但是在这个特殊的例子中,我对该怎么做有着复杂的感觉;考虑到上面的语法问题,似乎将其注入到SomeColumn中也很棘手。谷歌在试图找到一种同步列宽度的好方法时遇到了类似的问题。

1 个解决方案

#1


3  

Something like the following should work...

像下面这样的东西应该会起作用……

DECLARE @sizeParameter INT = 5;

-- start by creating the temp table as normal.
-- use any length you want as the default VARCHAR property.
IF OBJECT_ID('tempdb..#SomeTable', 'U') IS NOT NULL 
DROP TABLE #SomeTable;

CREATE TABLE #SomeTable (
    SomeStringColumn VARCHAR(1) NOT null 
    );

-- use dynamic sql to ALTER the table column based on the parameter value.
DECLARE @alter NVARCHAR(4000) = CONCAT(N'
ALTER TABLE #SomeTable ALTER COLUMN SomeStringColumn VARCHAR(', @sizeParameter, ');');

EXEC sys.sp_executesql @alter;

-- verify that the varchar(is now sized properly.
EXEC tempdb..sp_help #SomeTable;

Result...

结果……

Column_name       Type     Computed  Length      Prec  Scale Nullable  TrimTrailingBlanks  FixedLenNullInSource  Collation
----------------- -------- --------- ----------- ----- ----- --------- ------------------- --------------------- -----------------------------
SomeStringColumn  varchar  no        5                       yes       no                  yes                   SQL_Latin1_General_CP1_CI_AS

#1


3  

Something like the following should work...

像下面这样的东西应该会起作用……

DECLARE @sizeParameter INT = 5;

-- start by creating the temp table as normal.
-- use any length you want as the default VARCHAR property.
IF OBJECT_ID('tempdb..#SomeTable', 'U') IS NOT NULL 
DROP TABLE #SomeTable;

CREATE TABLE #SomeTable (
    SomeStringColumn VARCHAR(1) NOT null 
    );

-- use dynamic sql to ALTER the table column based on the parameter value.
DECLARE @alter NVARCHAR(4000) = CONCAT(N'
ALTER TABLE #SomeTable ALTER COLUMN SomeStringColumn VARCHAR(', @sizeParameter, ');');

EXEC sys.sp_executesql @alter;

-- verify that the varchar(is now sized properly.
EXEC tempdb..sp_help #SomeTable;

Result...

结果……

Column_name       Type     Computed  Length      Prec  Scale Nullable  TrimTrailingBlanks  FixedLenNullInSource  Collation
----------------- -------- --------- ----------- ----- ----- --------- ------------------- --------------------- -----------------------------
SomeStringColumn  varchar  no        5                       yes       no                  yes                   SQL_Latin1_General_CP1_CI_AS