Geek asks and answers

Sql argument to stored procedure as parameter for dynamic query

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

+5
source share
2 answers

Here is a 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 have I changed?

  • Always use the prefix dbowhen creating / referencing objects.
  • The names of tables and columns NVARCHARcan be longer than 150 characters. It is much safer to allow parameters to place a table that someone can add in the future.
  • SET NOCOUNT ON .
  • @sql NVARCHAR.
  • QUOTENAME , , SQL-, (, ).
  • (-, SQL-, ).
+11

?

@valuesname,

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

, sql? http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

-3

More articles:


All Articles