Tag Archives: t-sql - Page 4

T-SQL: Alternatives to Using Cursors

Posted by on May 27, 2008 Comments Off on T-SQL: Alternatives to Using Cursors

[UPDATE] — SQLServerCentral.com has a great article( from 2002) on the use of Temp Tables. You may find the information useful.

I have gotten a ton of hits some links that I created years ago on T-SQL Cursors. Sometimes they are the the best course of action , but may times they are not the most efficient. This posting has some a possible alternative.

Creating Tables with Identity Columns
I like to create tables and temp tables with identity columns. You can then do row by row process the data using the id column. In another post, I will show you how to create and some uses for Table-Valued functions to provide similar utility found with using cursors. Oh, don’t forget our friend the Case Statement, either.

Read more »

T-SQL: Adding Leading Zeros with a Simple Scalar Function

Posted by on April 28, 2008 Comments Off on T-SQL: Adding Leading Zeros with a Simple Scalar Function

We were creating a extract at work from T-SQL and new that the we would need to export data with leading zeros. The data was not stored in the database with those leading zero values. The solution, create a simple scalar function to dynamically add the zeros that could accept two parameters ( text , and total length).

The Function is rather simple takes the total length value passed by the user and then subtracts the Length of the the text value.

What would make this better?

  • Would have a default value for total length
  • Error checking to make sure the text supplied is not longer than the total length

Read more »

T-SQL Create a View for Report Date Ranges by Month

Posted by on March 28, 2008 Comments Off on T-SQL Create a View for Report Date Ranges by Month

The below example gives prior and current month first and last day values.The code below dynamically formats the date to give you date range values for reporting:

[LFM-FirstDay] – First Day of thePrior Month or Last Full Month First Day,
[LFM-LastDay] – 11:59 PM of the last day of the prior month,
[CM-FirstDay] – Current Month First Day and
[CM-LastDay] – 11:59 PM of the last day of the Current month.

USE [dbofchoice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [DBO].[ReportDates]
AS
/*
LFM = LAST FULL MONTH
CM = CURRENT MONTH
*/
SELECT
DATEADD(month, -1 ,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
as [LFM-FirstDay]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))
as [LFM-LastDay]
,DATEADD(mm, DATEDIFF(m,0,getdate()),0)
as [CM-FirstDay]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))
as [CM-LastDay]
GO

SQL Server How-to’s on Video (YouTube)

Posted by on March 10, 2008 Comments Off on SQL Server How-to’s on Video (YouTube)

Some not so bad How-to’s on You-tube for SQl Server. Some pretty advanced topics like SS with EMC best practices.

Take a Look:

Checkout Related and others Video Here.

Group By and Case Statements or Other SQL Functions

Posted by on February 28, 2008 Comments Off on Group By and Case Statements or Other SQL Functions

One thing I forget to do often is to include the actual item I’m returning in the Select Statement in the Group By. Meaning , if I use a case statement of some function to change the value that I’m returning, then I should include that and not the base column name in the Group By part of the select statement


/*
If you write something like this and the Col2 has multiple 'Unique' Values
then you will get a row for each value distinct Col2 value that says 'Other'
*/SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1], [Col2]

-- This Example will return Only two rows

SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1]
, Case When [Col2] = 10 then 'Ten'
Else 'Other' End

Select Case has a simple overview of using CASE in a SQL Query with the SUM() function.

For Each … SQL System Stored Procedures

Posted by on February 25, 2008 Comments Off on For Each … SQL System Stored Procedures

Sql server has some great built-in commands procs that can help you script server or database wide commands. sp_MSForEachTable and Sp_MSForEachDb

When first looking at a database , I sometimes like to run the following:
execute sp_MSforEachTable @command1=‘Print ”?”; Select Count(*) as [? – (count)] from ?;Select Top 10 * from ? ‘

I used the sp_MSforeachDB just the other day to set all the non-system
databases on a test server to Simple Recovery mode.Just made a quick stored procedure that I passed the ? value and filter out the system databases ( Master, Model, Tempdb and MSDB).

For More Information Checkout :

November 30, 2004
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb
By Gregory A. Larsen
http://www.databasejournal.com/features/mssql/article.php/3441031

Link to Old Articles and Scripts

Posted by on February 23, 2008 Comments Off on Link to Old Articles and Scripts

https://jackdonnell.com/articles/index.htm is still available and has the old links to javascript s, T-SQL scripts, etc.

Jack of All Trades – Yeah Right.

Posted by on February 20, 2008 Comments Off on Jack of All Trades – Yeah Right.

I’ve had a static web page up for the several years stating to check back soon for updates.  They have been around for so long that I even have a top search spot on ‘T-SQL Cursors’

Keep checking back. I’ll post somemore ‘Top’ ranking seraches. No code like bad code.