split comma separated string to integer

Sometime we need to split a comma separated string to a Table of integers. The below function will split the given comma-separated string into integers and process the results.


CREATE Function SplitStringtoInt(@strTemp nVarChar(4000)) Returns @intTable Table([Value] [Int] NOT NULL)
AS
BEGIN
    DECLARE @intValue nVarChar(100)
    DECLARE @pos int
    -- TRIMMING THE BLANK SPACES
    SET @strTemp = LTRIM(RTRIM(@strTemp))+ ',' 
    -- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
    SET @pos = CHARINDEX(',', @strTemp, 1) 
    -- CHECK IF THE STRING EXIST FOR US TO SPLIT
    IF REPLACE(@strTemp, ',', '') <> '' 
    BEGIN
        WHILE @pos > 0
        BEGIN
-- GET THE 1ST INT VALUE TO BE INSERTED
            SET @intValue = LTRIM(RTRIM(LEFT(@strTemp, @pos - 1))) 
            IF @intValue <> ''
            BEGIN
                INSERT INTO @intTable (Value) 
                VALUES (CAST(@intValue AS bigint)) 
            END
            -- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
            SET @strTemp = RIGHT(@strTemp, LEN(@strTemp) - @pos) 
            -- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
            SET @pos = CHARINDEX(',', @strTemp, 1) 
        END
    END    
    RETURN
END

Usage: SELECT * FROM dbo. SplitStringtoInt ('12345,87612,988473')

Query to write/create a file

This SQL stored procedure will allow you to write to the file on your system where SQL Server is running. If you are using this with your local SQL server then it will write and create files on your local file system and if you are on the remote machine, the file system will be the remote file system.

You need to reconfigure some advanced SQL server settings to use below stored procedure. Use the below configuration query to enable 'OLE Automation Procedures'. If this is not enabled and you try executing the procedure you will get errors.

Use master

GO

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

GO

--To enable Ole automation feature

EXEC sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Now, the stored procedure to create file in local system: 

Create Procedure  [dbo].[USP_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) 

AS

Begin

declare @Object int,

        @rc int, -- the return code from sp_OA procedures 

        @FileID Int

EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT

EXEC @rc = sp_OAMethod  @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1 

Set  @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')

EXEC @rc = sp_OAMethod  @FileID , 'WriteLine' , Null , @text  

Exec @rc = master.dbo.sp_OADestroy @FileID   

  

Declare @Append  bit

Select  @Append = 0

  

If @rc <> 0

Begin

    Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append

End

Exec @rc = master.dbo.sp_OADestroy @Object 

End

In this procedure the first parameter will take the text to be written to the file and the second parameter will take the complete path of the file to be created with the text in it. You can also use the same procedure to write binary files to the file system, you just need to check and change the file extension in the second parameter. 

EXEC USP_SaveFile 'Writing data to text file', 'D:\Temp\MSSQL.txt'

Useful views and stored procedures

• sys.databases : Lists all the databases in Sql Server.
• sys.tables : Lists all the Tables in the database.
• sys.views : Lists all the views in the database.
• sys.procedures : Lists all the Procedures in the database.
• sys.triggers : Lists all the Triggers in the database.
• sys.columns : Lists all the columns of tables in database.
• sys.syscolumns : Lists all the columns in database including of those SP.
• sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
• sys.check_constraints : Lists all the Check Constraints in the database.
• sys.default_constraints : Lists all the Default Constarints in the database.
• sys.foreign_keys : Lists all the Foreign Keys in the database.
• sys.syslogins : Lists all the login names in server.
• sys.sql_logins : Lists all the Sql Authentication Logins in server.
• sys.sysusers : Lists all the users in database.

Check leap year

There are various techniques to determine whether a given year is a leap year. You can use the below SQL function to check for leap year:


CREATE FUNCTION F_BIT_LEAP_YEAR
(@p_year SMALLINT)
RETURNS BIT
AS
BEGIN
    DECLARE @p_leap_date SMALLDATETIME
    DECLARE @p_check_day TINYINT

    SET @p_leap_date = CONVERT(VARCHAR(4), @p_year) + '0228'
    SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))
    IF (@p_check_day = 29)
        RETURN 1

    RETURN 0  
END

Recompile Stored Procedures, Views and Functions

Sometimes after massive changes in MSSQL database, there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. Below is a small MSSQL code snipped written solely for maintenance purposes. It goes through database objects and performs recompilation using sp_recompile system stored procedure.


DECLARE proccurs CURSOR
FOR
    SELECT [name] FROM sysobjects
WHERE xtype in ('p', 'v', 'fn')

OPEN proccurs
DECLARE @pname VARCHAR(60)

FETCH NEXT FROM proccurs INTO @pname
WHILE @@fetch_status = 0
BEGIN
    EXEC sp_recompile @pname
    FETCH NEXT FROM proccurs INTO @pname
END
CLOSE proccurs
DEALLOCATE proccurs