Stored Procedure Parameter Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Parameter Values

Author

Topic

user45
Starting Member

10 Posts

Posted - 2011-10-03 : 07:20:47

Hi All,

I have created a stored procedure which creates a temporary table and inserts data. Within this stored procedure I have declared a @daterange parameter with values = 1,2,3,4.
I want to run a job where all values for the daterange parameter are updated onto the table.
I have tried:
exec MCMStockAgeingSP @DateRange = 1
exec MCMStockAgeingSP @DateRange = 2
exec MCMStockAgeingSP @DateRange = 3
exec MCMStockAgeingSP @DateRange = 4

but this executes with daterange = 1 and then gets overridden with the values when daterange = 2 and so on....so the values i'm left with in the table are the ones with daterange = 4. But I want ALL values in the table.
Can this be done?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-10-03 : 07:27:12

Can you post your SP and expected output?

Senthil.C
------------------------------------------------------
MCTS - [Microsoft][ODBC SQL Server Driver]Operation canceled

user45
Starting Member

10 Posts

Posted - 2011-10-03 : 07:48:12

CREATE Procedure [dbo].[MCMStockAgeingSP]

@DateRange int

AS
Truncate Table [MCMStocksAging]
Set NoCount On
BEGIN

Declare @Branch varchar (50)
Declare @ProductCode varchar (50)
Declare @Description varchar (255)
Declare @DocYear varchar(10)
Declare @Month varchar(10)
Declare @NumberOfDays varchar(10)
Declare @DateReceived smalldatetime
DECLARE @today smalldatetime
DECLARE @fromdate smalldatetime
DECLARE @todate smalldatetime
DECLARE @PeriodDesc varchar(10)

SET @today = GETDATE()
SET @fromdate = DATEADD(D,-91,GETDATE())
SET @todate = DATEADD(D,1,GETDATE())
SET @PeriodDesc='0-3'

IF @DateRange = 2
BEGIN
SET @fromdate = DATEADD(D,-365,GETDATE())
SET @todate = DATEADD(D,-91,GETDATE())
SET @PeriodDesc='4-12'
END
IF @DateRange = 3
BEGIN
SET @fromdate = DATEADD(D,-729,GETDATE())
SET @todate = DATEADD(D,-365,GETDATE())
SET @PeriodDesc='13-24'
END
IF @DateRange = 4
BEGIN
SET @fromdate = DATEADD(D,-3650,GETDATE())
SET @todate = DATEADD(D,-729,GETDATE())
SET @PeriodDesc='24+'
END
DECLARE curProduct CURSOR
FOR
Select Branch.Name as Branch,
Product.ProductCode as ProductCode,
Product.description as ProductDescription,
(YEAR(datereceived)) AS DocYear,
(MONTH(datereceived)) AS DocMonth,
DATEDIFF(day, (DateReceived), @today) AS NumberOfDays,
@PeriodDesc AS Period,
StockReceiptCost.DateReceived,
from Product
WITH (NOLOCK) left join StockReceiptCost on Product.ProductID=StockReceiptCost.ProductID
left Join Branch with(nolock) on StockReceiptCost.BranchID=Branch.BranchID
where
and StockReceiptCost.DateReceived >= @fromdate
AND StockReceiptCost.DateReceived <@todate

FOR READ ONLY
OPEN curProduct
FETCH NEXT FROM curProduct INTO
@Branch,
@ProductCode,
@Description,
@DocYear,
@Month,
@NumberOfDays,
@PeriodDesc,
@DateReceived,

WHILE (@@FETCH_STATUS = 0)
BEGIN
Insert Into [MCMStocksAging]
(Branch,
ProductCode,
Description,
DocYear,
Month,
NumberOfDays,
PeriodDesc,
DateReceived)
Values
(@Branch,
@ProductCode,
@Description,
@DocYear,
@Month,
@NumberOfDays,
@PeriodDesc,
@DateReceived)
FETCH NEXT FROM curProduct INTO
@Branch,
@ProductCode,
@Description,
@DocYear,
@Month,
@NumberOfDays,
@PeriodDesc,
@DateReceived

END
CLOSE curProduct
DEALLOCATE curProduct
Set NoCount OFF
END

I want to fill the table with values when daterange = 1, daterange = 2 etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 07:53:11

so you want to replicate all daterange values in your table? ie. each record inserted once for ech daterange?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

user45
Starting Member

10 Posts

Posted - 2011-10-03 : 07:57:15

yes I want to insert all records for each daterange

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2011-10-03 : 08:00:06

You have truncate statement in your SP, Obviously it will empty the table when ever u execute the SP...

Senthil.C
------------------------------------------------------
MCTS - [Microsoft][ODBC SQL Server Driver]Operation canceled

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 08:22:37

you can do it in one shot


CREATE Procedure [dbo].[MCMStockAgeingSP]

@DateRange varchar(100)

AS
Truncate Table [MCMStocksAging]

Set NoCount On
BEGIN

DECLARE @Dates table
(
StartDate datetime,
ENdDate datetime,
PeriodDesc varchar(10)
)

INSERT @Dates
SELECT DATEADD(D,-91,GETDATE()), DATEADD(D,1,GETDATE()) ,'0-3'
WHERE ',' + @DateRange + ',' LIKE '%,1,%'
UNION ALL
SELECT DATEADD(D,-365,GETDATE()), DATEADD(D,-91,GETDATE()) ,'4-12'
WHERE ',' + @DateRange + ',' LIKE '%,2,%'
UNION ALL
SELECT DATEADD(D,-729,GETDATE()), DATEADD(D,-365,GETDATE()) ,'13-24'
WHERE ',' + @DateRange + ',' LIKE '%,3,%'
UNION ALL
SELECT DATEADD(D,-3650,GETDATE()), DATEADD(D,-729,GETDATE()) ,'24+'
WHERE ',' + @DateRange + ',' LIKE '%,4,%'



Select Branch.Name as Branch,
Product.ProductCode as ProductCode,
Product.description as ProductDescription, 
(YEAR(datereceived)) AS DocYear, 
(MONTH(datereceived)) AS DocMonth,
DATEDIFF(day, (DateReceived), @today) AS NumberOfDays,
d.PeriodDesc AS Period,
StockReceiptCost.DateReceived,
from Product 
WITH (NOLOCK) left join StockReceiptCost on Product.ProductID=StockReceiptCost.ProductID
left Join Branch with(nolock) on StockReceiptCost.BranchID=Branch.BranchID 
left join @Dates d
on StockReceiptCost.DateReceived >= d.startdate 
AND StockReceiptCost.DateReceived < d.endtodate

in your original post its included in where but you're doing a left join hence effectively it will reduee to inner join. if you want to filter it based on dates then make it inner join. alternatively if you want to use date filter only for getting matches use the above suggestion itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources