基于本文:如何将表名传递到存储的proc中?
我编写了一个过程来接受表名,列名和列的值,以检查所传递的值是否存在于所传递的表和列中。
CREATE PROC spCountAnyTableRows
(
@PassedTableName as NVarchar(255),
@PassedColumnName as NVarchar(255),
@PassedValue as NVarchar(255)
)
AS
BEGIN
DECLARE @ActualTableName AS NVarchar(255)
DECLARE @ActualColumnName AS NVarchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName
SELECT @ActualColumnName = QUOTENAME( COLUMN_NAME )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = @PassedColumnName
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' WHERE ' + @ActualColumnName +' = ' + @PassedValue
PRINT @sql
EXEC(@SQL)
END
但print语句返回:
SELECT COUNT(*) FROM [AppRoles] WHERE [Name] = Admin
代替
SELECT COUNT(*) FROM [AppRoles] WHERE [Name] = 'Admin'
我如何在下面编辑此行以返回正常的select语句。
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' WHERE ' + @ActualColumnName +' = ' + @PassedValue
您可以使用:
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' WHERE ' + @ActualColumnName +' = ''' + @PassedValue + ''''
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句