sql Table Variables

Question

sql Table Variables

davef 22400

SSC-Addicted

Points: 469
More actions
January 19, 2011 at 7:17 am

#388671

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

I'll give you some advice to get more (at least one) answer to your question. Provide some ddl for us to work with. It's more likely an expert will fancy your query if they had all the legwork done for them.

please provide

-DDL

-a few insert statements to populate the tables

-expected results

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

LutzM SSC Guru Points: 107049 More actions · Answer 2

One reason might be there are simply no rows in the table variable.

Either there are no data in the app_error_log table or your queries return no data.

As a side note: what is the specific reason for running two separate insert statements?

If I'm not overlooking something, both queries will return all errors from the given period.

The rows that are excluded in the first select will be inserted by the 2nd one.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

rvphx SSCrazy Points: 2120 More actions · Answer 3

This might sound a bit naive, but are you running both the queries in the same window/tab? If you are, then I suspect that there are no rows being pushed to your table variable. Probably some sample data and DDL statements might help us get a resolution for your issue.