.Net, SQL, Oracle,Sharepoint, Android, BB-CRM, Salesforce.com and MS Dyanamics CRM Tips and tricks: 2011

Thursday 22 December 2011

Joins in LINQ to SQL

The following post shows how to write different types of joins in LINQ to SQL. I am using the My Sample database and LINQ to SQL for these examples

SampleDbDataContext dataContext = new SampleDbDataContext();

Inner Join :-

var q1 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
c.CommentsId,
c.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
INNER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

Left Join :-

var q2 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId into g
from s in g.DefaultIfEmpty()
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
s.CommentsId,
s.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
LEFT OUTER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

Wednesday 21 December 2011

LinkedIn button on profile page

Something you may want to do, if you have a LinkedIn account (it's free!) is to add a button in your webpage or blog to make it easier for people to see your LinkedIn profile.

Here's how to find the button and code to paste into your site.

1. Go to your Profile page in LinkedIn
2. Click "My Profile"
3. Click on "Edit Public Profile Settings" (on the top right of the page)
4. Click "Promote your profile with customized buttons"
5. Pick a button, copy the code

or easier yet: http://www.linkedin.com/profile?promoteProfile=

Also, make sure your profile is not set to Private. Make it viewable to the world.

For users of Blogger, where to place the button.

In Blogger, you have to go to the Layout tab, click on "Add a Gadget" and add an "HTML/JavaScript" section where you paste the code. Title section is not mandatory.

Wednesday 28 September 2011

How to find a url of Active tab from Active Internet Explore Instance

First Add new class name as IEInstance.cs

The code for this class

using System;
using System.Drawing;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections;
public delegate bool IECallBack(int hwnd, int lParam);
namespace IEToolbarEngine
{
public class IEInstance//Form is a class in System.Windows.Forms namespace
{
[DllImport("user32.Dll")]
public static extern int EnumWindows(IECallBack x, int y);
[DllImport("User32.Dll")]
public static extern void GetWindowText(int h, StringBuilder s, int nMaxCount);
[DllImport("User32.Dll")]
public static extern void GetClassName(int h, StringBuilder s, int nMaxCount);
[DllImport("User32.Dll")]
public static extern IntPtr PostMessage(IntPtr hWnd, int msg, int wParam, int lParam);

public static ListBox lbPublic;

private ListBox listBox1; //ListBox is a class in System.Windows.Forms namespace
static IntPtr listBoxHandle;// IntPtr is a class in System namespace
static IntPtr windowHandle;
static StringBuilder sb, sbc;
static int i = 0;
static ArrayList myAl;

public IEInstance() //Constructor
{
InitializeComponent();
}

#region Windows Form Designer generated code
private void InitializeComponent()
{
// this.GetIE = new System.Windows.Forms.Button();
this.listBox1 = new System.Windows.Forms.ListBox();
myAl = new ArrayList();
}
#endregion

public void GetIEButton()
{
listBoxHandle = listBox1.Handle;
EnumWindows(new IECallBack(IEInstance.EnumWindowCallBack), (int)listBoxHandle);

}

private static bool EnumWindowCallBack(int hwnd, int lParam)
{
windowHandle = (IntPtr)hwnd;
listBoxHandle = (IntPtr)lParam;
lbPublic = (ListBox)ListBox.FromHandle(listBoxHandle);
sb = new StringBuilder(1024);
sbc = new StringBuilder(256);
GetClassName(hwnd, sbc, sbc.Capacity);
GetWindowText((int)windowHandle, sb, sb.Capacity);
int ind = sb.ToString().IndexOf("- Microsoft");
if (ind ==-1)
ind = sb.ToString().IndexOf("- Windows");
String xMsg2 = string.Empty;
if (ind != -1)
xMsg2 = sb.ToString().Substring(0,ind - 0) + " " + windowHandle;
else
xMsg2 = sb + " " + windowHandle;
String xMsg = windowHandle.ToString();
if (sbc.Length > 0)
{
if (sbc.ToString().Equals("IEFrame"))
{
myAl.Add(windowHandle);
i++;
if (lbPublic.Items.Count < 1)
lbPublic.Items.Add(xMsg2);
}
}
return true;
}

}
}

------
I have inherited this class with form class .In "lbPublic" .This is a ListBox type . After executing this class your lbPublic will fill the active tab title + handler of the tab. You will just add this whole code in your class file ,no need to extent more.

Now , for the sample Add a form in which you will call the above class method GetIEButton();
In a form include a COM component reference MICROSOFT HTML OBJECT LIBRARY.

After adding reference include Add the below line the using area
using mshtml;

Now in the form Load method

private void Form1_Load(object sender, EventArgs e)
{

//// Below is the IEInstance class object
IEInstance ieButton = new IEInstance();
ieButton.GetIEButton();

SHDocVw.ShellWindows shellWindows = new SHDocVw.ShellWindowsClass();
string filename = string.Empty; ;

foreach (SHDocVw.InternetExplorer ie in shellWindows)
{
filename = Path.GetFileNameWithoutExtension(ie.FullName).ToLower();
mshtml.IHTMLDocument2 htmlDoc = ie.Document as mshtml.IHTMLDocument2;
if (htmlDoc != null)
{
string strIe = htmlDoc.title + " " + ie.HWND.ToString();
//htmlDoc = (HTMLDocument)axWebBrowser1.Document;
if (filename.Equals("iexplore"))
{

if (IEInstance.lbPublic.Items[0].ToString() == strIe)
{
filename = ie.LocationURL;

// break;
goto NewLine;
}

}
}

//Console.WriteLine( "Web Site : {0}", ie.LocationURL );

if (filename.Equals("explorer""))
Console.WriteLine("Hard Drive : {0}", ie.LocationURL);
}

NewLine:
int i2 = 0;
MessageBox.Show(filename);

}

This is in working state .now you will get thr url of active tab of active Internet Explorer .

Wednesday 13 July 2011

START WITH and CONNECT BY in Oracle SQL

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table tblActivity (

id number primary key,

parent_id references tblActivity, -- references itself

descr varchar2(20),

);

This table is now filled with some values:

-- 1st level:

insert into tblActivity values ( 1, null, 'Yahoo' , 1);

-- 2nd level:

insert into tblActivity values ( 2, 1, 'Entertainment' , 1);

insert into tblActivity values ( 3, 1, 'Science' , 1);

insert into tblActivity values ( 4, 1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into tblActivity values ( 5, 2, 'Awards' , 1);

insert into tblActivity values ( 6, 2, 'Comedy' , 1);

insert into tblActivity values ( 7, 2, 'Humor' , 0);

insert into tblActivity values ( 8, 2, 'Magic' , 1);

-- 4th level (below Humor)

insert into tblActivity values (19, 7, 'Jokes' , 1);

insert into tblActivity values (20, 7, 'Advice' , 1);

insert into tblActivity values (21, 7, 'Parody' , 1);

-- 4th level (below Magic)

insert into tblActivity values (16, 8, 'Card Tricks' , 1);

insert into tblActivity values (17, 8, 'Magazines' , 1);

insert into tblActivity values (18, 8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into tblActivity values ( 9, 3, 'Astronomy' , 1);

insert into tblActivity values (10, 3, 'Biology' , 1);

insert into tblActivity values (11, 3, 'Geography' , 1);

insert into tblActivity values (12, 3, 'Physics' , 1);

insert into tblActivity values (13, 3, 'Research' , 1);

-- 4th level (below Astronomy)

insert into tblActivity values (22, 9, 'Galaxies' , 1);

insert into tblActivity values (23, 9, 'Pictures' , 1);

insert into tblActivity values (24, 9, 'Stars' , 1);

-- 4th level (below Physics)

insert into tblActivity values (25, 12, 'Electricity' , 1);

insert into tblActivity values (26, 12, 'Mechanics' , 1);

-- 3rd level (below Social Science)

insert into tblActivity values (14, 4, 'Psychology' , 1);

insert into tblActivity values (15, 4, 'Sociology' , 1);

-- 4th level (below Psychology)

insert into tblActivity values (27, 14, 'Conferences' , 1);

insert into tblActivity values (28, 14, 'Intelligence' , 1);

--Now Select

select * from   tblActivity

start with id = 3
connect by prior id = parent_id;

It return the below mention record

Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research

Tuesday 12 July 2011

Asp.net Datalist item fetches using javascript‏

string scr = @"<script>
function Done()
{

var Hdnval;
var HdnControl;
var foo = document.getElementById('dtlImages');
var inps = foo.getElementsByTagName('input');
var inpsmage = foo.getElementsByTagName('img');
for(var i=0;i<inps.length;i++){
if(inps[i].type=='checkbox')
{
if (inps[i].checked== true)
{
HdnControl = inpsmage[i];
Hdnval = inpsmage[i].src;
}
}}
var fName=Hdnval;
var ret= new Array(fName,HdnControl);
//SetPreviewElements(HdnControl,HdnControl)
window.opener.update(ret);
window.close();
}
</script>;";
ClientScript.RegisterClientScriptBlock(this.GetType(), "done", scr);

Dynamic SQL Query with Sql Paging‏

Create PROC DBO.Article_SelectByCriteria_BySqlPaging

-- Parameters of Article_SelectByCriteria--------------
@P_GUID                     VARCHAR(40)='' , -- OPTIONAL
@P_parentArticleId          INT = 0 , --OPTIONAL
@P_postingURL               nVARCHAR(200) = '' , --OPTIONAL
@P_authorId                 INT = 0, --OPTIONAL
@P_elaphObjectId            INT =0, --OPTIONAL
@P_assignedBy     BIGINT=0, --OPTIONAL
@P_assignedTo               BIGINT= 0 , --OPTIONAL
@P_assignedDate             VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_publishedBy              BIGINT = 0, -- OPTIONAL
@P_publishedDate            VARCHAR(50) = '12:00:00 AM' ,-- OPTIONAL
@P_lastModifiedBY           BIGINT = 0 , -- OPTIONAL
@P_lastModifiedDate        VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_archivedBy              BIGINT = 0, --OPTIONAL
@P_archivedDate            VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_expirationDate          VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_CountryId               TINYINT = 0 , --OPTIONAL
@P_IsSectionPageNewsTicker BIT = 0 , --OPTIONAL
@P_isHomePageNewsTicker    BIT = 0 , --OPTIONAL
@P_isPrinterFriendly       BIT = 0 , --OPTIONAL
@P_isSendToFriend          BIT = 0 , --OPTIONAL
@P_isAddToFavorite         BIT = 0, --OPTIONAL
@P_isWriteToAuthor         BIT = 0 , --OPTIONAL
@P_isSubscribedArticle     BIT = 0 , --OPTIONAL
@P_isDisablePrintCutPaste BIT = 0, --OPTIONAL
@P_IsCopyRight             BIT = 0 , --OPTIONAL
@P_IsArticleRating         BIT = 0 , --OPTIONAL
@P_IsComments              BIT = 0 , --OPTIONAL
@P_articleStatusId         INT = 0, --OPTIONAL
@P_articleTypeId           TINYINT = 0 ,--OPTIONAL
@P_createdBy               BIGINT = 0 , --OPTIONAL

@PageSize int, @PageNumber int

WITH RECOMPILE

AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max),    -- SQL statement to execute
@ubound int

Declare @RowStart int
Declare @RowEnd int

if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
--IF @start < 1 SET @start = 1
--IF @maxct < 1 SET @maxct = 1
-- SET @ubound = @start + @maxct

DECLARE @L_WhereClause VARCHAR(8000)
DECLARE @L_SQL Varchar(8000)
SET @L_WhereClause = ''
SET @L_SQL = ''

If @P_parentArticleId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ParentArticleId =' + CAST(@P_parentArticleId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ParentArticleId =' +   CAST(@P_parentArticleId AS VARCHAR(20))
            END

    IF @P_GUID <> ''
             BEGIN
    IF @L_WhereClause <> ''
                     SET @L_WhereClause = @L_WhereClause + ' AND Article.GUID =' + ''''+ @P_GUID + ''''
                  ELSE
                     SET @L_WhereClause = @L_WhereClause + ' Article.GUID =' + ''''+ @P_GUID + ''''
      END


    IF @P_postingURL <> ''
             BEGIN
    IF @L_WhereClause <> ''
                     SET @L_WhereClause = @L_WhereClause + ' AND Article.PostingURL =' + ''''+ @P_postingURL + ''''
                  ELSE
                     SET @L_WhereClause = @L_WhereClause + ' Article.PostingURL =' + ''''+ @P_postingURL + ''''
      END

    If @P_authorId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AuthorId =' + CAST(@P_authorId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AuthorId =' +   CAST(@P_authorId AS VARCHAR(20))
            END
      If @P_elaphObjectId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ElaphObjectId =' + CAST(@P_elaphObjectId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ElaphObjectId =' +   CAST(@P_elaphObjectId AS VARCHAR(20))
            END
If @P_assignedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedBy =' + CAST(@P_assignedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedBy =' +   CAST(@P_assignedBy AS VARCHAR(20))
            END
If @P_assignedTo <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedTo =' + CAST(@P_assignedTo AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedTo =' +   CAST(@P_assignedTo AS VARCHAR(20))
            END

       If @P_assignedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedDate =' + '''' + @P_assignedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedDate =' +   '''' + @P_assignedDate + ''''
            END

        If @P_publishedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedBy =' + CAST(@P_publishedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.PublishedBy =' +   CAST(@P_publishedBy AS VARCHAR(20))
            END
     -- If @P_publishedDate <> '12:00:00 AM'

--    BEGIN
           --      IF @L_WhereClause <> ''

--      SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedDate =' + '''' + @P_publishedDate + ''''
               -- ELSE
        --       SET @L_WhereClause = @L_WhereClause + ' Article.PublishedDate =' +   '''' + @P_publishedDate + ''''
          -- END

If @P_lastModifiedBY <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedBY =' + CAST(@P_lastModifiedBY AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedBY =' +   CAST(@P_lastModifiedBY AS VARCHAR(20))
            END
         If @P_lastModifiedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedDate =' +   '''' + @P_lastModifiedDate + ''''
            END
      If @P_archivedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedBy =' + CAST(@P_archivedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedBy =' +   CAST(@P_archivedBy AS VARCHAR(20))
            END

          If @P_archivedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedDate =' + '''' + @P_archivedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedDate =' +   '''' + @P_archivedDate + ''''
            END

         If @P_expirationDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ExpirationDate =' + '''' + @P_expirationDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ExpirationDate =' +   '''' + @P_expirationDate + ''''
            END

        If @P_CountryId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.CountryId =' + CAST(@P_CountryId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.CountryId =' +   CAST(@P_CountryId AS VARCHAR(20))
            END

        If @P_IsSectionPageNewsTicker <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSectionPageNewsTicker =' + CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsSectionPageNewsTicker =' +   CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
            END

        If @P_isHomePageNewsTicker <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsHomePageNewsTicker =' + CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsHomePageNewsTicker =' +   CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
            END

      IF @P_isPrinterFriendly <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.isPrinterFriendly =' + CAST(@P_isPrinterFriendly AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.isPrinterFriendly =' +   CAST(@P_isPrinterFriendly AS VARCHAR(20))
            END

IF @P_isSendToFriend <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.isSendToFriend =' + CAST(@P_isSendToFriend AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.isSendToFriend =' +   CAST(@P_isSendToFriend AS VARCHAR(20))
            END

         IF @P_isAddToFavorite <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsAddToFavorite =' + CAST(@P_isAddToFavorite AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsAddToFavorite =' +   CAST(@P_isAddToFavorite AS VARCHAR(20))
            END
   IF @P_isWriteToAuthor <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsWriteToAuthor =' + CAST(@P_isWriteToAuthor AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsWriteToAuthor =' +   CAST(@P_isWriteToAuthor AS VARCHAR(20))
            END
   IF @P_isSubscribedArticle <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSubscribedArticle =' + CAST(@P_isSubscribedArticle AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsSubscribedArticle =' +   CAST(@P_isSubscribedArticle AS VARCHAR(20))
            END

IF @P_isDisablePrintCutPaste <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsDisablePrintCutPaste =' + CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsDisablePrintCutPaste =' +   CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
            END
IF @P_IsCopyRight <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsCopyRight =' + CAST(@P_IsCopyRight AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsCopyRight =' +   CAST(@P_IsCopyRight AS VARCHAR(20))
            END
IF @P_IsArticleRating <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsArticleRating =' + CAST(@P_IsArticleRating AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsArticleRating =' +   CAST(@P_IsArticleRating AS VARCHAR(20))
            END
IF @P_IsComments <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsComments =' + CAST(@P_IsComments AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsComments =' +   CAST(@P_IsComments AS VARCHAR(20))
            END




          IF @P_articleStatusId <> 0

     BEGIN
                 IF @P_articleStatusId = 1
                    BEGIN
               IF @L_WhereClause <> ''

          SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' +
CAST(getdate() AS VARCHAR(50)) + ''''
                    ELSE
                 SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' + CAST(getdate() AS VARCHAR(50)) + ''''

                    END
     ELSE
     BEGIN
    IF @L_WhereClause <> ''

         SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20))
                  ELSE
                SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))
             END
    END



IF @P_articleTypeId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleTypeId =' + CAST(@P_articleTypeId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArticleTypeId =' +   CAST(@P_articleTypeId AS VARCHAR(20))
            END

      IF @P_createdBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.CreatedBy =' + CAST(@P_createdBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.CreatedBy =' +   CAST(@P_createdBy AS VARCHAR(20))
            END

      IF @L_WhereClause = ''

        SET @L_WhereClause = 'where row >= ' + CONVERT(varchar(9), @RowStart ) + ' AND
                row <= ' + CONVERT(varchar(9),@RowEnd)


SET @STMT = ' SELECT TOP 500 *
                FROM (
                      SELECT ROW_NUMBER() OVER(ORDER BY PublishedDate Desc) AS row, ArticleId, PublishedDate
                      FROM    Article
                     ) AS tbl
                '+@L_WhereClause+' '

EXEC (@STMT)

end

GO

//// Now You can execute the SP
Article_SelectByCriteria_BySqlPaging
@PageSize = 15,
@PageNumber = 11

.Net, SQL, Oracle,Sharepoint, Android, BB-CRM, Salesforce.com and MS Dyanamics CRM Tips and tricks