sql Table Variables - 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 2008 Forums

Transact-SQL (2008)

sql Table Variables

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-19 : 09:51:09

The select on the bottom brings back no records. What am I doing wrong?

ALTER PROCEDURE [PubWebdbo].[Usp_pasort_read_app_error_log]
--(
-- @Update_DateTime datetime
--)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;

DECLARE @app_error_log_Table TABLE (
app_error_log_id INT,
update_datetime DATETIME,
APPLICATION VARCHAR(8000),
module VARCHAR(8000),
error_num INT,
error_desc VARCHAR(500),
sid VARCHAR(8),
record_id INT,
offenderid INT)

INSERT INTO @app_error_log_Table
(app_error_log_id,
update_datetime,
APPLICATION,
module,
error_num,
error_desc,
sid,
record_id,
offenderid)
SELECT app_error_log_id,
CONVERT(VARCHAR, update_datetime, 101) AS update_datetime,
APPLICATION,
module,
error_num,
error_desc,
sid,
record_id,
offenderid
FROM dbo.app_error_log
WHERE ( update_datetime >= Dateadd(hh, 7, Dateadd(dd, 0, Datediff(dd, 0,
Getdate())
- 1)
) )
AND ( update_datetime <= Dateadd(hh, 7, Dateadd(dd, 0,
Datediff(dd, 0,
Getdate()
)))
)
AND ( APPLICATION = 'SORTInitialPhotoLoad' )
ORDER BY sid

INSERT INTO @app_error_log_Table
(app_error_log_id,
update_datetime,
APPLICATION,
module,
error_num,
error_desc,
sid,
record_id,
offenderid)
SELECT app_error_log_id,
CONVERT(VARCHAR, update_datetime, 101) AS update_datetime,
APPLICATION,
module,
error_num,
error_desc,
sid,
record_id,
offenderid
FROM dbo.app_error_log
WHERE ( update_datetime >= Dateadd(hh, 7, Dateadd(dd, 0, Datediff(dd, 0,
Getdate())
- 1)
) )
AND ( update_datetime <= Dateadd(hh, 7, Dateadd(dd, 0,
Datediff(dd, 0,
Getdate()
)))
)
AND ( APPLICATION <> 'SORTInitialPhotoLoad' )

-- Where clause to run reports for the day before
--WHERE (CONVERT(date, GETDATE() - 1) = CONVERT(date, Update_DateTime))
-- to pass a date
--WHERE ( Dateadd(DAY, 0, Datediff(DAY, 0, update_datetime)) = @Update_DateTime )
SELECT *
FROM @app_error_log_Table
END

Dave
Helixpoint Web Development
http://www.helixpoint.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-19 : 09:54:52

Do any of your inserts actually insert any rows?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-01-19 : 09:56:15

The selects seem to bring back data

quote:
Originally posted by nigelrivett

Do any of your inserts actually insert any rows?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dave
Helixpoint Web Development
http://www.helixpoint.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 10:01:09

Do you have executed the altered stored procedure?
The shown statement isn't executing the stored procedure, it is only altering it.
If yes then
Are there rows in the table app_error_log which are fitting the conditions in your WHERE clause?
i.e. between 2011-01-18 07:00:00.000 and 2011-01-19 07:00:00.000

Why do you use a table variable?
You could do the SELECT statements without it by using UNION ALL.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources