October 21, 2010 at 7:41 am
I have a procedure which is->
============================================================
create procedure procedure1(@city varchar(20),@department varchar(20),@name varchar(20))
as
begin
declare @a varchar(50);
declare @b-2 varchar(50);
declare @C varchar(50);
declare @d varchar(500);
if @city=''
set @a='';
else
set @a='where emp_city='+@city;
if @department=''
set @b-2='';
else
set @b-2='and emp_department='+@department;
if @name=''
set @C='';
else
set @C='and emp_name='+@name;
set @d='select * from employees where'+@a+@b+@c;
---???????????????????????????WHAT TO DO TO EXECUTE @d?
return
end;
===========================================================
Now in the procedure, @d holds a select query but it is in varchar format. I cannot execute that as a query in the procedure.
How to execute the query stored in @d?
pls help.
October 21, 2010 at 7:50 am
exec (@d) - note the brackets
October 21, 2010 at 7:51 am
another way:
EXEC SP_EXECUTESQL @d
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 21, 2010 at 8:01 am
Why not just do it like this:
SELECT * FROM employees
WHERE emp_city = @city
AND emp_department = @department
AND emp_name = @name
I recommend that you read this:
http://www.sommarskog.se/dynamic_sql.html
John
October 21, 2010 at 8:03 am
Beat me to it John, lol 🙂
October 21, 2010 at 8:10 am
This whole thing just isn't going to work as it sits.
If city <> '' your query will be "select * from employees where where city = 'city'"
if city = '' you query will be "select * from employees where and emp_department= '+ @department"
if all three = '' your query will "select * from employees where"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2010 at 8:25 am
subhankar02dey
First I altered your T-SQL code:
set @d='select * from employees where'+@a+@b+@c;
PRINT @d -- added
---???????????????????????????WHAT TO DO TO EXECUTE @d?
return
Then executed the resulting SP as procedure1 'NY','ABC','SAM' with the following results:
select * from employees wherewhere
emp_city=NYand emp_department=ABCand
emp_name=SAM
I would respectfully suggest that before attempting to EXECUTE your procedure you check the format of the command produced.
October 21, 2010 at 8:36 am
I changed this up and made it so that it can easily handle a dynamic number of where conditions..
create procedure procedure1
(
@city varchar(20),
@department varchar(20),
@name varchar(20)
) as
begin
declare @sql varchar(500);
set @sql = 'select * from employees where 1 = 1 '
if @city <> ''
set @sql = @sql + 'and emp_city = ' + @city;
if @department <> ''
set @sql = @sql + 'and emp_department = ' + @department;
if @name <> ''
set @sql = @sql + 'and emp_name = ' + @name;
exec SP_EXECUTESQL @sql
end
I would highly recommend you follow the suggested links above. Read them and understand what they are saying. Keep in mind this code I wrote is untested and HIGHLY vulnerable to sql injection. I would not suggest using this as is. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2010 at 12:31 pm
SeanLange (10/21/2010)
I would highly recommend you follow the suggested links above. Read them and understand what they are saying. Keep in mind this code I wrote is untested and HIGHLY vulnerable to sql injection. I would not suggest using this as is. 😉
Gosh, Sean... you shouldn't post the code if it's so bloody dangerous (and, it IS dangerous). Can't you post the correct method for doing this instead?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2010 at 12:34 am
Here is how to do it where you do not have the SQL injection problem and you get the plan-reuse features of sp_executesql. There is also proper semicolon usage for future versions of SQL Server.
create table dbo.employees (emp_city varchar(20), emp_department varchar(20), emp_name varchar(20))
go
create procedure dbo.procedure1
@city varchar(20),
@department varchar(20),
@name varchar(20)
as
declare @sStr nvarchar(1000);
declare @pstr nvarchar(1000);
declare @conj nvarchar(1000) = N'where ';
set @sStr = N'
select *
from dbo.employees;';
if (@city > '') begin
set @sStr = REPLACE(@sStr, N';', N'
' + @conj + N' emp_city = @city;');
set @conj = case @conj when N'where ' then N'and ' else @conj end;
end;
if (@department > '') begin
set @sStr = REPLACE(@sStr, N';', N'
' + @conj + N' emp_department = @department;');
set @conj = case @conj when N'where ' then N'and ' else @conj end;
end;
if (@name > '') begin
set @sStr = REPLACE(@sStr, N';', N'
' + @conj + N' emp_name = @name;');
set @conj = case @conj when N'where ' then N'and ' else @conj end;
end;
set @pstr = N'
@city varchar(20),
@department varchar(20),
@name varchar(20)
';
-- Remove print after debugging
print @sStr + N'
';
exec sp_executesql
@stmt = @sStr,
@city = @city,
@department = @department,
@name = @name
;
GO
exec procedure1
@city = '',
@department = '',
@name = ''
;
exec procedure1
@city = 'asdf',
@department = '',
@name = ''
;
exec procedure1
@city = '',
@department = 'asdf',
@name = ''
;
exec procedure1
@city = '',
@department = '',
@name = 'asdf'
;
exec procedure1
@city = 'asdf',
@department = 'asdf',
@name = ''
;
exec procedure1
@city = 'asdf',
@department = '',
@name = 'asdf'
;
exec procedure1
@city = '',
@department = 'asdf',
@name = 'asdf'
;
exec procedure1
@city = 'asdf',
@department = 'asdf',
@name = 'asdf'
;
GO
drop procedure dbo.procedure1;
drop table dbo.employees;
GO
Sincerely,
Daniel
October 25, 2010 at 7:37 am
Come to think of it, why use dynamic SQL at all? You can simply pass the variables to a standard select query with the use of a "like" predicate, without degrading performance.
Using the following example, the execution plan estimates a 49/51% split for the two methods given (with the 'like' query slightly more expensive), probably as a result of the seek predicate changing from "= value" to ">= value and <= value". But, on execution, the read counts are the same. (Note that this method works only because we pass an explicit value to the like predicate, without any % bracketing to get in the way of an index seek).
If you have a properly clustered index on the table, covering all of the searched fields, this method should be at least as fast (if not faster) and avoids the security issue completely. Note that your index should have the most frequently searched column as the clustering key, followed by the second most searched, etc.
Thoughts?
-- create temp table for testing
create table #t (f1 varchar(10), f2 varchar(10), f3 varchar(10))
-- use tally table to populate test data
insert #t
select 'Bob','Sue','Dave'
from Dev_Utils.dbo.Tally_Table
where Tally_Number <=1000
union all
select 'Mike','Claire','Lucy'
from Dev_Utils.dbo.Tally_Table
where Tally_Number <=1000
union all
select 'Sarah','Louise','Iain'
from Dev_Utils.dbo.Tally_Table
where Tally_Number <=1000
-- cluster table, covering all fields <-- important
create clustered index ix_f1 on #t(f1,f2,f3)
--------------------------------------------
-- make sure counts are off
set statistics io off
-- simulate some proc inputs
declare @c1_input varchar(10) = 'Bob'
, @c2_input varchar(10) = null
, @c3_input varchar(10) = null
-- simulate converting proc inputs
declare @c1 varchar(10)
, @c2 varchar(10)
, @c3 varchar(10)
-- set inputs
-- either use the passed in value
-- or use a single %
-- which will return all rows on a like query
set @c1 = isnull(@c1_input, '%')
set @c2 = isnull(@c2_input, '%')
set @c3 = isnull(@c3_input, '%')
-- start counting
set statistics io on
-- clear buffers - DO NOT DO THIS ON A PRODUCTION SERVER
dbcc dropcleanbuffers
dbcc freeproccache
-- select via variable criteria
select * from #t
where f1 like @c1
and f2 like @c2
and f3 like @c3
-- Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
-- clear buffers
dbcc dropcleanbuffers
dbcc freeproccache
-- equivalent passed to dynamic sql using old method
select * from #t
where f1 = 'Bob'
-- Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
set statistics io off
drop table #t
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply