Re: Search/ Stored Procedure.. Not Working

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chris, Master of All Things Insignificant (chris_at_No_Spam_Please.com)
Date: 02/18/05 

Date: Fri, 18 Feb 2005 10:12:54 -0600

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

Chris

"Compkitty" <compkitty7@yahoo-dot-com.no-spam.invalid> wrote in message
news:42160bc4_5@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

Relevant Pages

  • Re: Nested Iif in View / Stored Procedure
    ... "Candace" wrote in message ... > FROM dbo.Device INNER JOIN ... >>IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Re: List parameters for Stored Procedures
    ... INNER JOIN sys.systypes st ON st.xtype = p.system_type_id ... OP said he wants "to get a list of stored procedures (not system ... I think this is possible by querying the sysobjects table and the ... syscolumns has parameters of procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Nested Iif in View / Stored Procedure
    ... datetime, @End_Date datetime, @Nursing_Unit nvarchar, ... FROM dbo.Device INNER JOIN ... >IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Stored Procedure with Multiple conditions
    ... > stored procedures and then using them for the reports. ... > C.CostCenterID INNER JOIN ... > entries, and then there may only be 1 entry. ...
    (microsoft.public.sqlserver.programming)
  • Re: Is sql server as good as access 97?
    ... you might recall that the reason you had to use 'stacked' queries was that ... do not confuse 'stacked' queries with functions. ... Transact-SQL is your aim then use functions or call other stored procedures. ... As far as EM v Query Analyzer is concerned, ...
    (microsoft.public.sqlserver.programming)