Sql Function for padding text to field output

create function ULPAD(@vInput varchar(max), @iLenght smallint,@cPadChar char(1))
   returns varchar(max)
as
begin
   if @vInput is not null
   begin
      if LEN(@vInput) <= @iLenght
      begin
         set @vInput = RIGHT (REPLICATE(@cPadChar, @iLenght)+ CAST (@vInput AS varchar), @iLenght)
      end
   end
   return @vInput
end

Ex:
SELECT @NewEmpNo =dbo.ULPAD((Select com_empsrno +1  from Mcompany  where com_code=@Emp_Org_srno) , 5,'0')             

Add Dynamicaly Row in Grid view

'Designer page------------------------------------------------------------


<asp:GridView id="GridQuote" runat="server" DataKeyNames="Quote_srno" ShowHeader="False" GridLines="None" AutoGenerateColumns="False" CellPadding="0" __designer:wfdid="w31"><Columns>
<asp:TemplateField HeaderText="quotes"><ItemTemplate>
<TABLE style="WIDTH: 428px"><TBODY><TR><TD vAlign=top align=right><asp:Label id="Label1" runat="server" Text="Quote :" __designer:wfdid="w38"></asp:Label></TD><TD><uc1:cgTextbox id="txtQuote" runat="server" Height="150" MaxLength="1000" Width="325" TextMode="MultiLine" Text='<%# Eval("Quote") %>' Required="true" __designer:wfdid="w39"></uc1:cgTextbox></TD><TD vAlign=top>&nbsp;<asp:ImageButton id="btnRemQuote" onclick="btnRemQuote_Click" runat="server" ImageUrl="~/App_Themes/images/btnRemove.jpg" CausesValidation="False" __designer:wfdid="w40">
                                                                        </asp:ImageButton>&nbsp;</TD></TR><TR><TD align=right><asp:Label id="Label2" runat="server" Text="Quote By :" Width="65px" __designer:wfdid="w41"></asp:Label></TD><TD><asp:TextBox id="txtQuoteBy" runat="server" Text='<%# Eval("QuoteBy") %>' MaxLength="50" Width="98%" __designer:wfdid="w42"></asp:TextBox></TD><TD></TD></TR><TR><TD align=right><asp:Label id="Label3" runat="server" Text="Posted By :" Width="71px" __designer:wfdid="w43"></asp:Label></TD><TD><uc2:cgSearchEmp id="CgSearchEmp1" runat="server" UserId='<%#Eval("PostUserId") %>' EmpName='<%# Eval("PostUserNm") %>' EmpNo='<%# Eval("PostEmpNo") %>' __designer:wfdid="w44"></uc2:cgSearchEmp> </TD><TD align=right>&nbsp;<asp:ImageButton id="btnAddMoreQuote" onclick="btnAddMoreQuote_Click" runat="server" ImageUrl="~/App_Themes/images/btnAdd.jpg" Visible="False" CausesValidation="False" __designer:wfdid="w45"></asp:ImageButton>&nbsp;</TD></TR></TBODY></TABLE>
</ItemTemplate>
</asp:TemplateField>
</Columns>

<AlternatingRowStyle BackColor="WhiteSmoke"></AlternatingRowStyle>
</asp:GridView>


 'Code Behind..........................................
'On Load page--------------------------------------------------------
 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then

                If IsNothing(ViewState("Quote")) Then
                    MakeTableQuote()
                    btnAddMoreQuote_Click(sender, TryCast(e, System.Web.UI.ImageClickEventArgs))
                End If

  End If
End Sub

 Protected Sub MakeTableQuote()
        Dim dt As New DataTable
        Dim dc As DataColumn
        dc = New DataColumn("Quote_srno", System.Type.GetType("System.Int32"))
        dt.Columns.Add(dc)
        dc = New DataColumn("Quote", System.Type.GetType("System.String"))
        dt.Columns.Add(dc)
        dc = New DataColumn("QuoteBy", System.Type.GetType("System.String"))
        dt.Columns.Add(dc)
        dc = New DataColumn("PostUserId", System.Type.GetType("System.String"))
        dt.Columns.Add(dc)

        dc = New DataColumn("PostUserNm", System.Type.GetType("System.String"))
        dt.Columns.Add(dc)

        dc = New DataColumn("PostEmpNo", System.Type.GetType("System.String"))
        dt.Columns.Add(dc)

        ViewState("Quote") = dt
    End Sub


    Protected Sub btnAddMoreQuote_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim dt As DataTable = ViewState("Quote")
        Dim dr As DataRow
        For i As Integer = 0 To GridQuote.Rows.Count - 1

            dt.Rows(i).Item(1) = DirectCast(GridQuote.Rows(i).FindControl("txtQuote"), UserControl.TextBox).Text
            dt.Rows(i).Item(2) = DirectCast(GridQuote.Rows(i).FindControl("txtQuoteBy"), TextBox).Text
            dt.Rows(i).Item(3) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).UserId
            dt.Rows(i).Item(4) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).EmpName
            dt.Rows(i).Item(5) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).EmpNo

        Next
        dr = dt.NewRow
        dr.Item(0) = 0
        dr.Item(1) = ""
        dt.Rows.Add(dr)
        ViewState("Quote") = dt
        bindGridQuote()
    End Sub




  

  Protected Sub bindGridQuote()
        Dim dt As DataTable = ViewState("Quote")
        GridQuote.DataSource = dt
        GridQuote.DataBind()

        If HiddenFieldMode.Value = "New" Or HiddenFieldMode.Value = "" Then
            GridQuote.Rows(GridQuote.Rows.Count - 1).FindControl("btnAddMoreQuote").Visible = True
            GridQuote.Rows(GridQuote.Rows.Count - 1).FindControl("btnRemQuote").Visible = False
        Else
            GridQuote.Rows(GridQuote.Rows.Count - 1).FindControl("btnAddMoreQuote").Visible = False
            GridQuote.Rows(GridQuote.Rows.Count - 1).FindControl("btnRemQuote").Visible = False
        End If
       
        'For i As Integer = 0 To GridQuote.Rows.Count - 2
        '    GridQuote.Rows(i).FindControl("btnAddMoreQuote").Visible = False
        'Next
    End Sub





  Protected Sub btnRemQuote_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim row As GridViewRow = TryCast(DirectCast(sender, ImageButton).Parent.Parent, GridViewRow)
        'lblMsg.Text = row.RowIndex

        Dim dt As DataTable = ViewState("Quote")
        Dim dr As DataRow
        For i As Integer = 0 To GridQuote.Rows.Count - 1

            dt.Rows(i).Item(1) = DirectCast(GridQuote.Rows(i).FindControl("txtQuote"), UserControl.TextBox).Text
            dt.Rows(i).Item(2) = DirectCast(GridQuote.Rows(i).FindControl("txtQuoteBy"), TextBox).Text
            dt.Rows(i).Item(3) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).UserId
            dt.Rows(i).Item(4) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).EmpName
            dt.Rows(i).Item(5) = DirectCast(GridQuote.Rows(i).FindControl("CgSearchEmp1"), UserControl.cgSearchEmp).EmpNo

        Next

        dt.Rows(row.RowIndex).Delete()
        ViewState("Quote") = dt
        bindGridQuote()
    End Sub

Split function for Sql Server

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END