Dynamic SQL + DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL + DateTime

Author

Topic

IRBradles
Starting Member

5 Posts

Posted - 2007-11-07 : 21:27:22

I know the solution should be around the forums somewhere, but I've been staring at this code for 3 hours and am sure I'm missing something simple. Am Running SQL Server 2k5. Am Attempting to Recall all water useage details between two dates, just cant get the dates formatted properly and am still learning T-SQL (Am transitioning first DB from Access to SQL Server)

Input is Start and Finish Date in Format YYYYMMDD. If No Date is entered, pick up all records


Table Def
Holding_ID     VarChar
Meter_ID       VarChar
StartD         DateTime
EndD           DateTime
Rate           Float
Tarif          VarChar
Days           Int
Average_Rate   Float
Total_Water_.. Float
Total_Value    Float


/* Begin Procedure */

ALTER PROCEDURE dbo.Return_UseageBetweenDates
  (@sdate int, @ldate int)

AS
  -- Add the parameters for the stored procedure here
  SET DATEFORMAT DMY  
  DECLARE @sql nvarchar(MAX);

  BEGIN
  --Force Unknown Config Variables
  SET ANSI_NULLS ON;
  SET QUOTED_IDENTIFIER ON;

  -- Prevent extra result sets from interfering with SELECT statements.
  SET NOCOUNT ON;
  
  SET @sql = '
    SELECT     Holding_ID, Meter_ID, MIN(StartD) AS Start_Date, MAX(EndD) AS End_Date, Rate, SUM(Days) AS Total_Days, Tarif,
                     SUM(Total_Water_Used) AS T_Water_Used, CAST(SUM(Total_Value) AS money) AS T_Value, AVG(Average_Rate) AS Weighted_Average
    FROM         dbo.Temp_StateChange
    WHERE StartD >= ISNULL('
  SET @sql = @sql + @sdate
  SET @sql = @sql + ', 01011900) AND EndD < ISNULL('
  SET @sql = @sql + @ldate
  SET @sql = @sql + ', 99991231)
    GROUP BY Meter_ID, Holding_ID, Rate, Tarif;'

  EXEC sp_executesql @sql
END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 21:35:34

It would be easier to accept the parameters and set them to defaults outside the SQL, so that the SQL does not have to be dynamic. The stored procedure would have to recompile every time if you use dynamic, by setting the variables in advance, the SQL never changes and is more efficient.

Also, are the dates really stored as integers? or are they stored as char(8)



Set @sdate = IsNull(@sdate,'01011900') --min date of 1/1/1900 or as an integer would be 0
Set @ldate = IsNull(@ldate,'12312049')  --max date of 12/31/2049

Set NoCount on

SELECT     Holding_ID, Meter_ID, MIN(StartD) AS Start_Date, MAX(EndD) AS End_Date, Rate, SUM(Days) AS Total_Days, Tarif,
                     SUM(Total_Water_Used) AS T_Water_Used, CAST(SUM(Total_Value) AS money) AS T_Value, AVG(Average_Rate) AS Weighted_Average
    FROM         dbo.Temp_StateChange
    WHERE StartD >= @sdate and EndD <= @ldate

What is the datatype of the StartD and EndD fields? You can manipulate the query to throw them as dates, or manipulate the parameters passed to be in the same datatype as the column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources