October 22, 2014 at 8:45 am
I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.
See below.
Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(dd, 1, @ReportDateIN)
INSERT CustodyStatusTable
SELECT C.CustomerID AS 'CustomerID',
CASE
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@InCo)) THEN 1
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@OutCo)) THEN 0
END AS 'InCounty',
Any Help would be appreciated. I have tried serveral ways to do this.
October 22, 2014 at 9:56 am
You're hard coding the value as 4. Within a script as you've outlined, you'd have to change the value to 5, 6 or 7 to get those values. If you wanted to make these into parameters, you'd need to change your script to being a stored procedure.
For example:
CREATE PROCEDURE MyProc
(@InCo VARCHAR(MAX))
AS
Declare @ReportDateIN [datetime]=GETDATE(),
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(dd, 1, @ReportDateIN)
INSERT CustodyStatusTable
SELECT C.CustomerID AS 'CustomerID',
CASE
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@InCo)) THEN 1
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@OutCo)) THEN 0
END AS 'InCounty',...
Then you call the procedure and pass the value you want:
EXEC MyProc @Inco = 5;
--or
EXEC MyProc @Inco = 6
That will then execute the proc for 5 or 6, as needed.
Now, questions could be raised about why a VARCHAR(MAX) for a single numeric value, what are you doing with the function, because those look like problematic approaches to data access.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2014 at 12:22 pm
I am modifying a job someone else created.
I changed the following lines.
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@InCo)) THEN 1
WHEN CH.CoID IN (SELECT KeyValue FROM dbo.fnValuesText(@OutCo)) THEN 0
to
WHEN CH.CoID IN (4,5,6,7) THEN 1
WHEN CH.CoID IN (8) THEN 0
This worked perfectly.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply