34,576
社区成员
发帖
与我相关
我的任务
分享
SET @str = 'select @real='+@TextColumnName+' from ' + @TableName + ' where '+@ValueColumnName+'=@Value'
EXEC sp_executesql @str,
N'@real nvarchar(255) output,@Value varchar(255)',
@real OUTPUT,
@Value
USE [tempdb]
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
n NVARCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO t(id,n) VALUES (1,'123');
INSERT INTO t(id,n) VALUES (2,'''''');
GO
--------- 以上为测试表,测试数据 ---------
DECLARE @str NVARCHAR(MAX)
,@tableName NVARCHAR(MAX)
,@ValueColumnName NVARCHAR(MAX)
,@Value NVARCHAR(MAX)
,@real NVARCHAR(MAX)
,@TextColumnName NVARCHAR(MAX)
SET @tableName='t'
SET @TextColumnName='id'
SET @ValueColumnName='n'
SET @Value='''''' --赋值
--所有数据库对象,查询不允许用变量代替,包括 数据库名、表名、字段名
SET @str = 'select @real='+@TextColumnName+' from ' + @TableName + ' where '+@ValueColumnName+'=@Value'
EXEC sp_executesql @str,
N'@real nvarchar(255) output,@TextColumnName varchar(255),@ValueColumnName varchar(255),@Value varchar(255)',
@real OUTPUT,
@TextColumnName,
@ValueColumnName,
@Value
SELECT @real AS r
/*
r
------
2
*/
USE [tempdb]
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT PRIMARY KEY,
n NVARCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO t(id,n) VALUES (1,'123');
INSERT INTO t(id,n) VALUES (2,'''''');
GO
--------- 以上为测试表,测试数据 ---------
DECLARE @str NVARCHAR(MAX)
,@tableName NVARCHAR(MAX)
,@ValueColumnName NVARCHAR(MAX)
,@Value NVARCHAR(MAX)
,@real NVARCHAR(MAX)
,@TextColumnName NVARCHAR(MAX)
SET @tableName='t'
SET @TextColumnName='id'
SET @ValueColumnName='n'
SET @Value='''''' --赋值
--所有数据库对象,查询不允许用变量代替,包括 数据库名、表名、字段名
SET @str = 'select @real='+@TextColumnName+' from ' + @TableName + ' where '+@ValueColumnName+'=@Value'
EXEC sp_executesql @str,
N'@real nvarchar(255) output,@TextColumnName varchar(255),@ValueColumnName varchar(255),@Value varchar(255)',
@real OUTPUT,
@TextColumnName,
@ValueColumnName,
@Value
SELECT @real AS r
/*
r
------
2
*/
USE [master]
GO
DECLARE @str NVARCHAR(MAX)
,@tableName NVARCHAR(MAX)
,@ValueColumnName NVARCHAR(MAX)
,@Value NVARCHAR(MAX)
,@real NVARCHAR(MAX)
,@TextColumnName NVARCHAR(MAX)
SET @tableName='dbo.spt_values'
SET @TextColumnName='number'
SET @ValueColumnName='name'
SET @Value=''''
--所有数据库对象,查询不允许用变量代替,包括 数据库名、表名、字段名
SET @str = 'select @real='+@TextColumnName+' from ' + @TableName + ' where '+@ValueColumnName+'=@Value'
EXEC sp_executesql @str,
N'@real nvarchar(255) output,@TextColumnName varchar(255),@ValueColumnName varchar(255),@Value varchar(255)',
@real OUTPUT,
@TextColumnName,
@ValueColumnName,
@Value
SELECT @real