Search/ Stored Procedure.. Not Working

Compkitty

Hi, I'm sort of new to Stored Procedures; and I'm building a search
page where there are 9 parameters the user can put in; as little as
1, at most 9.. then it runs the Stored P and looks at the values.
I am using the COALESCE; to skips nulls,... but I'm still not getting
the right results.. either I get 0 or 4000..
CREATE PROCEDURE Sp_Search
(
@AcctNum varchar (100),
@CliId varchar (10),
@CLName varchar (25),
@CFName varchar (25),
@Foundation varchar (100),
@Custodian varchar (255),
@MMgr varchar (255),
@Advisor varchar (50),
@PA varchar (3),
@Status varchar (3),
@rc int output
)

AS
SELECT tblAccounts.ActAccountNum, tblAccounts.ActClientID,
tblMasterList.MstLName, tblMasterList.MstFName,
tblMasterList.MstFoundationName, tblManagerList.Manager,
tblMasterList.MstDTIARep, tblMasterList.MstReconAssignID,
tblAccounts.ActAcctStatus

FROM ((tblAccounts INNER JOIN tblManagerList ON
tblAccounts.ActMgrID = tblManagerList.MgrID) INNER JOIN
tblCustodian ON tblAccounts.ActCustID = tblCustodian.CustIDRSN)
INNER JOIN tblMasterList ON tblAccounts.ActClientID =
tblMasterList.MstClientID

WHERE tblAccounts.ActAccountNum = COALESCE(@AcctNum,
tblAccounts.ActAccountNum) AND
tblAccounts.ActClientID = COALESCE(@CliID,
tblAccounts.ActClientId) AND
tblMasterList.MstLname = COALESCE( @CLName,
tblMasterList.MstLName) AND
tblMasterList.MstFName = COALESCE(@CFName,
tblMasterList.MstFName) AND
tblMasterList.MstFoundationName = COALESCE(@Foundation,
tblMasterList.MstFoundationName) AND
tblCustodian.Custodian = COALESCE(@Custodian,
tblCustodian.Custodian) AnD
tblManagerList.Manager = COALESCE(@MMgr,
tblManagerList.Manager) AND
tblMasterlist.MstDTIARep = COALESCE(@Advisor,
tblMasterList.MstDTIARep) AND
tblMasterlist.MstReconAssignID =COALESCE( @PA,
tblMasterlist.MstReconAssignID) AND
tblAccounts.ActAcctStatus =COALESCE( @Status,
tblAccounts.ActAcctStatus)

SET @rc = @@ROWCOUNT
Return
GO

Not sure what is missing, but below is what the Query Analyzer comes
out with..

[code:1:0837109d5b]
EXEC @RC = [TrackingGenX2K].[dbo].[Sp_Search]
@AcctNum, @CliId, @CLName, @CFName, @Foundation, @Custodian, @MMgr,
@Advisor, @PA, @Status, @rc OUTPUT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TrackingGenX2K.dbo.Sp_Search'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
PRINT ' Output Parameter(s): '
SELECT @PrnLine = ' @rc = ' + isnull( CONVERT(nvarchar,
@rc), '<NULL>' )
PRINT @PrnLine

[/code:1:0837109d5b]

There is also an error attached now:

[code:1:0837109d5b]
Server: Msg 134, Level 15, State 1, Line 13
The variable name '@rc' has already been declared. Variable names must
be unique within a query batch or stored procedure.
[/code:1:0837109d5b]

ANY help would be appreciated.. THANKS!!! :lol: :oops: :D
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

Jul 21 '05 #1

Chris, Master of All Things Insignificant

You may want to put this in microsoft.public.sqlserver.programming group

Chris
"Compkitty" <co********@yahoo-dot-com.no-spam.invalid> wrote in message
news:42********@127.0.0.1...

Hi, I'm sort of new to Stored Procedures; and I'm building a search
page where there are 9 parameters the user can put in; as little as
1, at most 9.. then it runs the Stored P and looks at the values.
I am using the COALESCE; to skips nulls,... but I'm still not getting
the right results.. either I get 0 or 4000..
CREATE PROCEDURE Sp_Search
(
@AcctNum varchar (100),
@CliId varchar (10),
@CLName varchar (25),
@CFName varchar (25),
@Foundation varchar (100),
@Custodian varchar (255),
@MMgr varchar (255),
@Advisor varchar (50),
@PA varchar (3),
@Status varchar (3),
@rc int output
)

AS
SELECT tblAccounts.ActAccountNum, tblAccounts.ActClientID,
tblMasterList.MstLName, tblMasterList.MstFName,
tblMasterList.MstFoundationName, tblManagerList.Manager,
tblMasterList.MstDTIARep, tblMasterList.MstReconAssignID,
tblAccounts.ActAcctStatus

FROM ((tblAccounts INNER JOIN tblManagerList ON
tblAccounts.ActMgrID = tblManagerList.MgrID) INNER JOIN
tblCustodian ON tblAccounts.ActCustID = tblCustodian.CustIDRSN)
INNER JOIN tblMasterList ON tblAccounts.ActClientID =
tblMasterList.MstClientID

WHERE tblAccounts.ActAccountNum = COALESCE(@AcctNum,
tblAccounts.ActAccountNum) AND
tblAccounts.ActClientID = COALESCE(@CliID,
tblAccounts.ActClientId) AND
tblMasterList.MstLname = COALESCE( @CLName,
tblMasterList.MstLName) AND
tblMasterList.MstFName = COALESCE(@CFName,
tblMasterList.MstFName) AND
tblMasterList.MstFoundationName = COALESCE(@Foundation,
tblMasterList.MstFoundationName) AND
tblCustodian.Custodian = COALESCE(@Custodian,
tblCustodian.Custodian) AnD
tblManagerList.Manager = COALESCE(@MMgr,
tblManagerList.Manager) AND
tblMasterlist.MstDTIARep = COALESCE(@Advisor,
tblMasterList.MstDTIARep) AND
tblMasterlist.MstReconAssignID =COALESCE( @PA,
tblMasterlist.MstReconAssignID) AND
tblAccounts.ActAcctStatus =COALESCE( @Status,
tblAccounts.ActAcctStatus)

SET @rc = @@ROWCOUNT
Return
GO

Not sure what is missing, but below is what the Query Analyzer comes
out with..

[code:1:0837109d5b]
EXEC @RC = [TrackingGenX2K].[dbo].[Sp_Search]
@AcctNum, @CliId, @CLName, @CFName, @Foundation, @Custodian, @MMgr,
@Advisor, @PA, @Status, @rc OUTPUT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TrackingGenX2K.dbo.Sp_Search'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
PRINT ' Output Parameter(s): '
SELECT @PrnLine = ' @rc = ' + isnull( CONVERT(nvarchar,
@rc), '<NULL>' )
PRINT @PrnLine

[/code:1:0837109d5b]

There is also an error attached now:

[code:1:0837109d5b]
Server: Msg 134, Level 15, State 1, Line 13
The variable name '@rc' has already been declared. Variable names must
be unique within a query batch or stored procedure.
[/code:1:0837109d5b]

ANY help would be appreciated.. THANKS!!! :lol: :oops: :D
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

Jul 21 '05 #2