Francisco Tapia
fhtapia at gmail.com
Thu Jun 25 12:13:45 CDT 2009
You can't join on a sproc. typically your inline function (table return) would not exceed the amount of ram you have available for such tasks, and then you'd use this function with parameters to help result with a joinable table. (that's just one reason (otomh)). -- -Francisco http://sqlthis.blogspot.com | Tsql and More... On Thu, Jun 25, 2009 at 12:56 AM, Mark Breen <marklbreen at gmail.com> wrote: > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >