Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Views or 'WITH' to query a query.

Status
Not open for further replies.
shauns1

shauns1

Programmer
Oct 21, 2005
53
AU
Hi

I have, what has turned out to be a bit of a monster stored proc. The idea behind it was so that I have just one proc that can handle many different types on my table of contacts. This table links to various other 'relationship' tables such as ContactsToDirectories, ContactsToMaillists. So, depending what parameters are passed to my storedproc, it will query whichever tables it needs to, in order to return a list of contacts.

So, I have it working great (I will paste it below) but now have one more thing I need to do. I have added a ROW_COUNT column to the query, called Row. What I need to be able to do is to have another query that queries the list of contacts returned so that I can filter my results by the newly added Row column.

Lets name the result of my original monster query below "MyContactList". I then want to do something like:

SELECT * FROM MyContactList WHERE Row >0 AND Row <11

Is this possible? I thought it might involve using Views or 'WITH' but as I understand it, I can't have the query below wrapped up in either. I might be wrong.

The monster query follows. Thanks in advance for any suggestions. Similarly, if I've gone completely the wrong way about things in the query below, please do advise.

Thanks

Code: 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[crm_GetContactsPaging] 
	-- Add the parameters for the stored procedure here
	@catID int = null,
	@directoryID int = null,
	@mailingListID int = null,
	@Postcode int = null,
	@Suburb nvarchar(100) = null,
	@Email nvarchar(100) = null,
	@Surname nvarchar(50) = null,
	@State nvarchar(50) = null,
	@Country int = null,
	@ContactID int = null,
	@OrderClause nvarchar(100) = 'contactLastName',
	@StartRowIndex int,
	@NumRows int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
IF NOT @directoryID IS NULL AND NOT @mailingListID IS NULL
BEGIN
--We want to filter contacts by MailingListID and DirectoryID
			IF @directoryID > 0 AND @mailingListID > 0
			BEGIN
			--Get specific mailinglist and directory contacts
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_ContactsToContactCat.ContactID ASC) as Row, crm_ContactsToContactCat.ContactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName,ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody,
											  crm_ContactsToMailingList.listID, crm_ContactsToDirectory.directoryID, crm_ContactsToContactCat.ContactCatID
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToMailingList ON crm_Contacts.contactID = crm_ContactsToMailingList.contactID INNER JOIN
											  crm_ContactsToDirectory ON crm_Contacts.contactID = crm_ContactsToDirectory.contactID INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE     (crm_ContactsToMailingList.listID = @mailingListID) AND (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
											  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToDirectory.directoryID = @directoryID) AND 
											  (crm_ContactsToContactCat.ContactCatID = 1) OR
											  (crm_ContactsToContactCat.ContactCatID = 2)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
									
							
						END
			ELSE IF @directoryID = 0 and @mailingListID > 0
			-- Filter only those who are directory contacts for specific MailingLists
			BEGIN
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_ContactsToContactCat.ContactID ASC) as Row, crm_ContactsToContactCat.ContactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName, ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody,
											  crm_ContactsToMailingList.listID, crm_ContactsToContactCat.ContactCatID
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToMailingList ON crm_Contacts.contactID = crm_ContactsToMailingList.contactID INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE     (crm_ContactsToMailingList.listID = @mailingListID) AND (@Email IS NULL OR
											  crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR
											  crm_Contacts.contactPostCode = @PostCode) AND (@Suburb IS NULL OR
											  crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR
											  crm_Contacts.contactState = @State) AND (@Country IS NULL OR
											  crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToContactCat.ContactCatID = 1)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
									
						END
			ELSE IF @directoryID > 0 and @mailingListID = 0
			-- Filter only those in mailing list for particular directory
			BEGIN
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_ContactsToContactCat.ContactID ASC) as Row, crm_ContactsToContactCat.ContactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName, ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody,
											  crm_ContactsToDirectory.directoryID, crm_ContactsToContactCat.ContactCatID
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToDirectory ON crm_Contacts.contactID = crm_ContactsToDirectory.contactID INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE     (crm_ContactsToDirectory.directoryID = @directoryID) AND (@Email IS NULL OR
											  crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR
											  crm_Contacts.contactPostCode = @PostCode) AND (@Suburb IS NULL OR
											  crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR
											  crm_Contacts.contactState = @State) AND (@Country IS NULL OR
											  crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToContactCat.ContactCatID = 2)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
															
END
			ELSE IF @directoryID = 0 and @mailingListID = 0
			-- All contacts from both MailingList and Directory
			BEGIN
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_Contacts.ContactID ASC) as Row, crm_Contacts.contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName,ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE     (@Email IS NULL OR
											  crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR
											  crm_Contacts.contactPostCode = @PostCode) AND (@Suburb IS NULL OR
											  crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR
											  crm_Contacts.contactState = @State) AND (@Country IS NULL OR
											  crm_Contacts.contactCountryID = @Country) AND 
											  (crm_ContactsToContactCat.ContactCatID = 1) OR
											  (crm_ContactsToContactCat.ContactCatID = 2)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
														
END
			END
ELSE IF NOT @directoryID IS NULL AND @mailingListID IS NULL
--We want to filter by Directory only
BEGIN
			IF @directoryID > 0
			BEGIN
			-- Get specific directory
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_ContactsToDirectory.contactID ASC) as Row, crm_ContactsToDirectory.contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName,ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody, 
											  crm_ContactsToDirectory.directoryID
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToDirectory ON crm_Contacts.contactID = crm_ContactsToDirectory.contactID
						WHERE     (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
											  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToDirectory.directoryID = @directoryID)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
															
END
			ELSE
			BEGIN
			--Get all Directory members
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_Contacts.ContactID ASC) as Row, crm_Contacts.contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName,ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
											  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToContactCat.ContactCatID = 1)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
															
END
			END
ELSE IF @directoryID IS NULL AND NOT @mailingListID IS NULL
BEGIN
-- We want to filter by Mailing List only
			IF @mailingListID > 0
			BEGIN
			-- We want a specific mailing list
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_ContactsToMailingList.contactID ASC) as Row, crm_ContactsToMailingList.contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName, ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody,
											  crm_ContactsToMailingList.listID
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToMailingList ON crm_Contacts.contactID = crm_ContactsToMailingList.contactID
						WHERE     (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
											  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToMailingList.listID = @mailingListID)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
														
END
			ELSE
			BEGIN
			--Get all mailing list members
						SELECT     ROW_NUMBER() OVER(ORDER BY crm_Contacts.ContactID ASC) as Row, crm_Contacts.contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName,ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody
						FROM         crm_Contacts INNER JOIN
											  crm_ContactsToContactCat ON crm_Contacts.contactID = crm_ContactsToContactCat.ContactID
						WHERE (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
											  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country) AND (crm_ContactsToContactCat.ContactCatID = 2)
						ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
													
END
			END
ELSE IF @directoryID IS NULL AND @mailingListID IS NULL
BEGIN
-- Get all contacts
			SELECT     ROW_NUMBER() OVER(ORDER BY crm_Contacts.ContactID ASC) as Row, contactID, ISNULL(crm_Contacts.contactFirstName,'') as contactFirstName, ISNULL(crm_Contacts.contactLastName,'') as contactLastName, ISNULL(crm_Contacts.contactEmail,'') as contactEmail, 
											  ISNULL(crm_Contacts.contactTelephone,'') as contactTelephone, ISNULL(crm_Contacts.contactMobile,'') as contactMobile, ISNULL(crm_Contacts.contactTownID,0) as contactTownID, ISNULL(crm_Contacts.contactPostCode,0) as contactPostCode, 
											  ISNULL(crm_Contacts.contactSuburb,'') as contactSuburb, ISNULL(crm_Contacts.contactState,'') as contactState, ISNULL(crm_Contacts.contactCountryID,0) as contactCountryID, ISNULL(crm_Contacts.contactClinicName,'') as contactClinicName, ISNULL(crm_Contacts.contactDirectoryTitle,'') as contactDirectoryTitle, ISNULL(crm_Contacts.contactDirectoryDescription,'') as contactDirectoryDescription,ISNULL(crm_Contacts.contactDirectoryBody,'') as contactDirectoryBody
			FROM         crm_Contacts
			WHERE     (@Email IS NULL OR crm_Contacts.contactEmail = @Email) AND (@PostCode IS NULL OR crm_Contacts.contactPostCode = @PostCode) AND 
								  (@Suburb IS NULL OR crm_Contacts.contactSuburb = @Suburb) AND (@State IS NULL OR crm_Contacts.contactState = @State) AND (@ContactID IS NULL OR crm_Contacts.contactID = @ContactID) AND (@Country IS NULL OR crm_Contacts.contactCountryID = @Country)
			ORDER BY 
							CASE WHEN @OrderClause='contactLastName' THEN contactLastName 
								WHEN @OrderClause='contactFirstName' THEN contactFirstName 
								WHEN @OrderClause='contactEmail' THEN contactEmail 
								WHEN @OrderClause='contactSuburb' THEN contactSuburb
								WHEN @OrderClause='contactTelephone' THEN contactTelephone
							END
								
END
END
 
Sort by date Sort by votes
If you have SQL 2005:

Code: 
declare @MyContactList table (column, column2, column3...)

insert into @Something
exec dbo.crm_GetContactsPaging (parameters...)

SELECT * FROM @MyContactList WHERE Row >0 AND Row <11

If you do not have SQL 2005, I think you will need to create a temp table rather than a table variable.

If 'Row' is not a column in your query (I don't have time to read all of that) then I suspect what you want is
Code: 
SELECT TOP 10 * FROM @MyContactList ORDER BY WHATEVER

Make sure you use an order by or else you ahve no way of telling what top 10 you will get.

and FYI - there is a SQL Server forum here. Forum183

Hope this helps,

Alex





Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
143
carp
carp
BobThornton
  • Locked
  • Question
Count records until a record value changes
Replies
2
Views
206
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
133
mmiah1982
mmiah1982
Bill Doe
  • Locked
  • Question
PDO UPDATE too long to execute
Replies
1
Views
130
mikrom
mikrom
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
120
carp
carp

Part and Inventory Search

Sponsor

Back
Top