Remove Non Alphanumeric Characters

by Amol 20. July 2012 08:45

SQL Query to remove non alphanumeric characters

CREATE FUNCTION dbo.RemoveNonAlphaNumericCharacters

(

      @Str nvarchar(255)

)

RETURNS int

AS

BEGIN

 

      DECLARE @Result varchar(255)

      SET @Result = 1

 

      DECLARE @nchar nvarchar(1)

      DECLARE @position int

 

      SET @position = 1

      WHILE @position <= LEN(@Str)

      BEGIN

 

            SET @nchar = SUBSTRING(@Str, @position, 1)

            IF (ASCII(@nchar) between 32 and 128)

            BEGIN

                SET @Result = 1

            END

            ELSE

            BEGIN

                SET @Result = 0

                BREAK

            END

            SET @position = @position + 1

      END

 

      RETURN @Result

END 

Tags: ,

SQL Server Scripts

Generate insert data script

by Amol 21. March 2011 08:12

Problem: 

In many situations we need to populate table with required data and for that we need INSERT INTO statements in SQL Server. Writing such script would be time consuming if you have thousands of records. Mostly we need this script to populate Meta data tables from development box to production server. This can be achieve in couple of ways, like import export functionality, BCP out and BCP in functionality. These options are also time consuming and involved little bit efforts to do it. Also for these options you need some extra rights to perform these operations. 

Solution:

Isn't it be nice to call just one procedure to generate all insert data script and just run that script wherever you need to run? So here is the procedure to generate insert data script. Click to download the procedure prcCreateInsertScript and create this procedure into your database.

Create_Insert_Data_Script.sql (3.34 kb)

How it works?

Let’s take an example, you have Meta data table Country having the list of all the countries. 

 

CREATE TABLE [dbo].[Country] 
(
      Id INT NOT NULL,
      Name VARCHAR(255) NOT NULL
)
GO

 

Add some countries in country table. 

 

INSERT INTO [dbo].[Country]
SELECT 1, 'India'
UNION ALL SELECT 2, 'United States of America'
UNION ALL SELECT 3, 'Japan'
UNION ALL SELECT 4, 'Austrelia'
UNION ALL SELECT 5, 'England'
UNION ALL SELECT 6, 'Keneya'
UNION ALL SELECT 7, 'China'
UNION ALL SELECT 8, 'South Africa'
UNION ALL SELECT 9, 'West Indies'

GO

 

Run the above script to create Country table development box. There are many countries; I have just listed some of them to show as an example.  

Now execute the procedure [dbo].[prcCreateInsertScript] to generate insert script for countries listed in Country table.

 

EXEC [dbo].[prcCreateInsertScript] 
@ObjectName = 'Country', 
@Schema = 'dbo'

GO

 

Execute the above procedure to generate insert data script. It will show the resultset shown as below. Just copy these statements and run it wherever you needed.

 

 

[dbo].[prcCreateInsertScript]  procedure accept two parameters, one is @ObjectName, this is the object name (Table Name) for which you need to generate insert data script. And second one is @Schema, this is the schema (owner) of the table. By default it takes "dbo" as schema. The procedure will run without @Schema parameter if table owner is dbo otherwise you can specify the owner of the table. 

Another case here is to consider IDENTITY column while generating insert data script. Let's revise the above example by considering Id as identity column. 

 

CREATE TABLE [dbo].[Country] 
(
     Id INT IDENTITY(1,1) NOT NULL,
     Name VARCHAR(255) NOT NULL
)

GO

 

Add some countries in country table. 

 

INSERT INTO [dbo].[Country]

SELECT 'India'
UNION ALL SELECT 'United States of America'
UNION ALL SELECT 'Japan'
UNION ALL SELECT 'Austrelia'
UNION ALL SELECT 'England'
UNION ALL SELECT 'Keneya'
UNION ALL SELECT 'China'
UNION ALL SELECT 'South Africa'
UNION ALL SELECT 'West Indies'

GO

 

Execute the procedure [dbo].[prcCreateInsertScript] to generate insert script for countries listed in Country table having Id as IDENTITY column.

 

EXEC [dbo].[prcCreateInsertScript]  
       @ObjectName = 'Country',   
       @Schema = 'dbo'

 

 

 

You can observe the generated script, it adds "SET IDENTITY_INSERT [dbo].[Country] ON" as very first line to allow insert value into IDENTITY column. Also it sets OFF at the last line as "SET IDENTITY_INSERT [dbo].[Country] OFF" 

Limitations:

All the table datatypes has not been considered while generating insert data script. It may fail for new datatypes. 

Note: Please consider this script as baseline script and use it on your own risk.

Create_Insert_Data_Script.sql (3.34 kb)

Tags: , ,

SQL Server Scripts

Inside SQL Server Stored Procedure

by Amol 28. February 2011 00:21

fn_get_sql() function can be used to get what statement is currently executing for a process. Usually we use DBCC INPUTBUFFER to get know what the process is doing. But this will return the procedure name not the exactly which code of line is being executed. For example, if you have procedure 1 and procedure 1 calls another procedure 2 and procedure 2 calls another procedure 3. And even when procedure 3 is executing, DBCC INPUTBUFFER will return procedure 1 not procedure 3. 

fn_get_sql() will return the exact line of code being executed.

To see how it works, create following stored procedure. This procedure can be used to diagnose performance related issues, long running queries inside the procedures, blocked queries.

IF OBJECT_ID('dbo.WhatsGoingOn') IS NOT NULL
     DROP PROCEDURE dbo.WhatsGoingOn

GO

CREATE PROCEDURE dbo.WhatsGoingOn
(
    @SPID smallint,
    @Wait tinyint = 0
)
AS
BEGIN
SET NOCOUNT ON

      DECLARE @NoLoop bit, @SqlHandle binary(20), @HandleFound bit
      DECLARE @StmtStart int, @StmtEnd int
      DECLARE @Command nvarchar(4000), @WaitStr varchar(8)

      SET @HandleFound = 0
      SET @NoLoop = 0 

      IF @Wait NOT BETWEEN 0 AND 60
      BEGIN
            RAISERROR('@Wait should be between 0 to 60 seconds', 16, 1)
            RETURN -1
      END
      ELSE
      BEGIN
            SET @WaitStr = '00:00:' + RIGHT('00' + CAST(@Wait AS varchar(2)), 2)      END

      WHILE 1 = 1
      BEGIN
            SELECT @SqlHandle = sql_handle,
                   @StmtStart = stmt_start/2,
                   @StmtEnd = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
            FROM master.dbo.sysprocesses
            WHERE spid = @SPID
                  AND ecid = 0

            IF(@SqlHandle = 0x0) OR (@StmtStart = 0 AND @StmtEnd = 0)
            BEGIN
                  IF @HandleFound = 0
                  BEGIN
                        RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
                        RETURN -1
                  END
                  ELSE
                  BEGIN
                        RAISERROR('Query/Stored procedure completed', 0, 1)
                        RETURN 0
                  END
            END
            ELSE
            BEGIN
                  SET @HandleFound = 1
            END 

            SET @Command =
            (
                  SELECT
                  SUBSTRING ( text,
                              COALESCE (NULLIF(@StmtStart, 0), 1),
                              CASE @StmtEnd
                              WHEN -1 THEN DATALENGTH(text)
                              ELSE (@StmtEnd - @StmtStart)
                              END
                            )
                  FROM ::fn_get_sql(@SqlHandle)
            ) 

            RAISERROR(@Command, 0, 1) WITH NOWAIT

            IF @NoLoop = 1
            BEGIN
                  RETURN 0
            END 

            WAITFOR DELAY @WaitStr

       END

END
 

Tags: ,

SQL Server Scripts

Tag cloud