when i try to run a procedure i've been working on, i get the following Error:Server: Msg 8144, Level 16, State 2, Line 0Procedure or function has too many arguments specified.execution code:exec search_orders_1 @dwelltype=1
stored procedure:SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER PROCEDURE search_orders_1 @lowage int = null, @highage int = null, @highwealth int = null, @lowwealth int = null, @highpurprice int = null, @lowpurprice int = null, @lowmtgamt int = null, @highmtgamt int = null, @lowincome nvarchar(4000) = null, @highincome nvarchar(4000) = null, @marriedstat nvarchar(4000) = null, @lowyearbuild nvarchar(4000) = null, @highyearbuild nvarchar(4000) = null, @mtgpresstat nvarchar(4000) = null, @lenderpresstat nvarchar(4000) = null, @ratets nvarchar(4000) = null, @loants nvarchar(4000) = null, @childstat nvarchar(4000) = null, @homeownerstat nvarchar(4000) = null, @lowpurdate nvarchar(4000) = null, @highpurdate nvarchar(4000) = null, @addresstype varchar(3)=null, @SPSir varchar(3)=null, @debttype varchar(3)=null, @dwelltype varchar(3)=null, @zipselect nvarchar(4000) = null, @acselect nvarchar(4000) = null, @stselect nvarchar(4000) = null, @cityselect nvarchar(4000) = null, @counselect nvarchar(4000) = null,-- as @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o WHERE 1 = 1' IF @lowage<>0 and @lowage is not null SELECT @sql = @sql + ' AND o.per1_age >= @xlowage' IF @highage<>0 and @highage IS NOT null SELECT @sql = @sql + ' AND o.per1_age <= @xhighage' IF @lowincome<>'DF' and @lowincome IS NOT NULL SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome' IF @highincome<>'DF' and @highincome IS NOT NULL SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome' IF @highwealth<>0 and @highwealth IS NOT NULL SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth' IF @lowwealth<>0 and @lowwealth IS NOT NULL SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth' IF @highpurprice<>0 and @highpurprice IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice' IF @lowpurprice<>0 and @lowpurprice IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice' IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild' IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild' IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'IF @highmtgamt<>0 and @highmtgamt IS NOT NULL SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt' IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate' IF @highpurdate<>'DF' and @highpurdate IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate' IF @marriedstat in ('M','S') SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat' IF @ratets in ('V','F') SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets' IF @homeownerstat in ('H','R') SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'IF @childstat in ('Y','N') SELECT @sql = @sql + ' AND o.child_pres = @xchildstat' IF @lenderpresstat ='Y' SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>+ '' ''' IF @lenderpresstat ='N' SELECT @sql = @sql + ' AND o.oo_mtg_lender_name=+ '' ''' IF @mtgpresstat='Y' SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'IF @mtgpresstat='N' SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'IF @loants<>'DF' and @loants IS NOT NULL SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'if @dwelltype='1' SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''if @dwelltype='2' SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF' SELECT @sql = @sql + ' and (1=2'IF @acselect<>'DF' and @acselect IS NOT NULL SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )' IF @stselect<>'DF' and @stselect IS NOT NULL SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )' IF @cityselect<>'DF' and @cityselect IS NOT NULL SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )' IF @counselect<>'DF' and @counselect IS NOT NULL SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'IF @zipselect<>'DF' and @zipselect IS NOT NULL SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF' SELECT @sql = @sql + ')'SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv ' IF @debug = 1 PRINT @sql SELECT @paramlist = '@xlowage int , @xhighage int , @xlowincome nvarchar(4000) , @xhighincome nvarchar(4000) , @xmarriedstat nvarchar(4000) , @xhighwealth int , @xlowwealth int , @xhighpurprice int , @xlowpurprice int , @xlowyearbuild nvarchar(4000) , @xhighyearbuild nvarchar(4000) , @xlowmtgamt int , @xhighmtgamt int , @xmtgpresstat nvarchar(4000) , @xlenderpresstat nvarchar(4000) , @xratets nvarchar(4000) , @xloants nvarchar(4000) , @xchildstat nvarchar(4000) , @xhomeownerstat nvarchar(4000) , @xlowpurdate nvarchar(4000) , @xhighpurdate nvarchar(4000) , @xzipselect nvarchar(4000) , @xacselect nvarchar(4000) , @xstselect nvarchar(4000) , @xcityselect nvarchar(4000) , @xcounselect nvarchar(4000)' EXEC sp_executesql @sql, @paramlist, @lowage , @highage , @lowincome , @highincome , @marriedstat , @highwealth , @lowwealth , @highpurprice , @lowpurprice , @lowyearbuild , @highyearbuild , @lowmtgamt , @highmtgamt , @mtgpresstat , @lenderpresstat , @ratets , @loants , @childstat , @homeownerstat , @lowpurdate , @highpurdate , @addresstype, @SPSir, @DebtType, @DwellType, @zipselect , @acselect , @stselect , @cityselect , @counselectGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOcould someone help me figure out what's up? someone told me you can have 1,024 parameters for a stored procedure.... what am i missing?do i have to many IF clauses?
Error Executing Procedure
Posted - 2007-04-17 : 19:03:27
