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

Can you please confirm that you are looking for the row(s) in the education table where the eduChildName column contains the string value 'Revathi' and not the row(s) where the eduChildName column contains the same value as the Revathi column? — Aaron Bertrand
– Aaron Bertrand, Commented Jul 26, 2012 at 20:39
I deleted my answer, as it was pulling too much downvotes for some reason. Sorry baji, you are on your own with the other answers that you got, even if they are not so helpful. — Guffa
– Guffa, Commented Jul 26, 2012 at 23:29

Aaron Bertrand · Accepted Answer · 2012-09-19 20:37:16Z

15

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

edited Sep 19, 2012 at 20:37

answered Jul 26, 2012 at 12:44

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Madhivanan Over a year ago

Aaron, I am sure Quotename will work 100% unless there are object names with more than 128 characters (Which is rare to have). I just want to point out this :)

Aaron Bertrand Over a year ago

@Madhivanan please let me know how you can create an object with a name > 128 characters (I get an error). QUOTENAME was written explicitly to deal with object names and the understanding that an identifier cannot exceed 128 characters.

Madhivanan Over a year ago

You are right.I misunderstood that. I have seen people using Quotename in dynamic sql over not only on object names but on values too to put single quotes around it. Like Quotename(@v,''''). When I posted I kept that in mind but wrongly specified the object names

Guffa Over a year ago

-1 You are assuming things about the question, and downvoting other answers because don't ASSUME THE SAME THING.

Andriy M Over a year ago

Prefixing objects with dbo is a good idea, as is using QUOTENAME for delimiting names. But if you are applying QUOTENAME to a parameter like that, you shouldn't pass the name with a prefix, or you'll end up with a wrong object name.

|

Madhivanan · Accepted Answer · 2012-07-26 12:53:14Z

-3

Why are you passing object names as parameters?

If you pass string value to @valuesname, your code should be

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

Don't know how to use single quotes in dynamic sql? Refer this http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

answered Jul 26, 2012 at 12:53

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

sql stored procedure argument as parameter for dynamic query

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related