0

Based on this post: How should I pass a table name into a stored proc?

i wrote a procedure to accept a table name, column name and value of the column to check if the passed value exists in the passed table and column.

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

but the print statement return:

SELECT COUNT(*) FROM [AppRoles] WHERE [Name] = Admin

instead of

SELECT COUNT(*) FROM [AppRoles] WHERE [Name] = 'Admin'

how can i edit this line below to return the normal select statement.

SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' WHERE ' + @ActualColumnName +' = ' + @PassedValue
Community
  • 1
  • 1
Djama
  • 661
  • 5
  • 11
  • 28

1 Answers1

2

You can use:

SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' WHERE ' + @ActualColumnName +' = ''' + @PassedValue + ''''
tezzo
  • 10,858
  • 1
  • 25
  • 48