Search code examples
.netsql-serversqlparameter

Is there a way to specify the column length in a temp table through the use of a SQL parameter in SQL Server?

Imagine the following temp table is being created in 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:

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.

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

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).

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?

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.

Solution

  • 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