Conditional select - dependent on results returned by other select

[Earth]

I have an existing (working) stored procedure that I need to modify. The basics of the SP are:

CREATE PRODEDURE dbo.myProcedure
@dateFrom datetime,
@dateTo datetime

CREATE TABLE #myTempTable

DECLARE @currentDate datetime
SET @currentDate = getdate()

If (left(@dateFrom,11) != left(@currentDate,11))
/* If1: dateFrom is not the current date */
BEGIN
Insert into #myTempTable
SELECT statment from local db1
END

If (left(@dateTo,11) = left(@currentDate,11))
/* If2: dateTo is the currentDate */
BEGIN
Insert into #myTempTable
SELECT statment from linked server database
END

INSERT into #myTempTable
SELECT statement from local db2

SELECT *
FROM #myTempTable
ORDER BY Date ASC

GO

(All date rows that are returned are between @dateFrom and @dateTo due to the select queries)

The revision that I wish to make to this SP is:
If the first select statement (ie. in If1) places a row for the Current Date into the temp table, then I don't want the second select statement (ie. If2) to be executed.

I am unsure of the syntax for this. I don't belive the solutions I have been attempting (further SELECT statements etc) are very efficient.

Thanks for your help. If any further clarifications of the problem are necessary, I will respond ASAP. If anyone knows of any links I should follow to find an answer for myself, please post them!

Thank you,
Earth

 

[dbtech]

Check BOL for @@ROWCOUNT

The changes you need to make wil be

IF @@ROWCOUNT=0
BEGIN
If (left(@dateTo,11) = left(@currentDate,11))
/* If2: dateTo is the currentDate */
BEGIN
Insert into #myTempTable
SELECT statment from linked server database
END
END

@@ROWCOUNT Returns the number of rows affected by the last statement.

You may need to store the value returned by @@ROWCOUNT in a local variable for later processing, Because @@ROWCOUNT
value changes after each SQL satement is executed.

When you compare dates its better to use this format instead of left statement
CONVERT(datetime,convert(char(12),@currentDate))


dbtech

 

[Earth]

Hi dbtech,

I'm not sure that what you suggested would work in the way I need. As If1 will more than likely return a RANGE of dates, not just a single date, doing I believe that @@ROWCOUNT = 0 as a conditional test would not achieve my purpose.

However, if I could put in a SELECT statement such as:

SELECT *
FROM #myTempTable
WHERE
CONVERT(datetime,convert(char(12),Date)) = CONVERT(datetime,convert(char(12),@currentDate))

And then do the @@ROWCOUNT=0 conditional check, that would work (perhaps that's what you were suggesting in the first place?). The problem with that method is that the results of this query - a single row - will be returned with the result of the Stored Procedure, which is not what I want!

An alternative I have tried is to use a variable to hold the rowcount, as suggested (I assume that would mean the results of the select statement wouldn't be returned at the end of the SP). This was the code I tried:

DECLARE @todayCount int
SET @todayCount =
(SELECT Count (*)
FROM #myTempTable
WHERE
CONVERT(datetime,convert(char(12),Date)) = CONVERT(datetime,convert(char(12),@currentDate))
)

This however will not pass a SQL check: Incorrect Syntax near 'SET @todayCount'.

In fact, if I only add the line
'SET @todayCount = 0;
I get the same error.

Any ideas?

Also, could you please explain why using that date comparison mechanism is better?

 

[Earth]

It's working now. This might not be the most efficient way to do it, but it works:

CREATE PRODEDURE dbo.myProcedure
@dateFrom datetime,
@dateTo datetime
AS
CREATE TABLE #myTempTable

DECLARE @todayCount int
DECLARE @currentDate datetime
SET @currentDate = getdate()

If (left(@dateFrom,11) != left(@currentDate,11))
/* If1: dateFrom is not the current date */
BEGIN
Insert into #myTempTable
SELECT statment from local db1
END

If (left(@dateTo,11) = left(@currentDate,11))
/* If2: dateTo is the current date */
BEGIN
SELECT @todayCount =
(SELECT Count (*)
FROM #tblPubHols
WHERE
CONVERT(datetime,convert(char(12),Date)) = CONVERT(datetime,convert(char(12),@currentDate))
)
If (@todayCount = 0)
BEGIN
Insert into #myTempTable
SELECT statment from linked server database
END
END

INSERT into #myTempTable
SELECT statement from local db2

SELECT *
FROM #myTempTable
ORDER BY Date ASC

GO

 

[tlbroadbent]

Here are some suggestions.

You can set @todaycount to count(*) with the following syntax. You don't need the extra Select and parentheses.

SELECT @todayCount = Count(*)

If Exists is more efficient than Select Count(*).

If (left(@dateTo,11) = left(@currentDate,11))
/* If2: dateTo is the current date */
BEGIN
IF EXISTS
(SELECT * FROM #tblPubHols
--Note that the simpler date conversion
--You can also use...
-- Left([Date],11) =
-- Left(@cuurentdate,11)
WHERE
Convert(char(10),[Date], 101) =
Convert(char(10), @currentdate, 101))
BEGIN
Insert into #myTempTable
SELECT statement from linked server database
END
END

I would Declare @dateFrom, @dateTo, and @currentDate as char(10) and load the character value at the start of the SP. This would avoid performing conversions later in the stored proc.

DECLARE @currentDate char(10)
SET @currentDate = convert(char(10), getdate(), 101)

Other statements could then be modified.

If @dateFrom = @currentdate
If @dateTo = @currentdate
WHERE Convert(char(10),[Date], 101) = @currentdate

Note: I also avoid using reserved words such as Date as column or object names.

I hope these suggestions help. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.