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 ««123»»

When to Use Dynamic SQL Expand / Collapse
Author
Message
Posted Sunday, February 17, 2002 6:55 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
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
Post #27925
Posted Tuesday, February 19, 2002 4:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #27926
Posted Tuesday, February 19, 2002 4:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
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
Post #27927
Posted Tuesday, February 19, 2002 5:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, 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.
Post #27928
Posted Tuesday, February 19, 2002 7:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
Seems like a function would work for that?


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #27929
Posted Tuesday, February 19, 2002 9:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #27930
Posted Tuesday, February 19, 2002 10:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
Different regions meaning different parts of a table?


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #27931
Posted Tuesday, February 19, 2002 10:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #27932
Posted Monday, March 4, 2002 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2003 12:00 AM
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!




Post #27933
Posted Monday, March 4, 2002 10:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:11 AM
Points: 31,212, Visits: 15,655
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
Post #27934
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse