SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Stored Procedures instead of Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/27/2000 :  17:15:08  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 11/29/2000 :  18:03:13  Show Profile  Reply with Quote
a technicality...

well since your procedure can only delete a single record I question the use of the plural in it's name 8~P

jokes aside, I agree with you and also we should not overlook the performance benefit of having the SQL precompiled within the procedure.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/03/2000 :  22:55:48  Show Profile  Reply with Quote
RE: Using Stored Procedures instead of Dynamic SQL

Very good article.
I have a rule that when I'm writing applications I do the following
1. All deletes, inserts and updates are ALWAYS done thru a stored procedure.
2. I use views to do selects against for data display and the view I use has only the data I need in it.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/08/2000 :  16:39:34  Show Profile  Reply with Quote
Always Stored Procedures

We are currently delevoping a web application using ASP and everything is controlled from the stored procedures. It's makes developing and modifying SQL so much easier.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 01/23/2001 :  11:15:48  Show Profile  Reply with Quote
VB call

How does one call the SP results when using VB as a client?

Go to Top of Page

anilbisht
Starting Member

1 Posts

Posted - 01/21/2002 :  09:08:22  Show Profile  Reply with Quote
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

Go to Top of Page

penumatsa
Constraint Violating Yak Guru

USA
289 Posts

Posted - 01/21/2002 :  09:31:48  Show Profile  Visit penumatsa's Homepage  Reply with Quote
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
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 01/21/2002 :  09:35:07  Show Profile  Reply with Quote
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
Go to Top of Page

TorreyKite
Starting Member

USA
40 Posts

Posted - 08/02/2004 :  14:43:11  Show Profile  Reply with Quote
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
Go to Top of Page

TorreyKite
Starting Member

USA
40 Posts

Posted - 08/02/2004 :  15:56:50  Show Profile  Reply with Quote
LOL
i didn't realize how old this post was...oops
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000