Mark Breen
marklbreen at gmail.com
Thu Jun 25 02:56:24 CDT 2009
Hi John, Great, but in your comment below, you mention printing them out. I was referring more to writing your own UDF's. It is as easy as a sproc, but can be used inline. For the example you had originally posted in this thread, you could write a udf that would concatenate the fields you need, with nulls taken care of, then that udf is available throughout the db. Again, I guess you now this, but if it helps, I am glad. As Arthur mentions below, you can have a udf that returns a value, or you can also have one return a table. Does any one know why you use a UDF to return a table, rather than an sproc ? Mark 2009/6/24 jwcolby <jwcolby at colbyconsulting.com> > Mark, > > I know that they exist, and I have used them very occasionally. I really > need to go become > acquainted with all of them, print out a list and study what is available. > I am doing rather a lot > of stored procedures now, but they mostly just build tables, add fields, > indexes, bcp in/out etc. > > I am still at the advanced beginner level in SQL Server. > > John W. Colby > www.ColbyConsulting.com > > > Mark Breen wrote: > > Hello John, > > you thought me how to call vba functions in MS Access 97 queries, I was > > shocked by how powerful that feature was. > > > > Have you used functions in SQL Server? If so, please ignore my email and > > accept my apologies for even asking the question;) > > > > I have created a few functions in SQL Server and for low volumes of data, > > they work great, of course in your case, you are seeking max efficiency > and > > I have no idea about performance of a function versus inline code. I > guess > > no major difference. > > > > If you have not used them, give them a try. > > > > Thanks > > > > Mark > > > > > > > > > > > > 2009/6/23 jwcolby <jwcolby at colbyconsulting.com> > > > >> This certainly looks like the answer. Now to turn the computer loose > for a > >> week, updating the > >> address field... > >> > >> There are about 6 or 8 of these address parts, each will now be inside > of > >> an IsNull(). ;) > >> > >> Is it possible to do this X records at a time? IOW I have a fifty > million > >> record table. I want to > >> cause the computer to go off and start doing this but I also want the > table > >> that is being updated to > >> be available for use while this is happening (for selection queries). > How > >> do I tell SQL Server to > >> do updates in blocks of 1000 records or something like that? Can I > >> dynamically change that "number > >> of records updated at a time" inside of a stored procedure? > >> > >> John W. Colby > >> www.ColbyConsulting.com > >> > >> > >> Francisco Tapia wrote: > >>> John, you can do it right out of a select, something like this > >>> > >>> IsNull(StreetNumber + ' ', '') + IsNull(Direction + ' ', '') + > >>> IsNull(StreetName + ' ', '') etc... > >>> > >>> I added the space padding right inside of the IsNull formula because > this > >>> way you get a padding, for each field, unless there is nothing then the > >>> result is no padding. In Sql Server Null + a value = Null :) > >>> > >>> If you don't want to accidentally end up with a space padding on the > >> right > >>> simply trim your statement with RTRIM(your isnull series) > >>> > >>> > >>> > >>> -Francisco > >>> http://sqlthis.blogspot.com | Tsql and More... > >>> > >>> > >>> On Tue, Jun 23, 2009 at 6:53 AM, jwcolby <jwcolby at colbyconsulting.com > >>> wrote: > >>> > >>>> I am trying to append all of the pieces of an address into a single > >> address > >>>> string. As an example, > >>>> if I have the address: > >>>> > >>>> 121 S. Main Street NE Apt #201 > >>>> > >>>> this address will be stored with standardized spellings of all the > parts > >>>> into a set of fields: > >>>> > >>>> 121 > >>>> S > >>>> Main > >>>> St > >>>> NE > >>>> Apt > >>>> 201 > >>>> > >>>> I need to get this all back into a single field. Understand however > >> that > >>>> lots of those pieces might > >>>> be missing in any given address. For example the next field might be: > >>>> > >>>> 121 > >>>> > >>>> Main > >>>> St > >>>> > >>>> Ste > >>>> 1342 > >>>> > >>>> So... I need to get all the pieces back into a single address field. > If > >> a > >>>> piece exists then it > >>>> needs padding around it so I don't end up with: > >>>> > >>>> 121MainStSte1342 > >>>> > >>>> But if a piece does not exist then I do not want the padding for that > >>>> missing field. > >>>> > >>>> I am assuming that I will be using a stored procedure. Pass in the > >>>> database, table, > >>>> UnifiedAddressField, then all of the field parts. > >>>> > >>>> It seems clumsy to do it this way, not to mention record by record, > for > >> 50 > >>>> million records... well... > >>>> > >>>> There must be a better way. Has anyone on the list done this before? > >>>> > >>>> -- > >>>> John W. Colby > >>>> www.ColbyConsulting.com > >>>> _______________________________________________ > >>>> dba-SQLServer mailing list > >>>> dba-SQLServer at databaseadvisors.com > >>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >>>> http://www.databaseadvisors.com > >>>> > >>>> > >>> _______________________________________________ > >>> dba-SQLServer mailing list > >>> dba-SQLServer at databaseadvisors.com > >>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >>> http://www.databaseadvisors.com > >>> > >>> > >> _______________________________________________ > >> dba-SQLServer mailing list > >> dba-SQLServer at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >> http://www.databaseadvisors.com > >> > >> > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >