sql stored procedure argument as parameter for dynamic query

sql-server, sql-server-2005, stored-procedures

Solution

Here is a much safer alternative:

ALTER PROCEDURE dbo.queryfunctions 
  @Tabname NVARCHAR(511),
  @colname NVARCHAR(128),
  @valuesname VARCHAR(150)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = 'SELECT * FROM ' + @Tabname 
           + ' WHERE ' + QUOTENAME(@colname) + ' = @v';

  EXEC sp_executesql @sql, N'@v VARCHAR(150)', @valuesname;
END
GO

EXEC dbo.queryfunctions N'dbo.education', N'eduChildName', 'Revathi';

What did I change?

- Always use `dbo` prefix when creating / referencing objects.

- Table and column names are `NVARCHAR` and can be longer than 150 characters. Much safer to allow the parameters to accommodate a table someone might add in the future.

- Added `SET NOCOUNT ON` as a guard against network overhead and potentially sending erroneous result sets to client.

- `@sql` should always be `NVARCHAR`.

- Use `QUOTENAME` around entity names such as tables or columns to help thwart SQL injection and also to guard against poorly chosen names (e.g. keywords).

- Use proper parameters where possible (again to help thwart SQL injection but also to avoid having to do all kinds of escaping of delimiters on string parameters).

Problem

This procedure has three parameters. But when I try to execute by passing parameters it shows me an error. Please help me. ``` create procedure queryfunctions @Tabname varchar(150),@colname varchar(150),@valuesname varchar(150) as begin declare @sql varchar(4000) select @sql='select * from @Tabname where @colname=@valuesname' exec(@sql) end exec queryfunctions 'education','eduChildName','Revathi' ``` Error : Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@Tabname".

Original source