Tag Archives for reusable SQL

Declare Cursor with Dynamic SQL example

My new goal is to deliver to the DBAs scripts that they don’t need to execute on multiple databases. Cursors and Dynamic SQL are my new friends.

DECLARE @DbName VARCHAR(100), @sql1 VARCHAR(MAX), @sql2 VARCHAR(MAX)
DECLARE C1 CURSOR FORWARD_ONLY FOR
SELECT [Name] FROM SYS.SYSDATABASES WHERE [DBID] NOT IN (1,2,3,4) AND [Name] like ‘%’
OPEN C1
FETCH NEXT FROM C1 INTO @DbName WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql1 = ‘USE ‘ + @DB_Name + ‘; SELECT * FROM <TABLE_NAME>’
EXEC (sql1)

FETCH NEXT FROM C1 INTO @DbName
END
CLOSE C1
DEALLOCATE C1

CAST and CONVERT

Table of implicit and explicit data type conversions.

ms18792872efe1973708

Today I get to convert [text] to XML. This table will be more than valuable.

Coming off of a holiday weekend, this is quite a Tuesday morning wakeup. Welcome back to work! One thing is for certain, I still get to learn something new everyday. At least I had a fantastic weekend of grilling out and board games. Played some Kings and Queens, played some Battlestar Galactica, started playing Wall-E on the 360, saw Star Trek in the theater (again) and had grilled chicken and homemade ice cream on back to back nights. And this workweek is just four days long.

SELECT * FROM Week WHERE DAY = ‘Friday’

  • I’m searching through a table with 15 foreign keys trying to find a specific person on a specific day, then find out which foreign keys are actually meaningful. ‘LastModifiedBy’ is one I can probably skip. But this one stored procedure has been my biggest timesaver this week.

sp_fkeys @fktable_name = ‘<TABLENAME>’

  • I’m searching through the SALE and TRANS tables trying to find discrepancies. On the surface, they look identical [same number of rows, same number of distinct rows, same dollar total]. But a report I don’t trust anyway says there’s a $4000 difference. I got to say this to Amy Accounting: “I’ll be happy to look into it but it won’t be today; there are too many other important things.”
  • I’m running 4 different IM protocols, talking over all of them to coworkers, vendors, friends, and more coworkers.

Finale of Battlestar Galactica tonight, 8pm Central. I’m going to run/walk 7.5 miles tomorrow morning. I’ve got to pick up a new Rock Band guitar controller, new insoles for my running shoes, clean my kitchen, and at some point get 8 hours of sleep.