January 2005 Blog Posts
OK, it seems often asked how to return values from dynamic sql. My initial reastion was to do something like the following. Which works, is flexible, is highly dynamic, and does take a bit of setup
USE NorthwindGOSET NOCOUNT ONCREATE TABLE myCounts99(mySPID int, myCount int)GODECLARE @sql varchar(8000)SELECT @sql = 'DELETE FROM myCounts99 WHERE mySPID = ' + CONVERT(varchar(4),@@SPID) + CHAR(13)+ 'INSERT INTO myCounts99(mySPID, myCount) SELECT ' + CONVERT(varchar(4),@@SPID) + ', COUNT(*) FROM Orders'SELECT @sqlEXEC(@sql)DECLARE @x intSELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPIDSELECT @xGOSET NOCOUNT OFFDROP TABLE myCounts99GO
But as Nigel http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578 points out in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45193 it can be much...
I've seen recently a lot of people with quite a proliferation of databases on a single server. I'm not sure why this is, but it may be a “personalized” database per client, so each client has their own replica of a database for an application. If that's the case, then I would say those are bad designs. If it's not, then I don't know. It would just be a maint. nightmare. In any event, someone finally asked:
“Anyone might have an idea or know how to find a specific table from various databases? I have about 20 database”
In the following Post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44682
EDIT:...
I think I'll have the Chardonay with that.
But I don't know what to think when Fabian Pascal makes a reference to SQLTeam
http://www.tdan.com/sms_issue31.htm
And doesn't reference the responses in that thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42060
What would have been fun is if Fabian picked up on the thread where the posted wanted to make sure you could lock out the administrator for a database. THAT was a fun thread....I have to find that and post it here.
But, is it me, or does it seem overly self serving of Fabian?
Seems to be a popular question people seem to look for. I Still don't know why. Kinda hard to perform set processing against data put in this form. I forget the first day I saw this, or from whom, but I always reference this article on the great SQLTeam Site.
http://www.sqlteam.com/item.asp?ItemID=2368
So, once they got the ability to do it for 1 ID, they'd like the Whole thing denormalized...so I'll post mine here so I don't have to retpe (lord knows I could use the practice though). It's been done over and over, and I'm sure the original came from someone here...
HUH? VARCHAR2?
You gotta love Oracle...ANSI be damned. Well I never specifically knew why we needed to used varchar2 during the last several projects we were on. Mostly because I was told to...don't think the person who directed the effort really knew either.
CHAR is simple, it retains any data you put in there AND space pads the values. A pain for comparisons later on.
VARCHAR is not supported and is reserved for future use
VARCHAR2's definition is a little more complicated since it's different between version (Hey, eve M$ tries to backward compatible)
Brian Peasland at http://searchoracle.techtarget.com puts it best
Quote:
Actually, Oracle Corp. has changed the...