Where clause neded in complicated Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where clause neded in complicated Stored Procedure

Author

Topic

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-26 : 10:33:22

I have a for me complicated Stored Procedure (SP) and I use the SP to create a Paging function in my ASP .NET Datalist.
Everything works wel and it gives me a specified nr of records and through a output parameter also the nr of records.

But now I would like to select only the Persons with lastname starting with an A. A proviced by input parameter.
I tried everywhere but I cannot find a place to put the clause.

Parameter: @searchstring char
WHERE Lastname LIKE @searchstring

I need to be able to select all persons with lastname like @searchstring
AND
keep the paging function

This is the original SP that works (TG again thanx for this one)

ALTER PROCEDURE [dbo].[getContactsPaged]
(
@searchstring char,
@startRowIndex int,
@maximumRows int,
@Totalrecords int output
)
AS

SELECT PersonID
,CompanyID
,CompanyName
,LastName
,FirstName
,Email
,Telephone
,Extra1
,extra2
,Extra3
FROM (
select p.PersonID
,p.CompanyID
,c.companyName
,p.LastName
,p.FirstName
,p.Email
,p.Telephone
,p.Extra1
,p.extra2
,p.Extra3
,ROW_NUMBER() OVER (ORDER BY PersonID) AS RowRank
from Person p
left outer join company c
on c.companyid = p.companyid
) AS ContactsWithRowNumbers

WHERE RowRank > @startRowIndex
AND RowRank <= (@startRowIndex + @maximumRows)

Select @totalrecords = count(*)
From Person
WHERE Lastname LIKE @searchstring

Return @totalRecords

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-26 : 10:53:39

[code]
ALTER PROCEDURE [dbo].[getContactsPaged]
@searchstring char,
@startRowIndex int,
@maximumRows int,
@Totalrecords int output
AS

SELECT PersonID
,CompanyID
,CompanyName
,LastName
,FirstName
,Email
,Telephone
,Extra1
,extra2
,Extra3
FROM (
select p.PersonID
,p.CompanyID
,c.companyName
,p.LastName
,p.FirstName
,p.Email
,p.Telephone
,p.Extra1
,p.extra2
,p.Extra3
,ROW_NUMBER() OVER (ORDER BY PersonID) AS RowRank
from Person p
left outer join company c
on c.companyid = p.companyid
WHERE Left(Lastname, 1) LIKE @searchstring
) AS ContactsWithRowNumbers
WHERE RowRank > @startRowIndex
AND RowRank <= (@startRowIndex + @maximumRows)

SET @Totalrecords = @@RowCount[/code]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-26 : 11:03:55

is it an optional paramater. if so all I would add to the previous query is ..in case it might come in as NULL, just doing the same thing and I got bit when searchstring comes in as NULL

WHERE Left(Lastname, 1) LIKE @searchstring OR @searchstring IS NULL

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-26 : 11:20:44

Thank you Yosiasz

I will test this solution and post my findings.
Thank you for your quick reply.

Rick

rickrehorst
Starting Member

7 Posts

Posted - 2009-10-28 : 12:07:48

Thanx Russel and Yosiasz

The SP works fine
But now the part of understanding it.

1st question
What is it with parameters with a double @@ ?

2nd question
WHERE Left(Lastname, 1) LIKE @searchstring
does that belong to Select PersonID or to Select p.personID ??

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-28 : 12:18:34

1. @@ means a global variable used by SQL itself.
2. That will be for the inner SELECT which is in between the () but it does not matter. If you do not specify SQL figures it out. But if later on you add another table to that select statement that has PersonID then it will let you know..Ambiguous column name 'PersonID'

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources