CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Farkiba
Farkiba

Reputation: 376

SQL Select maxDate in SP

USE [MyDatabase]
GO
Object:  StoredProcedure [dbo].[SP_MyProcedure]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[SP_MyProcedure] 
-- Add the parameters for the stored procedure here
@StartDate  NVARCHAR(19),
@EndDate    NVARCHAR(19)
AS
BEGIN

SET NOCOUNT ON;

 Insert statements for procedure here
DECLARE @FirstQuery nvarchar(1500);
DECLARE @SecondQuery nvarchar(1500);
DECLARE @TSQL nvarchar(4000);



SET @FirstQuery =
    '
    SELECT * FROM OPENQUERY(LinkedServer,
    ''
    SELECT * FROM Server.Table
    WHERE Name IN 
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM LocalServer.MyServer.dbo."NameList"
    WHERE LOCATION = ''''X'''' AND SOURCE = ''''Y'''') AND TIMESTAMP >= ''''' + @StartDate + ''''' AND TIMESTAMP < ''''' + @EndDate + ''''''')';

My question is: How could I include max(date) in the code, so the @startDate would be compared to the latest available date for a column in the DB instead of todays date. The code you see works fine, however when I choose a later date than the last existing date in the DB the query runs for a while before it returns an error that no columns were found.

Thanks in advance for any suggetions.

HELP. The following part of the code doesn't work, but I can't figure out why. Does anyone have any suggestions? Thank you

     -- Insert statements for procedure here
DECLARE @FirstQuery nvarchar(1500);
DECLARE @SecondQuery nvarchar(1500);
DECLARE @TSQL nvarchar(4000);
DECLARE @MaxTimeStamp nvarchar(19);


SET@MaxTimeStamp =
'SELECT MAX(TimeStamp) From OPENQUERY(LinkedServer)'


IF @StartDate <= @MaxTimeStamp
BEGIN

SET @FirstQuery =
    '
    SELECT * FROM OPENQUERY(LinkedServer,
    ''
    SELECT * FROM Server.Table
    WHERE Name IN 
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM LocalServer.MyServer.dbo."NameList"
    WHERE LOCATION = ''''X'''' AND SOURCE = ''''Y'''') AND TIMESTAMP >= ''''' + @StartDate + ''''' AND TIMESTAMP < ''''' + @EndDate + ''''''')';

END

Upvotes: 2

Views: 2021

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Well, you could replace it with a subquery:

(select max(timestamp) from NameList)

If this is a stored procedure, you might want to put this in a variable, something like:

declare @MaxTimestamp datetime;
select @MaxTimestamp = max(TimeStamp) from NameList

-- in your query, something like
      coalesce(@StartTime, maxTimeStamp)

If performance is an issue, try adding an index on NameList(Location, Source, Timestamp).

Upvotes: 2

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

Try this...

DECLARE @maxDate datetime;
SELECT @maxDate = MAX(timestamp) FROM OPENQUERY(...);

IF @StartDate <= @maxDate
BEGIN
  --Your original query here
END

Upvotes: 1

Related Questions