将sql_variant转换为提供为varchar的data_type [英] Cast sql_variant into data_type provided as varchar

问题描述

我有以下sql表：

 Types table

    --------------------------------------
    |Name(varchar(50))|Type (varchar(50))|
    --------------------------------------
    | Car             | varchar(50)      |
    | Apples          | int              |
    --------------------------------------

我正在使用另一个表来存储值，例如：

I am using another tables for storing values such as:

Apples table:

    ----------------------------
    |Value (providedType - int)|
    ----------------------------
    |50                        |
    |60                        |
    ----------------------------

要将值插入这些表中，我正在使用存储过程（其中一部分）：

To insert values into these tables I am using a stored procedure (part of it):

CREATE PROCEDURE [dbo].[AddValue]
@value sql_variant
@name varchar(50)
@tableName (50)
AS
BEGIN

DECLARE @Sql NVARCHAR(MAX)
DECLARE @valueType VARCHAR(50)
SET @valueType = (SELECT [Type] FROM [dbo].[Types] WHERE [Name] = @name)

SET @Sql = N'INSERT INTO [dbo].'+ @tableName + N' VALUES(' + @value + N')'
EXECUTE sp_executesql @Sql 
...

动态执行将抛出一个异常，即不允许隐式转换sql_variant。有什么方法可以将sql_variant类型转换为varchar提供的类型？
例如：

Dynamic execute will throw an exception that implicit casting of sql_variant is not allowed. Is there any way to convert sql_variant type into the type that is provided as varchar? Such as:

CONVERT(@valueType, @value)

@valueType是varchar而不是datetype

Where @valueType is varchar not datetype

推荐答案

是的，您可以将 sql_variants 作为参数传递给 sp_executesql ，但是您需要继续执行以下动态SQL路由强制转换为类型，并使用您为要在 CAST 中使用的列确定的类型的名称。

Yes, you can pass sql_variants as parameters to sp_executesql, but you'll need to continue down the dynamic SQL route with the "Cast to" type, and use the name of the Type that you've determined for the column to be used in a CAST.

例如：

CREATE TABLE Foo
(
    ID INT
);
declare @type NVARCHAR(20) = N'INT'; -- Substitute your Type here.
declare @tableName NVARCHAR(50) = 'Foo';
declare @value sql_variant;
set @value = 1234;
DECLARE @Sql AS NVARCHAR(MAX) = N'INSERT INTO [dbo].'+ @tableName +
           N' VALUES(CAST(@value AS ' + @type + '))';
EXECUTE sp_executesql @Sql, N'@value sql_variant', @value = @value;  

不用说，您需要确保您的 @tableName 和 Type 数据将需要针对白名单运行，以防止像这样的带有动态Sql的Sql Injection漏洞。

Needless to say, you'll need to ensure that your @tableName and Type data will need to be run against a whitelist, in order to protect against Sql Injection vulnerabilities with dynamic Sql like this.

SqlFiddle在这里

这篇关于将sql_variant转换为提供为varchar的data_type的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！