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 1 1252
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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
reply
views
Thread by todd |
last post: by
|
3 posts
views
Thread by rdlebreton |
last post: by
|
2 posts
views
Thread by M Wells |
last post: by
|
3 posts
views
Thread by Job Lot |
last post: by
|
4 posts
views
Thread by Axel |
last post: by
|
1 post
views
Thread by Compkitty |
last post: by
|
25 posts
views
Thread by abbylee26 |
last post: by
|
4 posts
views
Thread by red vertigo |
last post: by
|
2 posts
views
Thread by acw |
last post: by
| | | | | | | | | | |