SQLTeam.com | Weblogs | Forums

CROSS JOIN with Parameters table

tsql
sql2012

#1

Hello,

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

Any suggestions in how to make the code work?

Thank you in advance.


#2

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)

#3

Thank you for your answer.
I will return more than one rows but the DATE_VALID will be the same for all the rows.


#4

So you NEED to return the resultset for the multiple rows?

You could also return an OUTPUT parameter for the DATE_VALID, maybe something like:

CREATE PROCEDURE AppRestrictions
...
@DATE_VALID int OUTPUT
...
AS
...
SELECT Top 1 @DATE_VALID = DATE_VALID_EXPRESSION
FROM tblApplications
    CROSS JOIN tblParameters
...

#5

Maybe I didn't explain it very well, so I'm giving an example.

tblParametes (EndDate, StartDate) --> Records: (20160101, 20161231)
tblApplications (IDNO, CatID) --> (Given through parameter: 123456, 1)
tblApplicationsRestrictions (CatRestrID, CatID) (Return based on given IDNO: ({1,5},{1,3},{1,8})

The result should be: ({5,1} (since date is valid),{3,1},{8,1})

I don't know if I'm thinking it right though.


#6

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

#7

Thank you so much for your help, your answers helped a lot :slight_smile:

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

#8

Looks good :slight_smile:

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

#9

There will be no duplicates but I will use UNION ALL if will perform better. Thank you again for all the help.