SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL or Stored Procedure


Dynamic SQL or Stored Procedure

Author
Message
Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: Moderators
Points: 37226 Visits: 2762
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/dynamicsqlorstoredprocedure.asp>http://www.sqlservercentral.com/columnists/awarren/dynamicsqlorstoredprocedure.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
zspitzer
zspitzer
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1
what about bind paramertised queries? best of both worlds?



matt1
matt1
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 1
what are the benefits of a compiled query plan?



ccubley
ccubley
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1
As a developer, the biggest benefit I get from using stored procedures is ease of development. By using stored procs exclusively, all applications have a well-defined interface to the database. Complex queries can be written by developers with more SQL experience and used by those with less experience. SPs also provide a degree of modularity and code reuse in applications. I've found that maintainance is decreased because its easier to do component-level testing and optimization for stored procs than it is for client-generated dynamic SQL. Any added performance is just an added bonus for me. Development time (up-front and down the road) is more costly than buying a beefier server.

It is true that sometimes dynamic SQL is necessary. When this is the case, I still try to encapsulate this within a stored procedure so that I get at least some of the benefits of testing and modularity.


Chris Cubley, MCSD, MCDBA
www.queryplan.com



RRadcliff3
RRadcliff3
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1431 Visits: 64
quote:

what are the benefits of a compiled query plan?



Faster execution. With a compiled query plan, SQL doesn't have to figure out the best way to execute the code, it reuses an old plan.



Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: Moderators
Points: 37226 Visits: 2762
Bind queries - as in true ODBC binding?

Thank you all for your comments so far. Yes, faster execution with a compiled query plan - usually only makes a difference if the procedure gets called frequently, otherwise there is the chance the query plan will get aged out of cache.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Bodhi Densmore
Bodhi Densmore
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 50
Thank you for informing us that sp_executesql parameters may be OUTPUT parameters. I didn't find that anywhere in BOL.

Here is an example:
USE pubs
declare @sql nvarchar(3000)
declare @d datetime
set @sql = N'
-- compute the same or next date that is in Sales table.
SET NOCOUNT ON
declare @o_date datetime, @p_date datetime
set @p_date = convert(datetime,convert(varchar(10),@date, 120)) -- chop off hours

select @o_date = min(ord_date) from Sales where ord_date >= @P_date
if @@rowcount = 0
select @o_date = max(ord_date) from Sales where ord_date < @P_date

if @o_date is not null
set @next_date = @o_date
else
set @next_date = @p_date
'
exec dbo.sp_executesql @sql
, N'@date datetime, @next_date datetime OUT'
, '1993-05-25', @next_date = @d OUT
print @d



SIGSAUERP22045
SIGSAUERP22045
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Often we find ourselves with search screens that allow the user to enter one or more search criteria. Since we can't predict what parameters they'll pass we build the SQL statement in the stored procedure and dynamically build the WHERE clause based on what they pass in. We then use sp_executesql to run the procedure. The procedure itself has as at least as many params as their are search fields and they're all set to NULL so they're optional.

We've found this method to be very efficient.


Steven M. King
Systems Consultant IT

Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: Moderators
Points: 37226 Visits: 2762
Why do you consider that a better approach than building the statement in the app?

Nice screen name, though Im not a huge fan of SIG!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
RyanNerd
RyanNerd
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 2
Nice article that I plan on using when someone looks at me with a blank stare when I try to explain the difference between static and dynamic queries.

The application I work on is mostly dynamic SQL. I am one of 5 developers that work on this application. When static SQL has been used most of the stored procedures are mine (this is because these are often used querys that populate grids and I was looking for ways of increasing performance). When I asked why the other developers did not use stored procedures more they all pretty much said the same thing: It's a pain to leave the development IDE and launch Enterprise Manager or SQL Analizer and write up the query and save it as a stored procedure, set the permissions, etc. It's much easier to just write the code in the development IDE (where you are in the first place). The other complaint is that often it is not worth the time and trouble of getting permission from the DBA or overcoming other buracratic policies just to write a simple query. Also, I think that many of the developers feel that because they don't really know T-SQL very well or at all and really would not care to learn it since dynamic SQL has worked just fine so far...

My 2 cents.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search