I have a Parameters table that keep the starting and ending date for my project.
What I need is to have sproc that return (among others) a 1/0 field according to that dates. Here's what I tried to do:
CREATE PROCEDURE AppRestrictions
(@IDNO nvarchar(20),
@CatType int)
AS
BEGIN
SELECT CatID, (???) AS DATE_VALID
FROM tblApplications
CROSS JOIN tblParameters
WHERE (CONVERT(char(10), GETDATE(),126) BETWEEN StartDate and EndDate) AND AppID NOT IN (
SELECT AppCategory FROM tblApplications WHERE IDNO=@IDNO
UNION
SELECT RestrCatID FROM tblApplications INNER JOIN tblApplicationRestrictions
ON tblApplications.CatID=tblApplicationRestrictions.CatID
WHERE IDNO=@IDNO)
END
Are you just returning one row (rather than many-rows)?
If so you could use an OUTPUT parameter, and return the Yes/No (or 1/0 whatever) via that parameter - and also the CatID via another OUTPUT parameter, if you need that value too)
CONVERT(char(10), GETDATE(),126) BETWEEN StartDate and EndDate
Change StartDate and EndDate to be DATE datatype columns, and then just do a normal comparison thereby avoiding the "cost" (and potential ambiguity) of converting GetDate() to String.
WHERE GETDATE() >= StartDate
AND GETDATE() < DATEADD(Day, 1, EndDate)
I may be answering the wrong question! I was just wanting to ameka suggestion in case it was relevant to what you were doing (and you hadn't already thought of doing it that way)
You can get back your CatID from your SELECT, as a resultset, and as you currently have it in your original code sample
You could pass back the DATE_VALID as an additional column in that resultset (as you proposed in your first code example).
OR ... given that the DATE_VALID should be the same for all rows you could separately pass that back as an OUTPUT Parameter. Reason to do this would be if the Caller needs that value, on its own, for some purpose.
DECLARE @MyDATE_VALID int
EXEC AppRestrictions @IDNO = 'xxx', @CatType = 123, @DATE_VALID = @MyDATE_VALID OUTPUT
IF @MyDATE_VALID = 1
BEGIN
... do some stuff ...
END
Thank you so much for your help, your answers helped a lot
This is what I finally did:
CREATE PROCEDURE AppRestrictions
(@IDNO nvarchar(20), @CatType int, @DATE_VALID bit OUTPUT)
AS
BEGIN
SELECT CatID
FROM tblApplications
WHERE AppID NOT IN (
SELECT AppCategory FROM tblApplications WHERE IDNO=@IDNO
UNION
SELECT RestrCatID FROM tblApplications INNER JOIN tblApplicationRestrictions
ON tblApplications.CatID=tblApplicationRestrictions.CatID
WHERE IDNO=@IDNO)
IF EXISTS (SELECT 1 FROM tblParameters WHERE GETDATE() >= StartDate AND GETDATE() < DATEADD(Day, 1, EndDate))
SET @DATE_VALID=1
ELSE
SET @DATE_VALID=0
END
If your UNION can never have duplicates in the First and Second SELECT statements?? then if you use UNION ALL it will perform better (it won't spend time doing a de-dupe of the results)
Dunno if you prefer it? but you can set @DATE_VALID like this:
SELECT @DATE_VALID = CASE WHEN EXISTS
(
SELECT 1 FROM tblParameters
WHERE GETDATE() >= StartDate
AND GETDATE() < DATEADD(Day, 1, EndDate)
)
THEN 1
ELSE 0
END