I have the following stored procedure:
CREATE procedure sp_BA_BuildTablesStep2
(
@.BACKENDDATEVALUE varchar(10),
@.QUERYSTATMENT varchar(100)
)
AS
DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_BACK') is not null drop table BA_BACK
SELECT BA_NEW.SRC_CODE,
Count(RG_BA_SHIPPED_ORDERS.ORDER_NBR) AS SUBSEQUENT_ORDERS,
Sum(RG_BA_SHIPPED_ORDERS.TOTAL_AMT) AS INVOICE_TOTAL,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_CHARGE) AS SHIPPING_CHARGE,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_COST) AS SHIPPING_COST,
Sum(RG_BA_SHIPPED_ORDERS.MARGIN) AS MARGIN
INTO BA_BACK
FROM BA_NEW INNER JOIN RG_BA_SHIPPED_ORDERS ON BA_NEW.CUST_NBR = RG_BA_SHIPPED_ORDERS.CUST_NBR
WHERE (RG_BA_SHIPPED_ORDERS.DATE > [BA_NEW].[MaxOFDATE]) And (RG_BA_SHIPPED_ORDERS.DATE < = " + @.BACKENDDATEVALUE+ ") " + @.QUERYSTATMENT +"
GROUP BY BA_NEW.SRC_CODE"
EXEC(@.SQL)
GO
When I run it I get 0 rows affected, when I run the query in QA with my values plugged in I get the results I want... can you see anything I did wrong?
@.BACKENDDATEVALUE is a date supplied as '02/18/03' and @.QUERYSTATMENT is like: 'and RG_BA_SHIPPED_ORDERS.CO_TYPE =1'
I'm changeing @.QUERYSTATMENT by adding "OR" statements at the end depending on what the user chooses in the form.
Any ideas?
KenFigured out the problem! I wasn't passing the vars correctly!
Thanks much!
Ken
Monday, March 26, 2012
HELP! Stored Procedure problem
Labels:
asdeclare,
backenddatevalue,
database,
following,
microsoft,
mysql,
oracle,
procedure,
procedurecreate,
querystatment,
server,
sp_ba_buildtablesstep2,
sql,
stored,
varchar
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment