Last Update SP List Date Wise in MS Sql

Select name,modify_date,create_date
FROM sys.procedures
order By modify_date desc

MultiValues Function in MS Sql

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END

Get Name On Code With Comma Separate in MS Sql

CREATE FUNCTION [dbo].[GetNameOnCode]
(
@StrCodes NVARCHAR(MAX)='C-05465,C-00535,C-00538,C-03865,C-06377'

)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @Separator CHAR(1)
DECLARE @SeparatorPosition INT
DECLARE @strCode NVARCHAR(50)
SET @Separator = ','
DECLARE @NewStr NVARCHAR(MAX)
DECLARE @NewName NVARCHAR(MAX)=''

SET @StrCodes=@StrCodes+','
--SELECT @StrCodes

WHILE PATINDEX('%' + @Separator + '%', @StrCodes) <> 0
BEGIN
SET @SeparatorPosition = PATINDEX('%' + @Separator + '%', @StrCodes)
SET @strCode = LEFT(@StrCodes, @SeparatorPosition - 1)

--SELECT @strCode

SET @NewStr=ISNULL((SELECT TOP 1 ISNULL(CustName,'') FROM CustomerMaster WHERE LTRIM(RTRIM(No))=LTRIM(RTRIM(@strCode))),'')
SET @NewName=@NewName+@NewStr+','

SET @StrCodes = STUFF(@StrCodes, 1, @SeparatorPosition, '')

END

RETURN @NewName
END

Get Month No Scalar Valued Function MS Sql

ALTER FUNCTION [dbo].[GetMonthNo]
(
@MonthName NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

RETURN(CASE @MonthName
   WHEN 'january' THEN 1
   WHEN 'february' THEN 2
   WHEN 'march' THEN 3
   WHEN 'april' THEN 4
   WHEN 'may' THEN 5
   WHEN 'june' THEN 6
   WHEN 'july' THEN 7
   WHEN 'august' THEN 8
   WHEN 'september' THEN 9
   WHEN 'october' THEN 10
   WHEN 'november' THEN 11
   WHEN 'december' THEN 12
   ELSE  '' END)

END

Insert All Data From One Table To Other

TRUNCATE TABLE [dbo].[StateMst]

INSERT INTO [dbo].[StateMst]
(
[Code],[Name]
)
SELECT [Code],
      [Description]    
FROM [dbo].[StateMaster]

Cursor in MsSql

ALTER PROCEDURE [dbo].[UpdateStateFromTable1toTable2]
AS

DECLARE @Code NVARCHAR(250)
DECLARE @Name NVARCHAR(450)

DECLARE @StateCodeCount NVARCHAR(50)

DECLARE cur_emp CURSOR
STATIC FOR
SELECT [Code],[Description]
FROM [dbo].[State]
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
FETCH NEXT FROM cur_emp INTO @Code,@Name
WHILE @@Fetch_status = 0
BEGIN

SET @StateCodeCount=''
SET @StateCodeCount=ISNULL((SELECT TOP 1 ISNULL(Code,'')
FROM [dbo].[State_Mst]
WHERE LOWER(Code)=LOWER(@Code)),'')


IF(LEN(@StateCodeCount)=0)
BEGIN
INSERT INTO [dbo].[State_Mst]
(
[Code],[Name]
)
VALUES
(
@Code,@Name
)
END
ELSE
BEGIN
UPDATE [dbo].[State_Mst]
SET [Name]=@Name
WHERE LOWER(Code)=LOWER(@Code)
END
FETCH NEXT FROM cur_emp INTO  @Code,@Name
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Stored Procedure Paging Wise Row

CREATE PROCEDURE [dbo].[SiteErrorSelectAll]
(
@Search VARCHAR(MAX)='',
@PageIndex INT=1,
@PageSize INT=5,
@SortExp NVARCHAR(MAX)='ErrorIDDESC',
@IPAddress VARCHAR(50)='',
@Browser VARCHAR(50)='',
@WebURL VARCHAR(1024)='',
@UserMasterID VARCHAR(100)='36'
)
AS
BEGIN TRANSACTION
BEGIN TRY

SELECT Top(@PageSize)
ErrorID, ErrorCode, ExceptionMessage, ExceptionStackTrace,
[Source], IPAddress, Browser, [Description],
WebURL, UserName,    ModifiedON, TotalRows
FROM
( SELECT SE.ErrorID,
SE.ErrorCode,
SE.ExceptionMessage,
SE.ExceptionStackTrace,
SE.[Source],
SE.IPAddress,
SE.Browser,
SE.[Description],
SE.WebURL,
UM.UserName,
CONVERT(NVARCHAR(50),SE.ModifiedON,103) AS ModifiedON,
(
ROW_NUMBER() OVER
(
ORDER BY
CASE WHEN @SortExp='ErrorIDASC' THEN SE.ErrorID END ASC,
CASE WHEN @SortExp='ErrorIDDESC' THEN SE.ErrorID END DESC,
CASE WHEN @SortExp='ErrorCodeASC' THEN SE.ErrorCode END ASC,
CASE WHEN @SortExp='ErrorCodeDESC' THEN SE.ErrorCode END DESC
)
) AS RowNumber,
TotalRows=Count(*) OVER()
FROM SiteError SE
LEFT JOIN UserMaster UM ON UM.UserMasterID=SE.ModifiedBY
WHERE
(
LOWER(SE.ErrorCode) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ExceptionMessage) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ExceptionStackTrace) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.[Source]) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.IPAddress) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.Browser) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.[Description]) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.WebURL) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(UM.UserName) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ModifiedON) LIKE '%'+LOWER(@Search)+'%'
)
) AS ErrorWithRowNumber
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1
AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1


END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

BEGIN TRANSACTION siteerror

Declare @ErrorCode VARCHAR(50), @ExceptionMessage varchar(max), @Source varchar(512),@ErrorDesc varchar(max)

set @ErrorCode=(select CAST(ISNULL(ERROR_NUMBER(),'') AS VARCHAR))
set @Source=(select ISNULL(ERROR_PROCEDURE(),''))
set @ExceptionMessage=(select ISNULL(ERROR_MESSAGE(),''))
set @ErrorDesc=(select 'Severity : ' + CAST(ISNULL(ERROR_SEVERITY(),'') AS VARCHAR(50)) + ' State : ' + CAST(ISNULL(ERROR_STATE(),'') AS VARCHAR(50)) + ' Line : ' + CAST(ISNULL(ERROR_LINE(),'') AS VARCHAR(50)))

EXECUTE [dbo].[SiteErrorInsert]
@ErrorCode,@ExceptionMessage,'',@Source,@IPAddress,@Browser,@ErrorDesc,@WebURL,@UserMasterID

COMMIT TRANSACTION siteerror

END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION

Remove Unicode Characters From String

private static Regex _compiledUnicodeRegex = new Regex(@"[^\u0000-\u007F]", RegexOptions.Compiled);

        public static String StripUnicodeCharactersFromString(string inputValue)
        {
            return _compiledUnicodeRegex.Replace(inputValue, String.Empty);
        }

Remove weird Character From string in Sql

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@CardID, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

Date Filter With From And To Date in MsSql

DECLARE @FromDate datetime
Declare @ToDate datetime

SET @FromDate='06/01/2017'
SET @ToDate='07/18/2017'

SELECT *
FROM SiteActivity SA
LEFT JOIN Users CU ON CU.userCode=SA.ModifiedBy
Where CAST(SA.ModifiedON AS DATE) BETWEEN CAST(@FromDate AS VARCHAR(20)) AND CAST(@ToDate AS VARCHAR(20))

Asp.Net Get (Access) Session Values in JavaScript (Client Side)

JavaScript or jQuery Code

<script type="text/javascript">

$(function() {

var name = 'Welcome '+' <%=Session["UserName"] %>'

$('#lbltxt').text(name)

});

</script>

Default.aspx

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<title>Asp.Net Access Session Variable Value in JavaScript or jQuery</title>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>

<script type="text/javascript">

$(function() {

var name = 'Welcome '+' <%= Session["UserName"] %>'

$('#lbltxt').text(name)

});

</script>

</head>

<body>

<form id="form1" runat="server">

<div>

<label id="lbltxt" />

</div>

</form>

</body>

</html>

C# Code

using System;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Session["UserName"] = "Guest";
}

}

Use Of Coalesce

Declare @ID Nvarchar(max)

select @ID = COALESCE(CAST(@ID AS VARCHAR(50)) + ', ','') + CAST(ExpenseCategoryID  AS VARCHAR(50))
from   (select distinct ID
        from   TableName
        ) CategoryID

SELECT @ID AS CategoryID

set and get the innerHTML of the Label and DIV control using jQuery or JavaScript

<html>

<head>

    <title></title>

    <head>

        <script type="text/javascript" src="jquery1.8.3-min.js"></script>

        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

        <script type="text/javascript">

            // get and set using jquery

            $(document).ready(function () {

                $("#jq1").html("Div is Fill.");

                var title = $('#jq1').html();

                alert(title);

                // get and set using javascript

                // Get the text content:

                alert(document.getElementById("je1").textContent);

                // Set the text content:

                document.getElementById("je1").textContent = "This is text";

            });

          

        </script>

    </head>

    <body>

        <div id="jq1">

            jQuery DIV

        </div>

        <div id="je1">

            JavaScript DIV

        </div>

    </body>

</html>

Calculate Age Based On BirthDate

function SetAge(birthdate) {

            var birthd = Xrm.Page.getAttribute("birthdate").getValue();

            if (birthd == null) {

                return;

            }

            var today = new Date().getFullYear();

            year1 = birthd.getFullYear();

            Xrm.Page.getAttribute("new_age").setValue(today - year1);

        }

obtain longitude and latitude On address

var geocoder = new google.maps.Geocoder();

var address = "Ahmedabad";

geocoder.geocode( { 'address': address}, function(results, status) {

var latitude = results[0].geometry.location.lat();

var longitude = results[0].geometry.location.lng();

alert(latitude+" and "+longitude);

  }

});

Get selected text from a drop-down list (select box) using jQuery

<%@ Page Language="C#" AutoEventWireup="true"CodeFile="drpGetText.aspx.cs" Inherits="drpGetText" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<script type="text/javascript"src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function() {

    $("#submit").click(function () {

        alert("selected Text =" + $('#ddlCountry option:selected').text() + "  And Value = " + $('#ddlCountry option:selected').val());

    });

});

 

</script>

    <title>JQuery get dropdown selected value and text in asp.net</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:DropDownList ID="ddlCountry" runat="server">

            <asp:ListItem Value="1">India</asp:ListItem>

            <asp:ListItem Value="2">China</asp:ListItem>

            <asp:ListItem Value="3">Pakistan</asp:ListItem>

            <asp:ListItem Value="4">USA</asp:ListItem>

            <asp:ListItem Value="5">UK</asp:ListItem>

        </asp:DropDownList>

    </div>

    <input type="button" value="Click" id="submit" />

    </form>

</body>

</html>