شکل دیگری از دستور TOP و ایجاد شرایط متنوع در SP

[In_Chan_Nafar]

با سلام و خسته نباشید خدمت دوستان گرامی
عرض به خدمت شما که من می خوام

1- یک SP بنویسم که توش شرایط متفاوت استفاده بشه مثلا 3 تا پارامتر دارم که احتمال داره یکی از اونا در محدود کردن اطلاعات استفاده بشه و یا نه (بسته به نوع انتخاب کاربر داره) حتی من به پارامترها هم مقدار NULL رو میدم ولی باز هم تاثیری ندارد.چون ما در شرظ از AND استفاده کرده ایم و این باعث میشه که اطلاعاتی بدست نیاید.
ALTER PROCEDURE [dbo].[SPLogFileGetByParams]
@_IDUser int,
@_IDGroup int,
@_ActionDate1 varchar(10),
@_ActionDate2 varchar(10),
@_Action varchar(50)

AS
BEGIN
SELECT
LF.[ID],
U.[RealName] + ' ' + U.[RealFamily],
G.[GroupName],
LF.[ActionDate],
LF.[ActionTime],
A.[Action]
FROM TBLogFile LF
INNER JOIN TBUsers U ON U.[ID] = LF.[IDUser]
INNER JOIN TBGroupUsers G ON G.[ID] = U.[IDGroup]
INNER JOIN TBAction A ON A.[ID] = LF.[IDAction]
WHERE
LF.[IDUser] = @_IDUser AND U.[IDGroup] = @_IDGroup AND
LF.[ActionDate] BETWEEN @_ActionDate1 AND @_ActionDate2
AND LF.[Action] IN (@_Action)

END

2- می خوام یک دستور SQL بنویسم که اطلاعات یک جدول رو به صورت Page به Page یعنی مثلا 10 تا 10 تا نشون بده. به عنوان مثال اگر 100 رکورد در جدول داشته باشیم و کاربر در سطح فرم از کومبوباکس یک تا 10 را انتخاب نماید 10 تای اولی را برای ما نشان دهد و اگر 11 تا 20 را انتخاب نماید رکوردهای از 11 تا 20 نمایش داده شود. و الی آخر ...

مچکر از تمامی عزیزان

[Mohammad_Mnt]

اگه درست متوجه شده باشم می خوای وقتی یه فیلد ( مثلا LF.ActionDate ) نول بود شرطی که گذاشتی True باشه. این طوری تغییرش بده :


ISNULL( LF.[ActionDate], @_ActionDate1) BETWEEN @_ActionDate1 AND @_ActionDate2

[In_Chan_Nafar]

ببینید:
ما در سطح فرم 5 تا پارامتر به SP ارسال می کنیم. حالا کاربر احتمال داره که از این 5 تا پارامتر یکی را ارسال و 4تای دیگه رو انتخاب نکنه. مثلا کاربر در سطح فرم می خواهد گزارش LogFile گروه کاری Users را مشاهده نماید پس ما فقط پارامتر ID گروه کاری را به SP ارسال نموده و دیگر پارامترها (4تای دیگر) NULL خواهند بود همچنان که در SP مشاهده می نمائید ما تمام پارامترها را در شرط WHERE با یکدیگر AND نموده ایم. حالا چون شرط AND شده و یک پارامتر مقدار و 4تا پارامتر دیگر مقدار ندارند بنابراین هیچ خروجی در این SP نخواهیم داشت.
حالا ما چی کار کنیم که اگه پارامترها NULL بودند (البته حداقل یک پارامتر انتخاب می شه) در خروجی تاثیری نداشته باشد

لطفا سوال دوم رو هم جواب بدید مچکرم

[In_Chan_Nafar]

سوال اول حل شد لطفا در مورد سوال دوم کمک کنید.
با تشکر از آقای ثباتی عزیز و بقیه سروران

[Hamid.Kad]

برای مورد اول شرطها رو اینجوری بنویسید :
LF.[IDUser] = @_IDUser OR LF.[UserId] Is Null

برای مورد دوم میتونید به روش زیر اون رو پیاده سازی کنید

Select top 100 * into #a from Table1 order by FieldName
Select top 10 * from #a order by FieldName desc
drop table #a

اگه دقت کنید این دستورات رکوردهای با شماره 90 تا 100 رو بر میگردونه

[In_Chan_Nafar]

دوست عزیز سوال اول من با توجه به این مقاله زیر که آقای ثباتی گذاشته بودن حل شد

http://www.sommarskog.se/dyn-search.html

در مورد سوال دوم Query شما خیلی کند عمل می کنه آیا روش بهینه تری هست؟

[Hamid.Kad]

حداقل من روش دیگه ای به ذهنم نمی رسه
من این کوئری رو روی یه جدول با 120000 رکورد اعمال کردم (مقدار عدد اول رو 60000 گذاشتم ) 6 ثانیه طول کشید وقتی تعداد فیلدها رو کم کردم حدود 2 ثانیه شد که فکر میکنم تقریباً قابل قبول باشه

[In_Chan_Nafar]

در واقع کد شما همیشه اون 10 تای آخر رو برمیگردونه یعنی 90 تا 100 ولی من می خوام که کاربر خودش از ComboBox انتخاب کنه مثلا 1 تا 10 (10 رکورد اول) و 11 تا 20 (10 رکورد دوم) و 21 تا 30 (10 رکورد سوم) و الی آخر ولی کد شما همیشه 10 رکورد آخر رو بر خواهد گردوند.

[Hamid.Kad]

دوست عزیز. این روش ایده اصلی بود. شما برای نشان دادن رکوردهای مختلف می تونید اعداد مورد نظر رو محاسبه و سپس کوئری رو اعمال کنید. مثلاً می تونید عدد اولی رو از حاصلضرب 10 * (Combobox1.ItemIndex+1) بدست بیاورید. عدد دوم رو هم همون 10 قرار بدید. در واقع ثابت 10 تعداد رکوردهایی است که می خواهید نشان داده شود.

[MohammadSoft]

سلام
به نظر من این کار رو سمت Application انجام بدید بهتره ، ولی برای انجامش تو sql می تونید از دستور زیر استفاده کنید :

select top 10 * from table_name where PK in (select top 20 PK from table_name order by PK asc) order by PK desc
برای رکوردهای 31 تا 40 به جای عدد 20 از 40 استفاده کنید .

[Hamid.Kad]

محمد جان. انجام این کار در سمت Application زمان بیشتری لازم داره.
فکر کنم کوئری اول سریعتر جواب بده چون از مرتبه O(n) هست ولی کوئری شما از مرتبه
) O(n^2 میشه

[رضا عربلو]

Getting Rows m to n

If you order the authors in the pubs sample database by author ID, you can use a simple TOP query to ask for
the first five authors. However, if you want the second group of five authorsauthors six to tenthings become a
little bit more complex.
You can use the ANSI−compliant query shown in Listing 17−49. For each author, this query performs a
correlated subquery that calculates the number of authors with author IDs that are smaller than or equal to the
current author ID.
Listing 17−49: Getting Rows m to n in One Query

SELECT
*
FROM
Authors AS A1
WHERE
(
SELECT
COUNT(*)
FROM
Authors AS A2
WHERE
A2.au_id <= A1.au_id
) BETWEEN 6 AND 10
ORDER BY
au_id

Note that this query has poor performance, as it needs to scan the Authors table as many times as there are
authors in the table. This query also requires the column that you order by, in this case the au_id column, to be
unique. We can improve our query's performance by splitting our solution into two steps. First we can place
the authors' rows in a temporary table, along with their ordinal position according to the author ID column.
We can achieve this by using the IDENTITY() function (the IDENTITY() function is discussed in detail in
Chapter 6), as Listing 17−50 shows.
Listing 17−50: Placing the Authors in a Temporary Table Along with Their Ordinal Positions

SELECT
IDENTITY (int, 1, 1) AS rownum,

479

*
INTO
#TmpAuthors
FROM
Authors
ORDER BY
au_id

You can now issue a simple query to retrieve authors six to ten, as Listing 17−51 shows.
Listing 17−51: Retrieving Authors Six to Ten from the Temporary Table

SELECT
*
FROM
#TmpAuthors
WHERE
rownum BETWEEN 6 AND 10

Note that this technique did not always work prior to SQL Server 2000 (tested on SQL Server 7.0, Service
Pack 2). When using the SELECT INTO statement, sometimes the Identity values were calculated prior to the
sort, making this solution improper for the problem at hand. SQL Server 2000 solved this problem, and if you
examine the execution plan of the SELECT INTO statement, you can actually see that a sort is performed
prior to calculating the IDENTITY value. In both versions, however, if you create the temporary table
manually with an additional IDENTITY column and use an INSERT SELECT statement to populate it, the
execution plans show that a sort is performed prior to calculating the IDENTITY values, making this solution
valid for both versions. Hopefully this bug will be resolved in SQL Server 7.0 in one of the next service
packs.
If you want to do everything in a single statement, you can use the TOP feature of the SELECT statement.
First, you need to determine the first ten authors, which you do with a TOP 10, and you ORDER BY au_id
ASC. This SELECT then acts as a derived table from which you can do a TOP 5, this time with ORDER BY
au_id DESC. This gives you the second group of five; however, it is sorted in reverse order to what is desired.
This result is then used as a derived table, where you do a regular SELECT and just sort the rows with au_id
ASC. The solution is presented in Listing 17−52.
Listing 17−52: Retrieving Authors Six through Ten

SELECT
*
FROM
(
SELECT TOP 5
*
FROM
(
SELECT TOP 10
*
FROM
Authors
ORDER BY
au_id ASC
) X
ORDER BY

480

au_id DESC
) Y
ORDER BY
au_id ASC

[In_Chan_Nafar]

WITH TBExample AS
(
SELECT [ID],[Name], ROW_NUMBER() OVER(ORDER BY [ID]) AS RN
FROM TBMain
WHERE [IDParvandeh] = @_IDParvandeh AND IDMadrak!=255
)

SELECT * FROM TBExample
WHERE RN BETWEEN @_mRow AND @_nRow

[AminSobati]

USE Northwind
GO
CREATE PROC PageCustomers
@RowsPerPage INT,
@PageNo INT
AS
WITH Pages AS (
SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS Rank,
* FROM Customers)
SELECT * FROM Pages
WHERE Rank between
(@PageNo*@RowsPerPage) - @RowsPerPage + 1
and (@PageNo*@RowsPerPage)
GO

EXEC PageCustomers 5,2

[رضا عربلو]

USE Northwind
GO
CREATE PROC PageCustomers
@RowsPerPage INT,
@PageNo INT
AS
WITH Pages AS (
SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS Rank,
* FROM Customers)
SELECT * FROM Pages
WHERE Rank between
(@PageNo*@RowsPerPage) - @RowsPerPage + 1
and (@PageNo*@RowsPerPage)
GO

EXEC PageCustomers 5,2

این دستور تنها در sqlserver2005 به بالا قابل اجرا است.
از انجا که در sql server 2000 چیزی به نام @RowNumber و یا ... نداریم بهترین روشی را که پیدا کردم روشی بود که در بالا ذکر کردم.

[AminSobati]

این دستور تنها در sqlserver2005 به بالا قابل اجرا است.
از انجا که در sql server 2000 چیزی به نام @RowNumber و یا ... نداریم بهترین روشی را که پیدا کردم روشی بود که در بالا ذکر کردم.

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html