How to pass parameter values to SQL query

1

Hi,

I'm trying to run a query in SQL Server 2012 which sums up all inventory transactions for the end of each month.
I'm simplifying the problem here. I can't use grouping.

I've created this stored procedure.
The idea was to call the procedure and hand it the parameters for the end of month dates for the last 12 months.

CREATE PROCEDURE [dbo].[ICAgeing]  (@CutOffDate varchar(6))
AS
BEGIN
   
    INSERT INTO CDStockAgeing (ItemValue, ItemQty, YearMonth)

    SELECT SUM(TRANSCOST) AS TotalCost, SUM(Quantity) AS Qty, @CutOffDate 
    FROM   ICIVAL
    WHERE LEFT(TRANSDATE,6) <= @CutOffDate 

END

I would then call the procedure and get the 'YearMonth' values in the format '201512', '201511', '201510' etc. for the last 12 months as @CutOffDate.
I seem to be unable to change the parameter however. The select statement does not run.
Would appreciate any advice.

DECLARE @CutOffDate varchar (6);
SET @CutOffDate  = 'SELECT YearMonth FROM Past12Months';

EXEC CDStockAgeing @CutOffdate
2 

That tries to put the string "SELECT" into @CutOffDate not the value.  It would not work anyway as multiple values would try and go into the variable.

That tries to put the string "SELECT" into @CutOffDate not the value. It would not work anyway as multiple values would try and go into the variable.

3

Thanks for your reply.
Is there a better way to hand the changing parameters to my query?

4

is my understanding correct

the data from the part12months table are only cut off dates? it has only 1 col then you want to iterate with it?

something like this?

DECLARE @CutOffDate varchar (6)
DECLARE @Order Int

select @Order = min([order]) from past12months --in case your table has fields for ordering

while @order is not null
begin
select @CutOffDate=cutoffdate from past12months where [order] = @order
--do more here select @CutOffDate --
select @Order = min([order]) from past12months where [Order] > @order
end

5

What exactly are you trying to do?
Based on your description so far, I can see no reason not to use grouping:

SELECT SUM(V.TransCost) AS TotalCost
    ,SUM(V.Quantity) AS Qty
    ,A.YearMonth
FROM ICIVAL AS V
    CROSS APPLY
    (
        VALUES (DATEADD(month, DATEDIFF(month, 0, V.TransDate), 0))
    ) AS A (YearMonth)
WHERE TransDate >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) -12, 0)
    AND TransDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
GROUP BY A.YearMonth;