Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

Help with Sproc and multi parameter

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.

Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)OR (PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)OR (StatusISNULL)AND (ContactTypeID = @.ConTypeID)OR (ContactTypeIDISNULL)AND (Caller = @.Caller)OR (CallerISNULL)AND (SubjectLIKE @.Keyword)OR (SubjectISNULL)RETURN

You have the query correct. Your OR's and AND's are misplaced around the brackets.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASBEGINSET NOCOUNT ONSELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROMtblTasksWHERE(ClientID = @.ClientID)AND (Priority = @.PriorityOR @.PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.StatusOR @.StatusISNULL)AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)AND (Caller = @.CallerOR @.CallerISNULL)AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)SET NOCOUNT OFFEND

|||

I tried it and I'm still not getting any rows returned. I have even tried it with all parameters having a good entry.

Just to be sure;

I should be able to enter a clientID, an ActionDate range, a priority, and the other fields of the table could have any entry or null for the other parameters and get returned, YES?

|||I did not notice it but try setting the length for your parameters.|||

Besize the size, could the default values be the reason:

Like:

...

@.Statusnvarchar(50)=NULL,
@.ConTypeIDint=NULL,
@.Callernvarchar(50)=NULL,
@.Keywordnvarchar(50)=NULL

|||I set the size and still I must have an entry in every parameter. Am I missing something? I should be able to do this, right?|||

I really am stuck on this one. Can anyone offer any suggestions? Does anyone understand my problem with this?

|||

Hi

You could add default value to parameter as limno suggested.

If that doesn't work. You could try adding following code to your stored procedure and test in Sql Server Management Studio to trace each parameters.:

if (@.PriorityISNULL)begin print N'Priority IS NULL'endelsebegin print @.Priorityend--print other parameters in similar wayHope this helps.

|||

So here is what I have, listed below: I should be able to pass in a clientID and a valid date range and it should not matter what is in the other fields because I'm passing in NULL, right?

ALTER PROCEDURE dbo.eP_BindContactManagementAction(@.ClientIDint,@.Prioritysmallint=NULL,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar(50)=NULL,@.ConTypeIDint=NULL,@.Callernvarchar(50)=NULL,@.Keywordnvarchar(50)=NULL)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)AND (ContactTypeID = @.ConTypeID)AND (Caller = @.Caller)AND (SubjectLIKE @.Keyword)RETURN

I appreciate any help,|||

You WHERE clause has problem to handle NULL values: Please try this one fromndinakar

SELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,
ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROMtblTasks
WHERE(ClientID = @.ClientID)
AND (Priority = @.PriorityOR @.PriorityISNULL)
AND (ActionDateBETWEEN @.TStartAND @.TEnd)
AND (Status = @.StatusOR @.StatusISNULL)
AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)
AND (Caller = @.CallerOR @.CallerISNULL)
AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)

|||

Another question: could you show us your datasource control code too? if you are using SQLDatasource control, you may need to set this CancelSelectOnNullParameter="false".

Just another shot in dark.

|||

Here is the code, it will return a record if I make an entry for all parameters. But if I leave one blank no records are returned.

Sub BindData() Session("TaskStart") =Me.BasicDatePickerHStart.SelectedValue Session("TaskEnd") =Me.BasicDatePickerHEnd.SelectedValueDim TStartAs String = Session("TaskStart")Dim TEndAs String = Session("TaskEnd")Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("SQL2ConnectionString").ConnectionString'If user unchecks use due date sproc.Dim strSprocAs String strSproc ="BindContactManagementAction" MyConnection =New SqlConnection(ConnectStr) MyCommand =New SqlCommand(strSproc, MyConnection) MyCommand.CommandType = CommandType.StoredProcedureDim ClientIDParamAs New SqlParameter("@.ClientID", SqlDbType.Int, 4) MyCommand.Parameters.Add(ClientIDParam) ClientIDParam.Value = Session("lgClientID")Dim TaskStartParamAs New SqlParameter("@.TStart",Me.BasicDatePickerHStart.SelectedValue) MyCommand.Parameters.Add(TaskStartParam) TaskStartParam.Value =Me.BasicDatePickerHStart.SelectedValueDim TaskEndParamAs New SqlParameter("@.TEnd",Me.BasicDatePickerHEnd.SelectedValue) MyCommand.Parameters.Add(TaskEndParam) TaskEndParam.Value =Me.BasicDatePickerHEnd.SelectedValueIf String.IsNullOrEmpty(Me.KeyWordText.Text)Then Dim KeywordParamAs New SqlParameter("@.Keyword", DBNull.Value) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)Else Dim KeywordParamAs New SqlParameter("@.Keyword",Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then Dim StatusParamAs New SqlParameter("@.Status", DBNull.Value) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)Else Dim StatusParamAs New SqlParameter("@.Status",Me.StatusSearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)End If If String.IsNullOrEmpty(Me.PrioritySearchDrop.SelectedItem.Text)Then Dim PriorityParamAs New SqlParameter("@.Priority", DBNull.Value) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)Else Dim PriorityParamAs New SqlParameter("@.Priority",Me.PrioritySearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)End If If String.IsNullOrEmpty(Me.CallerTextSearch.Text)Then Dim CallerParamAs New SqlParameter("@.Caller", DBNull.Value) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)Else Dim CallerParamAs New SqlParameter("@.Caller",Me.CallerTextSearch.Text) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)End If If String.IsNullOrEmpty(Me.ContactTypeSearchDrop.SelectedValue)Then Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID", DBNull.Value) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)Else Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID",Me.ContactTypeSearchDrop.SelectedValue) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)End If MyConnection.Open()Dim DSAs SqlDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) MyEditDataGrid.DataSource = DS MyEditDataGrid.DataBind()End Sub

|||

We have to find where the problem is:

1. Whether the Stored Procedure is running OK or not in your database?

2. If the sp can return records with limited parameters, we can focus on the code part.

PS:(this is a version I tested on my db which works)( By the way, I would use field name to Name parameters to avoid confusion, but this is not the problem here)

ALTER

PROCEDURE [dbo].[eP_BindContactManagementAction]

(

@.ClientID

int,

@.Priority

smallint=NULL,

@.TStart

datetime,

@.TEnd

datetime,

@.Status

nvarchar(50)=NULL,

@.ConTypeID

int=NULL,

@.Caller

nvarchar(50)=NULL,

@.Keyword

nvarchar(50)=NULL)

AS

SELECT

Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,ReminderDate,

Reminder

, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDate

FROM

tblTasks

WHERE

(ClientID= @.ClientID)

AND

(Priority= @.PriorityOR @.PriorityISNULL)

AND

(ActionDateBETWEEN @.TStartAND @.TEnd)

AND

(Status= @.StatusOR @.StatusISNULL)

AND

(ContactTypeID= @.ConTypeIDOR @.ConTypeIDISNULL)

AND

(Caller= @.CallerOR @.CallerISNULL)

AND

(SubjectLIKE @.KeywordOR @.KeywordISNULL)

END

|||

Ok, progress the sp is working in the database, I tested it in VS 2005 and it worked, so it must be in my code.

Any thoughts from my earlier post?

|||

I changed all my parameters to the code type below and it is working now:

I very much appreciate your help!

If String.IsNullOrEmpty(Me.KeyWordText.Text)Then MyCommand.Parameters.AddWithValue("@.Keyword", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Keyword",Me.KeyWordText.Text)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then MyCommand.Parameters.AddWithValue("@.Status", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Status",Me.StatusSearchDrop.SelectedItem.Text)End If

sql

Help with Paging SPROC

I'm trying to learn from SQLMag.com's InstantDoc #40505 called "Server-Side
Paging with SQL Server "
This was a well written main feature story, however, their syntax in Listing
4 below is giving me errors. Can someone paste the below code into QA and
help me find which quotes are incorrect? I've never dealt with "Search
Variable" syntax before and want to learn from this article.
CODE from
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=40505&DisplayTab=Article[
/url]
=============
/* Listing 4: SELECT_WITH_PAGING Stored Procedure */
CREATE PROCEDURE SELECT_WITH_PAGING (
@.strFields varchar(4000),
@.strPK varchar(100),
@.strTables varchar(4000),
@.intPageNo int = 1,
@.intPageSize int = NULL,
@.blnGetRecordCount bit = 0,
@.strFilter varchar(8000) = NULL,
@.strSort varchar(8000) = NULL,
@.strGroup varchar(8000) = NULL)
/* Executes a SELECT statement that the parameters define,and returns a
particular page of data (or all rows) efficiently. */
AS
DECLARE @.blnBringAllRecords bit
DECLARE @.strPageNo varchar(50)
DECLARE @.strPageSize varchar(50)
DECLARE @.strSkippedRows varchar(50)
DECLARE @.strFilterCriteria varchar(8000)
DECLARE @.strSimpleFilter varchar(8000)
DECLARE @.strSortCriteria varchar(8000)
DECLARE @.strGroupCriteria varchar(8000)
DECLARE @.intRecordcount int
DECLARE @.intPagecount int
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more
efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @.intPageNo < 1
SET @.intPageNo = 1
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all records,
don't do paging.
SET @.blnBringAllRecords = 1
ELSE
BEGIN
SET @.blnBringAllRecords = 0
SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
(@.intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting,
respectively, by executing more efficient
queries. */
IF @.strFilter IS NOT NULL AND @.strFilter != ''
BEGIN
SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
END
ELSE
BEGIN
SET @.strSimpleFilter = ''
SET @.strFilterCriteria = ''
END
IF @.strSort IS NOT NULL AND @.strSort != ''
SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
ELSE
SET @.strSortCriteria = ''
IF @.strGroup IS NOT NULL AND @.strGroup != ''
SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
ELSE
SET @.strGroupCriteria = ''
/* Now start doing the real work. */
IF @.blnBringAllRecords = 1 -- Ignore paging and run a
simple SELECT.
BEGIN
EXEC (
'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
@.strGroupCriteria + @.strSortCriteria
)
END -- We had to bring all
records.
ELSE -- Bring only a
particular page.
BEGIN
IF @.intPageNo = 1 -- In this case we can
execute a more efficient
-- query
with no subqueries.
EXEC (
'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
)
ELSE -- Execute a structure of
subqueries that brings the correct page.
EXEC (
'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
'IN' + '
(SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' + @.strTables
+
' WHERE' + @.strPK + 'NOT IN' + '
(SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strSimpleFilter +
@.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strGroupCriteria +
@.strSortCriteria
)
END -- We had to bring a
particular page.
/* If we need to return the recordcount: */
IF @.blnGetRecordCount = 1
IF @.strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
(id)
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
@.strFilterCriteria +
@.strGroupCriteria
)
GOI think most of the problems are with comments wrapping and breaking down
into 2 lines. There's a missing quote below as well. I correct it and see if
it works for you. Hope the lines won't wrap this time.
/* Listing 4: SELECT_WITH_PAGING Stored Procedure */
CREATE PROCEDURE SELECT_WITH_PAGING (
@.strFields varchar(4000),
@.strPK varchar(100),
@.strTables varchar(4000),
@.intPageNo int = 1,
@.intPageSize int = NULL,
@.blnGetRecordCount bit = 0,
@.strFilter varchar(8000) = NULL,
@.strSort varchar(8000) = NULL,
@.strGroup varchar(8000) = NULL)
/* Executes a SELECT statement that the parameters define,and returns a
particular page of data (or all rows) efficiently. */
AS
DECLARE @.blnBringAllRecords bit
DECLARE @.strPageNo varchar(50)
DECLARE @.strPageSize varchar(50)
DECLARE @.strSkippedRows varchar(50)
DECLARE @.strFilterCriteria varchar(8000)
DECLARE @.strSimpleFilter varchar(8000)
DECLARE @.strSortCriteria varchar(8000)
DECLARE @.strGroupCriteria varchar(8000)
DECLARE @.intRecordcount int
DECLARE @.intPagecount int
/* Normalize the paging criteria.
If no meaningful inputs are provided, we can avoid paging and execute a more
efficient query, so we will
set a flag that will help avoid paging (blnBringAllRecords). */
IF @.intPageNo < 1
SET @.intPageNo = 1
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all records,
don't do paging.
SET @.blnBringAllRecords = 1
ELSE
BEGIN
SET @.blnBringAllRecords = 0
SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
(@.intPageNo - 1))
END
/* Normalize the filter and sorting criteria.
If the criteria are empty, we will avoid filtering and sorting,
respectively, by executing more efficient
queries. */
IF @.strFilter IS NOT NULL AND @.strFilter != ''
BEGIN
SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
END
ELSE
BEGIN
SET @.strSimpleFilter = ''
SET @.strFilterCriteria = ''
END
IF @.strSort IS NOT NULL AND @.strSort != ''
SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
ELSE
SET @.strSortCriteria = ''
IF @.strGroup IS NOT NULL AND @.strGroup != ''
SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
ELSE
SET @.strGroupCriteria = ''
/* Now start doing the real work. */
IF @.blnBringAllRecords = 1 -- Ignore paging and run a
simple SELECT.
BEGIN
EXEC (
'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
@.strGroupCriteria + @.strSortCriteria
)
END -- We had to bring all
records.
ELSE -- Bring only a
particular page.
BEGIN
IF @.intPageNo = 1 -- In this case we can
execute a more efficient
-- query
with no subqueries.
EXEC (
'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
)
ELSE -- Execute a structure of
subqueries that brings the correct page.
EXEC (
'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
'IN' + '
(SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' + @.strTables
+
' WHERE' + @.strPK + 'NOT IN' + '
(SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
@.strTables +
@.strFilterCriteria + @.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strSimpleFilter +
@.strGroupCriteria +
@.strSortCriteria + ') ' +
@.strGroupCriteria +
@.strSortCriteria
)
END -- We had to bring a
particular page.
/* If we need to return the recordcount: */
IF @.blnGetRecordCount = 1
IF @.strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
@.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
(id)'
)
ELSE
EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
@.strFilterCriteria + @.strGroupCriteria
)
GO
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"scott" <sbailey@.mileslumber.com> wrote in message
news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> I'm trying to learn from SQLMag.com's InstantDoc #40505 called
"Server-Side
> Paging with SQL Server "
> This was a well written main feature story, however, their syntax in
Listing
> 4 below is giving me errors. Can someone paste the below code into QA and
> help me find which quotes are incorrect? I've never dealt with "Search
> Variable" syntax before and want to learn from this article.
>
> CODE from
>
http://www.windowsitpro.com/Article...playTab=Articlekred">
> =============
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
more
> efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
records,
> don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure
of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
@.strTables
> +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS
tbl
> (id)
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria +
> @.strGroupCriteria
> )
> GO
>
>
>|||i finally got it into sql, but it renders errors. i'm giving up and going at
another way. thanks for your help though.
you'd think sqlmag.com wouldn't print errant code.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uM8N2pjXFHA.2996@.TK2MSFTNGP10.phx.gbl...
>I think most of the problems are with comments wrapping and breaking down
> into 2 lines. There's a missing quote below as well. I correct it and see
> if
> it works for you. Hope the lines won't wrap this time.
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
> more
> efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
> records,
> don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
> @.strTables
> +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
> (id)'
> )
> ELSE
> EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria + @.strGroupCriteria
> )
> GO
>
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> "Server-Side
> Listing
> http://www.windowsitpro.com/Article...Articl
e
> more
> records,
> of
> @.strTables
> tbl
>|||Check out this article on ASPFAQ: http://www.aspfaq.com/show.asp?id=2120
"scott" <sbailey@.mileslumber.com> wrote in message
news:Okf9SkiXFHA.2684@.TK2MSFTNGP09.phx.gbl...
> I'm trying to learn from SQLMag.com's InstantDoc #40505 called
> "Server-Side Paging with SQL Server "
> This was a well written main feature story, however, their syntax in
> Listing 4 below is giving me errors. Can someone paste the below code into
> QA and help me find which quotes are incorrect? I've never dealt with
> "Search Variable" syntax before and want to learn from this article.
>
> CODE from
> http://www.windowsitpro.com/Article...Articl
e
> =============
> /* Listing 4: SELECT_WITH_PAGING Stored Procedure */
> CREATE PROCEDURE SELECT_WITH_PAGING (
> @.strFields varchar(4000),
> @.strPK varchar(100),
> @.strTables varchar(4000),
> @.intPageNo int = 1,
> @.intPageSize int = NULL,
> @.blnGetRecordCount bit = 0,
> @.strFilter varchar(8000) = NULL,
> @.strSort varchar(8000) = NULL,
> @.strGroup varchar(8000) = NULL)
> /* Executes a SELECT statement that the parameters define,and returns a
> particular page of data (or all rows) efficiently. */
> AS
> DECLARE @.blnBringAllRecords bit
> DECLARE @.strPageNo varchar(50)
> DECLARE @.strPageSize varchar(50)
> DECLARE @.strSkippedRows varchar(50)
> DECLARE @.strFilterCriteria varchar(8000)
> DECLARE @.strSimpleFilter varchar(8000)
> DECLARE @.strSortCriteria varchar(8000)
> DECLARE @.strGroupCriteria varchar(8000)
> DECLARE @.intRecordcount int
> DECLARE @.intPagecount int
> /* Normalize the paging criteria.
> If no meaningful inputs are provided, we can avoid paging and execute a
> more efficient query, so we will
> set a flag that will help avoid paging (blnBringAllRecords). */
> IF @.intPageNo < 1
> SET @.intPageNo = 1
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> IF @.intPageSize IS NULL OR @.intPageSize < 1 -- Bring all
> records, don't do paging.
> SET @.blnBringAllRecords = 1
> ELSE
> BEGIN
> SET @.blnBringAllRecords = 0
> SET @.strPageSize = CONVERT(varchar(50), @.intPageSize)
> SET @.strPageNo = CONVERT(varchar(50), @.intPageNo)
> SET @.strSkippedRows = CONVERT(varchar(50), @.intPageSize *
> (@.intPageNo - 1))
> END
> /* Normalize the filter and sorting criteria.
> If the criteria are empty, we will avoid filtering and sorting,
> respectively, by executing more efficient
> queries. */
> IF @.strFilter IS NOT NULL AND @.strFilter != ''
> BEGIN
> SET @.strFilterCriteria = ' WHERE ' + @.strFilter + ' '
> SET @.strSimpleFilter = ' AND ' + @.strFilter + ' '
> END
> ELSE
> BEGIN
> SET @.strSimpleFilter = ''
> SET @.strFilterCriteria = ''
> END
> IF @.strSort IS NOT NULL AND @.strSort != ''
> SET @.strSortCriteria = ' ORDER BY ' + @.strSort + ' '
> ELSE
> SET @.strSortCriteria = ''
> IF @.strGroup IS NOT NULL AND @.strGroup != ''
> SET @.strGroupCriteria = 'GROUP BY' + @.strGroup + ' '
> ELSE
> SET @.strGroupCriteria = ''
> /* Now start doing the real work. */
> IF @.blnBringAllRecords = 1 -- Ignore paging and run a
> simple SELECT.
> BEGIN
> EXEC (
> 'SELECT ' + @.strFields + 'FROM' + @.strTables + @.strFilterCriteria +
> @.strGroupCriteria + @.strSortCriteria
> )
> END -- We had to bring all
> records.
> ELSE -- Bring only a
> particular page.
> BEGIN
> IF @.intPageNo = 1 -- In this case we can
> execute a more efficient
> -- query
> with no subqueries.
> EXEC (
> 'SELECT TOP' + @.strPageSize + ' ' + @.strFields + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria + @.strSortCriteria
> )
> ELSE -- Execute a structure of
> subqueries that brings the correct page.
> EXEC (
> 'SELECT' + @.strFields + 'FROM' + @.strTables + 'WHERE' + @.strPK +
> 'IN' + '
> (SELECT TOP' + @.strPageSize + ' ' + @.strPK + 'FROM' +
> @.strTables +
> ' WHERE' + @.strPK + 'NOT IN' + '
> (SELECT TOP' + @.strSkippedRows + ' ' + @.strPK + 'FROM' +
> @.strTables +
> @.strFilterCriteria + @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strSimpleFilter +
> @.strGroupCriteria +
> @.strSortCriteria + ') ' +
> @.strGroupCriteria +
> @.strSortCriteria
> )
> END -- We had to bring a
> particular page.
> /* If we need to return the recordcount: */
> IF @.blnGetRecordCount = 1
> IF @.strGroupCriteria != ''
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM' +
> @.strTables + @.strFilterCriteria + @.strGroupCriteria + ') AS tbl
> (id)
> )
> ELSE
> EXEC (
> 'SELECT COUNT(*) AS RECORDCOUNT FROM' + @.strTables +
> @.strFilterCriteria +
> @.strGroupCriteria
> )
> GO
>
>
>