x002548's Blog - January 2005 Entries

Posts: 83
Comments: 600
Trackbacks: 40

January 2005 Entries

But MOM! Do I have to use sp_executesql

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 Northwind
GO

SET NOCOUNT ON
CREATE TABLE myCounts99(mySPID int, myCount int)
GO

DECLARE @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 @sql
EXEC(@sql)

DECLARE @x int
SELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPID
SELECT @x
GO

SET NOCOUNT OFF
DROP TABLE myCounts99
GO

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 simpler

DECLARE @Count int
execute sp_executesql
N'select @Count = COUNT(*) from Northwind.dbo.Orders',
N'@Count int OUT', @Count OUT
SELECT @Count

And, in reading Books Online (BOL) the benefits of sp_executesql are that it may reuse a plan that is stored for the sql. Where as EXEC has to be recompiled each and every time. Now being lazy, EXEC is nice and simple, no thought to syntax is needed for the most part, and I'm sure you can go real nuts with EXEC and dynamic. BUT, if you need to return a value, it sure seems like the way to go.

Also, now that I've bloged this example, I don't have to remeber how to use it anymore. Like in the thread, I got hung up on the OUT paramater...notice it's not OUTPUT...ah M$ standards....

MOO

EDIT: Here's a dynamic twist from Pat

http://www.dbforums.com/t1171619.html

EDIT: Here's a good article discussiong the pro's and cons' of dynamic sql

http://www.sommarskog.se/dynamic_sql.html

posted @ Thursday, January 27, 2005 10:13 AM | Feedback (6)

Search SQL Server for a table in any databse

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: Well Chester (http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=10121) has a simpler solution. It just comes out as many result sets, and you can't interogate it...but it is elegant

Exec sp_MSforeachdb
'Select ''?'' as Dbname, * From ?.INFORMATION_SCHEMA.Tables where TABLE_NAME like ''Orders%'''

Well...here you go:

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50))
GO

CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON

TRUNCATE TABLE myTable99

IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a tabke name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END

DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)

SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases

WHILE @dbname < = @MAX_dbname
BEGIN
SELECT @sql = 'SET NOCOUNT ON '                                                    + CHAR(13)
  + 'INSERT INTO myTable99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)'    + CHAR(13)
  + 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE '                   + CHAR(13)
  + 'FROM ' + @dbname + '.INFORMATION_SCHEMA.Tables'                                + CHAR(13)
  + 'WHERE TABLE_NAME LIKE ''' + @TABLE_NAME + '%' + ''''                           + CHAR(13)
-- SELECT @sql
EXEC(@sql)
SELECT @dbname = MIN([name]) FROM master..sysdatabases WHERE [name] > @dbname
END

SELECT * FROM myTable99

usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURN
GO

EXEC usp_FindMyTable

EXEC usp_FindMyTable Orders
GO

SET NOCOUNT OFF
DROP PROC usp_FindMyTable
DROP TABLE myTable99
GO

posted @ Thursday, January 13, 2005 12:03 PM | Feedback (5)

Fair or Fowl?

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?

posted @ Wednesday, January 12, 2005 1:33 PM | Feedback (20)

Build a Comma Delemited String For All Rows

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 (especially the part about loosing the Cursor). I've just received so much knowledge from the “Team“ that's hard to keep track (I still have to post Arnolds time conversion that he was so gracious to allow me to post). Thanks again to all the members:

http://www.sqlteam.com/forums/members.asp

So I take no credit for any of this, I'm just being lazy.

The original thread that got me to post this, because I was tired of retyping it from scratch

http://www.dbforums.com/t1084977.html

OK, I got this from MWolf at

http://www.dbforums.com/member.php?userid=99822

It seems that the plan for this much moer effecient. I'll need to investigate....see for your self...use with the code below..

CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ', ','') + col2
FROM myTable99
WHERE col1=@id

RETURN @Result
END
GO

SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 1, 'D' UNION ALL
SELECT 1, 'P' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT 2, 'W' UNION ALL
SELECT 3, 'X' UNION ALL
SELECT 3, 'Y' UNION ALL
SELECT 4, 'Z' UNION ALL
SELECT 5, 'O'
GO

DECLARE @MAX_Col1 int, @Col1 int, @strCol2 varchar(8000)
DECLARE @rs table(Col1 int, strCol2 varchar(8000))

SELECT @MAX_Col1 = MAX(Col1), @Col1 = MIN(Col1) FROM myTable99

WHILE @MAX_Col1 > = @Col1
BEGIN
SELECT @strCol2 = COALESCE(@strCol2 + ', ','') + Col2 FROM myTable99 WHERE Col1 = @Col1
INSERT INTO @rs(Col1, strCol2) SELECT @Col1, @strCol2
SELECT @Col1 = MIN(Col1), @strCol2 = null FROM myTable99 WHERE Col1 > @Col1
END

SELECT * FROM @rs
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

posted @ Wednesday, January 05, 2005 2:22 PM | Feedback (4)

Datatypes: CHAR, VARCHAR and VARCHAR2

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 semantics of the VARCHAR2 datatype. Currently, in Oracle 9i, when you insert a zero length string into a VARCHAR2 column, Oracle treats this the same as if you insert a NULL value into that column. But it wasn't always this way. Previous versions of the database treated zero length strings and NULL values as two distinctly different items.

And aren't they supposed to be different? This is progress? An empty string <> null, hell even null <> null..so what gives? Is an empty string and null ANSI compliance? Or does it have nothing to do with it? Ah, more reasearch...

UPDATE:

Tony Andrews, a moderator over at dbforums points out the following...further adding to the confusion...nothing like testing things out...Thanks Tony

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Jan 5 17:27:58 2005

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> create table t (id int, text varchar2(10));

Table created.

SQL> insert into t values (1, '');

1 row created.

SQL> select * from t where text is null;

ID TEXT
---------- ----------
1