Monday, March 26, 2012

HELP! Stored Procedure problem

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

No comments:

Post a Comment