dynamin sql query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

SQL

WITH MEM AS
(
    SELECT ROW_NUMBER() OVER ( ORDER BY PRODUCTID) AS ROWNUMBER,PRODUCTNAME FROM PRODUCTS
)

SELECT * FROM MEM ORDER BY ROWNUMBER DESC

I want to make a stored procedure. This block is a part of that procedure. Can any one please tell me how can I make this select query dynamic? Query I will pass from asp.net.

Thanks in advance.

Posted 30-Aug-10 6:21am

hiren soni

Updated 30-Aug-10 6:42am

Yusuf

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Al-Farooque Shubho · Answer 1 · 2010-08-30T07:36:00

Solution 1

I guess you actually need to know how to write a Stored Procedure for Pagination in SQL Server 2005 using Dynamic SQL (Dynamic SQL is required because you would need to pass Page number, Page size, Order By field and Order Direction (Desc or Asc)

If that is so, you can have a look at This Article[^] to learn how to write pagination queries using Dynamic SQL in SQL Server.

Posted 30-Aug-10 7:36am

Al-Farooque Shubho

Updated 30-Aug-10 8:31am

v2

Comments

hiren soni 30-Aug-10 13:46pm

NO dear I already know how to write a stored procedure, whatever parameters u told me I have already written a stored procedure using those parameters. But in my application there are more than 10 pages where I need paging. I dont want to write 10 stored procedure for that. So I am trying to take a query as a parameter. So with one stored procedure only I can Pass multiple query and use it in N times.

Al-Farooque Shubho 30-Aug-10 14:33pm

The link I have provided has a "Generic stored procedure" that you can use to get paginated data for any table from SQL Server. I didn't try it but hopefully this is something you need. Best of luck.

Al-Farooque Shubho · Answer 2 · 2010-08-30T08:31:00

Solution 2

You please take a look at this article : http://www.15seconds.com/issue/070628.htm[^]. This has a "Generic" stored procedure that you can use to get paginated data for any table from SQL Server.

Posted 30-Aug-10 8:31am

Al-Farooque Shubho