CSV to INT

CSV to INT

this is how you can convert a CVS to Int

CREATE function [dbo].[FN_CsvToInt] (@id varchar(5000))      

returns @table table (wd_code int identity(1,1), [id] int)     

as     

begin     

     

 declare @separator char(1)     

 set @separator = ','     

     

 declare @separator_position int      

 declare @array_value varchar(5000)      

      

 set @id = @id + ','     

      

 while patindex('%,%' , @id) <> 0      

 begin     

      

   select @separator_position =  patindex('%,%' , @id)     

   select @array_value = left(@id, @separator_position - 1)     

      

 if(@array_value = 'null')     

  insert @table ([id])     

  values (null)     

     

 else     

  insert @table ([id])     

  values (cast(@array_value as int))     

     

   select @id = stuff(@id, 1, @separator_position, '')     

 end     

     

 return     

end

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar

TreeView in DropDown

TreeView in DropDown

Private Sub RecursiveFillTree(ByVal dtParent As DataTable, ByVal parentID As Integer)

    level += 1

    Dim appender As StringBuilder = New System.Text.StringBuilder()

    For i = 0 To level

        appender.Append(" ")

    Next

    If level > 0 Then

        appender.Append("• ")

    End If

    Dim dv As DataView = New DataView(dtParent)

    dv.RowFilter = String.Format("ParentID = {0}", parentID)

    Dim j = 0

    If dv.Count > 0 Then

        While j < dv.Count

            ddlMTDetail.Items.Add(New ListItem(Server.HtmlDecode(appender.ToString() + dv(j)("MTDetailDesc").ToString()), dv(j)("MTDetailId").ToString()))

            RecursiveFillTree(dtParent, Convert.ToInt32(dv(j)("MTDetailId").ToString()))

            j += 1

        End While

    End If

    level -= 1

End Sub

Private Sub ShowTreeNodes()

    ddlMTDetail.Items.Clear()

    Dim sErrMsg = String.Empty

    Dim dtNodes As DataTable = objMaintenanceDb.FM_MaintenanceDetail_SelectTable()

    RecursiveFillTree(dtNodes, 0)

    ddlMTDetail.Items.Insert(0, New ListItem("- Select -", "0"))

    ddlMTDetail.SelectedIndex = 0

End Sub

Manage Transaction in Database

THIS IS HOW WE CAN Manage Transaction in Database

CREATE PROCEDURE DeleteDepartment

(

   @DepartmentID    int

)

AS

-- This sproc performs two DELETEs.  First it deletes all of the

-- department's associated employees.  Next, it deletes the department.

-- STEP 1: Start the transaction

BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement

DELETE FROM Employees

WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors

IF @@ERROR <> 0

 BEGIN

    -- Rollback the transaction

    ROLLBACK

    -- Raise an error and return

    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)

    RETURN

 END

DELETE FROM Departments

WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors

IF @@ERROR <> 0

 BEGIN

    -- Rollback the transaction

    ROLLBACK

    -- Raise an error and return

    RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)

    RETURN

 END

-- STEP 4: If we reach this point, the commands completed successfully

--         Commit the transaction....

COMMIT

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar

CSV (Comma Separated Values) of integer to Table (SQL)

This is how you can convert CSV (Comma Separated Values) of integer to Table in SQL 

1. Run the following Function in SQL Server

CREATE function [dbo].[FN_CsvToInt] (@id varchar(5000))

returns @table table (wd_code int identity(1,1), [id] int)

as

begin

 declare @separator char(1)

 set @separator = ‘,’

 declare @separator_position int

 declare @array_value varchar(5000)

 set @id = @id + ‘,’

 while patindex(‘%,%’ , @id) <> 0

 begin

   select @separator_position =  patindex(‘%,%’ , @id)

   select @array_value = left(@id, @separator_position – 1)

 if(@array_value = ‘null’)

  insert @table ([id])

  values (null)

 else

  insert @table ([id])

  values (cast(@array_value as int))

   select @id = stuff(@id, 1, @separator_position, ”)

 end

 return

end

2. Thats it, Execute the following query

select * from dbo.FN_CsvToInt(’11,15,13,18′)

3. Output:

Code	Id
1	11
2	15
3	13
4	18

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar

Search any text or data in columns of SQL tables.

Search Text/Data in All Tables and Column in SQL Server

Following are the steps how you may search any text or data in columns of SQL tables.

1. Create the following stored procedure in the database you want to search any data in tables/columns

STORED PROCDURE

create PROC dbo.SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

       CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

       SET NOCOUNT ON

       DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

       SET @TableName = ”

       SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

       WHILE @TableName IS NOT NULL

       BEGIN

              SET @ColumnName = ”

              SET @TableName =

              (

              SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

              FROM INFORMATION_SCHEMA.TABLES

              WHERE TABLE_TYPE = ‘BASE TABLE’

              AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) &gt; @TableName

              AND OBJECTPROPERTY(

              OBJECT_ID(

              QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

              ), ‘IsMSShipped’

              ) = 0

              )

                     WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

                     BEGIN

                     SET @ColumnName =

                     (

                     SELECT MIN(QUOTENAME(COLUMN_NAME))

                     FROM INFORMATION_SCHEMA.COLUMNS

                     WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

                     AND TABLE_NAME = PARSENAME(@TableName, 1)

                     AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)

                     AND QUOTENAME(COLUMN_NAME) &gt; @ColumnName

                     )

                     IF @ColumnName IS NOT NULL

       BEGIN

       INSERT INTO #Results

       EXEC

       (

       ‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

       FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

       ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

       )

       END

       END

       END

SELECT ColumnName, ColumnValue FROM #Results

END

2. Run the procedure and give parameter execute SearchAllTables ‘Gr 4′

OUTPUT

Column Name	Column Value
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4
[dbo].[BackupStudent].[Class_Enrolled]	Gr 4

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar

Return a Camel Case Text in SQL

Following are the steps to return the Camel Case sentence in SQL

1. Execute the following procedure

CREATE FUNCTION [dbo].[CamelCase]

(@Str varchar(8000))

RETURNS varchar(8000) AS

       BEGIN

       DECLARE @Result varchar(2000)

       SET @Str = LOWER(@Str) + ‘ ‘

       SET @Result = ”

       WHILE 1=1

       BEGIN

                     IF PATINDEX(‘% %’,@Str) = 0 BREAK

                     SET @Result = @Result + UPPER(Left(@Str,1))+

                     SubString (@Str,2,CharIndex(‘ ‘,@Str)-1)

                     SET @Str = SubString(@Str,

                     CharIndex(‘ ‘,@Str)+1,Len(@Str))

                     END

       SET @Result = Left(@Result,Len(@Result))

       RETURN @Result

END

2. Thats it …. run the the function

select dbo.[CamelCase] (‘HOW ARE YOU’)

3. Output 

How Are You

Regards,

Sheryar Nizar

LinkedIn Profile :http://www.linkedin.com/in/sheryarnizar