Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 4, 2001

SQL "How To's" Part 1

By Sumit Dhingra


When working with SQL, how many times have you come to a situation where you've wondered "How do I do this?". Although you could find answers by doing some reading from BOL or from some newsgroup, it would be nice to have a kind of a reference material where you can get to-the-point answers. It could save you lot of time and frustration to be able to consult this resource for quick, helpful answers to common questions. This article is an attempt to begin such a reference material.


1) How to display Year as YYYY instead of YY

Enterprise Manager adopts the User's regional settings for the date format. By default, the regional setting for the short date format is m/d/yy. To display it in m/d/yyyy format, follow the following steps:

On the taskbar, click the Start button, point to Settings and then select Control Panel. In the Control Panel dialog box double-click Regional Settings. In the Regional Settings Properties dialog box, select the Date tab. Next, change the value for the Short Date Style from M/d/yy to M/d/yyyy.


2) How to Build a Comma Delimited String from Table values

Sometimes you might want to return your records in a single comma delimited string. For example, if you have records like :-

Mango
Banana
Peach
Grapes
Strawberry

Instead of returning it as a recordset containing these 5 records, you might want to return them as 'Mango, Banana, Peach, Grapes, Strawberry'. Here's how you can accomplish this.

-- Create a temporary table & insert dummy records

Create Table #Fruits (Fruit Varchar(25))
Insert #Fruits (Fruit) values('Mango')
Insert #Fruits (Fruit) values('Banana')
Insert #Fruits (Fruit) values('Peach')
Insert #Fruits (Fruit) values('Grapes')
Insert #Fruits (Fruit) values('Strawberry')
-- Build comma delimited string
Declare @Fruits Varchar(200)
Set @Fruits = ''
Update #Fruits
Set @Fruits = @Fruits + Fruit + ','
Set @Fruits = Substring(@Fruits,1,len(@Fruits)-1)
              -- to remove extra comma at the end
print @Fruits

3) How to write values to a Text file

To write a result of a SELECT query in a text file :-

master..xp_cmdshell 'osql -SMyServer -Umyuser -Pmypwd
-Q"select * from products" -dNorthwind  -w"1000" -oc:\MyText.txt'

To write a string (like comments, etc.) you can use this code :-

declare @cmd varchar(1000)
select @cmd = 'echo These are the Products in the
database>> "c:\MyText.txt"'
exec master..xp_cmdshell @cmd

4) How to add a User Defined Error Message

Use Master
Go

EXEC sp_addmessage @msgnum = 50001, @severity = 16,
    @msgtext =
N'Failed to insert Customer Transaction into %s table',
    @lang =
'us_english'

Go

You can raise this message from your code like this :-

INSERT Customers (CustomerName)
VALUES ('Rick Nelson')

IF @@Error <> 0
            RAISERROR
(50001,16,1, 'Customers')

5) How to get the Rowcount as well as Error at the same time

Normally when we run a Query, we check if the query was executed successfully or not before commiting it.

DECLARE @CustomerId            Int
SET @CustomerId = 1

Begin Transaction
Update Orders
Set OrderDate = GetDate()
Where CustomerId = @CustomerId

IF @@Error <> 0
Begin
            Raiserror('Failed
to update Orders',16,1)   
            Rollback
Transaction
End

Commit Transaction

But what if you also want to get the number of records which were effected by this query? If you do a @@Rowcount after your Error checking statement then you are going to get 0 as the value of @@Recordcount would have been reset. And if you place @@Recordcount before the error-checking statement then your @@Error would get reset. So what's the solution to get both of them at the same time ? Save both the values in a local variable and then check that local variable. Here's how it can be done.

DECLARE @Rcount                      Int
DECLARE @ErrNum                    Int

DECLARE @CustomerId            Int

SET @Rcount = 0
SET @ErrNum = 0
SET @CustomerId = 1


Begin Transaction
Update Orders
Set OrderDate = GetDate()
Where CustomerId = @CustomerId

SELECT @Rcount = @@RowCount, @ErrNum = @@Error

-- check if there was an error in Updating the records
IF @ErrNum <> 0
Begin
            Raiserror('Failed
to update Orders',16,1)   
            Rollback
Transaction
End

-- check if some records were updated or not
IF @Rcount = 0
Begin
            Raiserror('No
records for CustomerId %d',16,1, @CustomerId)             
            Rollback
Transaction
End

Commit Transaction


Any comments or suggestions are welcome at sumitdig@hotmail.com.


» See All Articles by Columnist Sumit Dhingra




SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM