| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
anilbisht
Starting Member
1 Posts |
 Posted - 01/21/2002 : 09:08:22
|
hi,
does writing dynamic sql affect performance.
because Stored procedure are precompiled and they create the query plan for the query. Does it create the query plan for dynamic SQL also ??
I write a stored procedure like this
create procedure test
@age numeric(10)
@order varchar(1)
as
if @Order = "+"
begin
select *
from tab1
where age = @age
order by age acc
end
else if @order = "-"
begin
select *
from tab1
where age = @age
order by age desc
end
and other using the dynamic SQL
create procedure test1
@age numeric(10)
@order varchar(10)
as
declare
@sql varchar(250)
set @sql = "select * from tab1 where age = @age order by age"
if @Order = "+"
begin
set @orderby = "acc"
end
else if @order = "-"
begin
set @orderby = "desc"
end
set @sql = @sql + @orderby
EXECUTE sp_executesql @sql
will test have better performance than test1 ??
TIA
Anil
|
 |
|
|
penumatsa
Constraint Violating Yak Guru
USA
289 Posts |
Posted - 01/21/2002 : 09:31:48
|
It all depends upon the way you write the query. I would prefer a stored procedure rather than a dyanamic query. When you write a dynamic query, i dont think it creates a query plan and so affects the performance of the system.
suresh penumatsa |
|
|
|
Nazim
A custom title
United Arab Emirates
1408 Posts |
|
|
TorreyKite
Starting Member
USA
40 Posts |
Posted - 08/02/2004 : 14:43:11
|
I agree with the weakness in security of Dynamic SQL. Especially after watching some very impresive demonstrations of SQL injection at the MS security summit.
With that in mind, what is the best way to delete, insert and update?
when updating one field in one table, does one need to create an SP for each field?
or is there some way to designate the field to update and pass the new value.
How would one replace the following
--------------------
CREATE PROCEDURE sp_Save_Data
@Table_Name as varchar(20),
@Field_Name as varchar(20),
@Key_Field as varchar(20),
@Key_Value as varchar(100),
@New_Value as varchar(100)
AS
Set nocount on
Declare @SQL as varchar(7000)
Select @sql =''
Select @sql = @sql + 'Update ' + @Table_Name
Select @sql = @sql + ' Set ' + @Field_Name + ' = "' + @New_Value + '"'
Select @sql = @sql + ' Where ' + @Key_Field + ' = "' + @Key_Value +'"'
exec (@sql)
Return
------------------
would i need to create a separate SP for each possible combination?
thanks for any advice!
TK 
|
Edited by - TorreyKite on 08/02/2004 14:44:16 |
|
|
|
TorreyKite
Starting Member
USA
40 Posts |
Posted - 08/02/2004 : 15:56:50
|
LOL
i didn't realize how old this post was...oops  |
|
|
| |
Topic |
|
Article: Using Stored Procedures instead of Dynamic SQL
