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.

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) 
    IF @str IS NULL
        RETURN NULL  

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

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

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')