Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When to Use Dynamic SQL


When to Use Dynamic SQL

Author
Message
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7223 Visits: 2679
I removed the duplicate postings you mentioned.

Why would your procs change so often? And in what way do they change?


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
Thanks for removing the duplicate posts!

Our stored procedures change so often because we are always adding new features. For example we will soon be modifying a feature so that instead of only being able to select one state or one country per search you will be able to select multiple states and/or multiple countries. This change will effect about 40 to 60 stored procedures.

We are competing in a niche market and must continue to add value for our customers or risk loosing them to someone else who is adding more value than we are.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7223 Visits: 2679
I hear you!

My only thought on this is that most companies tend to fall into the same trap, adding functionality over time. It makes sense to trickle out changes rather than do "big" upgrades. What we try to do is identify places where we expect to add functionality and try to plan so that we don't have to make a lot of changes later to fit that added piece in. Not simple and not always successful, but every time you win it helps.

Im curious though as to why it would affect 40+ procs? Seems like some very tight coupling?


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
nigelrivett
nigelrivett
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 211
I use dynamic sql for converting hex

      set   @sql = 'set @bin = 0x' + @char
      set   @parm = '@bin varbinary(' + convert(varchar(10),len(@char)/2) + ') output'
      exec sp_executesql @sql, @parm, @bin output

And for getting the output buffer.
Also writing a scheduler to give more flexible dependencies - you need to use dynamic sql to call stored procedures which are held in the dependency table (sp_executesql to get the output parameters back).

I have used it on systems which have searches on a lot of different parameters and different numbers of parameters as you can build up the query by adding new features rather than having to do a lot of checking of values - as these systems temd to have ever changing requirements.


Cursors never.
DTS - only when needed and never to control.

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7223 Visits: 2679
Seems like a function would work for that?


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
We have stored procedures that hit different regions in our database and when a large change comes along it requires changes to many of them. It would be fewer, but we have been converting stored procedures from dynamic SQL to static SQL and usually this means 1 stored procedure becomes more than 1. The major change was when 1 became 22.

I wish we could plan ahead for the changes, but often we don't know until a month or 2 before it goes live. Sometimes we have a 3 or 4 month warning, but that is rare.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7223 Visits: 2679
Different regions meaning different parts of a table?


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
sorry, meaning different databases with distinct but related purposes. One is our data on 13F filings (stocks), one is for our N30D filings (mutual funds), one for staff that buy stocks and manage mutual funds, one for staff that sell stocks. Analysts are in there somewhere too. And a few other databases.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

acotgreave
acotgreave
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Does anyone have any problems printing this article out? I'm trying to print it using IE6, but with no joy. No print job is sent to the printer. Other articles from this site print fine. Strange!



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36062 Visits: 18736
No issues here. IE6.0.26

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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