Archive for the ‘MS SQL Server/ T-SQL’ Category

Select Multiple Objects in SQL Manager Studio

This is a little tip for developers who are using SQL Server 2005 and, like me, are missing some of the functionality Enterprise Manager used to offer.

It’s often necessary to select multiple objects (tables or stored procedures) to script or drop them. But in the Object Explorer in SQL Management Studio you can only select one item at a time. That’s a pain when you need to publish your new stored procedures on the production database at the end of the day.

After a couple of days I realised that you can use the summary page to multiselect objects. Just use the F7 key to open it and now you can script or delete multiple objects. You can even filter the objects you want to see on the page by using the filter option on the top of the page. That’s very helpful when your database has more than one thousand stored procedures.

Advertisements

T-SQL Proper Case Function

Today one of our clients asked us to fix the letter casing in a particular field of the products table. We had just imported their products table from their old system into ours and the products descriptions were all messed up.

Since there is no buit-in function to do that, I had to do it my own.
I’m posting it here in case someone find it useful. 

CREATE FUNCTION [dbo].[ProperCase] 
( 
    @str VARCHAR(500) 
) 
RETURNS VARCHAR(500) 
AS
BEGIN
    IF @str IS NULL
        RETURN NULL  

    DECLARE @counter INT
    DECLARE @ret VARCHAR(255) 
    DECLARE @goUp BIT
    DECLARE @currentChar CHAR 
     
    SET @counter = 1 
    SET @ret = ''         
    SET @goUp = 1 
 
    WHILE @counter <= LEN(@str) 
    BEGIN 
        SET @currentChar = SUBSTRING(@str, @counter, 1) 
        IF @currentChar IN (' ', '-')                    
            SET @goUp  = 1 
        ELSE 
        BEGIN 
            IF @goUp  = 1 
                SET @currentChar = UPPER(@currentChar) 
            ELSE 
                SET @currentChar = LOWER(@currentChar)  
             
            SET @goUp  = 0 
        END 

        SET @ret = @ret + @currentChar
        SET @counter = @counter + 1 
    END 
    RETURN @ret 
END 

After creating the user-defined function in the database, you can use the following line to test it:

SELECT dbo.ProperCase('GOLD 802.11B WIRELESS CARD')