Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Dynamic SQL or Stored Procedure Expand / Collapse
Author
Message
Posted Friday, April 18, 2003 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #11504
Posted Monday, April 21, 2003 3:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2003 12:00 AM
Points: 3, Visits: 1
what about bind paramertised queries? best of both worlds?




Post #59783
Posted Monday, April 21, 2003 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 3, 2007 8:46 AM
Points: 100, Visits: 1
what are the benefits of a compiled query plan?




Post #59784
Posted Monday, April 21, 2003 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2003 12:00 AM
Points: 3, 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




Post #59785
Posted Monday, April 21, 2003 8:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 9:09 AM
Points: 1,405, Visits: 58
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.




Post #59786
Posted Monday, April 21, 2003 9:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #59787
Posted Monday, April 21, 2003 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 1:45 PM
Points: 17, 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





Post #59788
Posted Monday, April 21, 2003 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2003 12:00 AM
Points: 1, 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
Post #59789
Posted Monday, April 21, 2003 10:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, November 17, 2014 2:20 PM
Points: 6,800, Visits: 1,914
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
Post #59790
Posted Monday, April 21, 2003 1:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 6:34 PM
Points: 28, 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.




Post #59791
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse