Article: Using Stored Procedures instead of Dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Using Stored Procedures instead of Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-27 : 17:15:08

Applications that access SQL Server databases should make extensive if not exclusive use of stored procedures for any statement, from a very simple one-row select to a complex cascaded delete. Applications that paste together SQL statements are more difficult to manage and result in less secure databases than applications that exclusively use stored procedures.

Article Link.

anilbisht
Starting Member

1 Post

Posted - 2002-01-21 : 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

289 Posts

Posted - 2002-01-21 : 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

1408 Posts

Posted - 2002-01-21 : 09:35:07

Nopes, it doesnt check this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10964

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

TorreyKite
Starting Member

40 Posts

Posted - 2004-08-02 : 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

TorreyKite
Starting Member

40 Posts

Posted - 2004-08-02 : 15:56:50

LOL
i didn't realize how old this post was...oops

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources