sql stored procedure argument as parameter for dynamic query

Question

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".

Answer 1

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?

1. Always use dbo prefix when creating / referencing objects.
2. 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.
3. Added SET NOCOUNT ON as a guard against network overhead and potentially sending erroneous result sets to client.
4. @sql should always be NVARCHAR.
5. 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).
6. 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).