Batching begin and End - 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 2000 Forums

Transact-SQL (2000)

Batching begin and End

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-12 : 11:33:39

Morning,

I have what seems to be a simple query but I can't get it to run within the begin/end block. Can anyone point me in the right direction?


IF  exists(SELECT tblFailedUpLoad.Source,tblFailedUpLoad.Location,tblFailedUpLoad.TrailerID,tblFailedUpLoad.TrailerType,tblFailedUpLoad.Classification,tblFailedUpLoad.Contents,tblFailedUpLoad.Comments,convert(varchar, tblFailedUpLoad.UpLoadDate, 101)as UpLoadDate,LEFT (CONVERT(char(19), tblFailedUpLoad.UpLoadTime,108),5) as UpLoadTime,'May be a 3rd Party' as FailedCode,tbl.FailedUpLoad.WhenUpLoaded
	FROM tblFailedUpLoad LEFT JOIN tblTrailerInfo ON tblFailedUpLoad.TrailerID = tblTrailerInfo.TrailerID
	WHERE  tblTrailerInfo.TrailerID  Is Null 
	AND tblFailedUpLoad.Source='ClementPappas')
BEGIN
   SELECT *
   FROM tblFailedUpload
END
ELSE
 BEGIN
   Insert into Yardcheck(Source,Location,TrailerID,TrailerType,Classification,Contents,Comments,UpLoadDate,UpLoadTime,WhenUpLoaded)
   SELECT tblFailedUpLoad.Source,tblFailedUpLoad.Location,tblFailedUpLoad.TrailerID,tblFailedUpLoad.TrailerType,tblFailedUpLoad.Classification,tblFailedUpLoad.Contents,tblFailedUpLoad.Comments,convert(varchar, tblFailedUpLoad.UpLoadDate, 101)as UpLoadDate,LEFT (CONVERT(char(19), tblFailedUpLoad.UpLoadTime,108),5) as UpLoadTime,tblFailedUpLoad.WhenUpLoaded
   FROM  tblFailedUpLoad
   where Source='ClementPappas'
   and TrailerID='013904'

   delete from dbo.tblFailedUpLoad 
   where Source='ClementPappas'
   and TrailerID='013904'

 END

Thanks for any help.

Laura

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-12 : 11:56:27

Use PRINT / SELECT statements to "walk-through" your code.

For example, what does this return to you?

IF exists (SELECT 1 FROM tblFailedUpLoad fu LEFT JOIN tblTrailerInfo ti ON fu.TrailerID = ti.TrailerID WHERE t.TrailerID Is Null AND fu.Source = 'ClementPappas')
BEGIN
	SELECT 1
END ELSE
BEGIN
	SELECT 2
END

Nathan Skerl

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-12 : 12:21:13

Thanks Nathan,

It returns select 2.

So shouldn't it do whatever is in that begin block?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-12 : 12:30:57

Yes, is that not what you are seeing?

Try executing the code outside the query. Which is failing, the DELETE or INSERT, or both?

Does this return records:

  
SELECT  fu.Source,
	fu.Location,
	fu.TrailerID,
	fu.TrailerType,
	fu.Classification,
	fu.Contents,
	fu.Comments,
	convert(varchar, fu.UpLoadDate, 101) as UpLoadDate,
	LEFT (CONVERT(char(19), 
	fu.UpLoadTime,108),5) as UpLoadTime,
	fu.WhenUpLoaded
FROM  tblFailedUpLoad fu
where Source = 'ClementPappas'
	and TrailerID = '013904'

Nathan Skerl

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-12 : 12:41:23

Executed outside the block both execute fine.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-12 : 12:59:01

well...[scratching head] take a look at the rowcounts:

IF exists (SELECT 1 FROM tblFailedUpLoad fu LEFT JOIN tblTrailerInfo ti ON fu.TrailerID = ti.TrailerID WHERE t.TrailerID Is Null AND fu.Source = 'ClementPappas')
BEGIN
	SELECT 1
	SELECT * FROM tblFailedUpload

END ELSE
BEGIN
	SELECT 2
	Insert into Yardcheck(Source,Location,TrailerID,TrailerType,Classification,Contents,Comments,UpLoadDate,UpLoadTime,WhenUpLoaded)
		SELECT 	fu.Source,
			fu.Location,
			fu.TrailerID,
			fu.TrailerType,
			fu.Classification,
			fu.Contents,
			fu.Comments,
			convert(varchar, fu.UpLoadDate, 101) as UpLoadDate,
			LEFT(CONVERT(char(19),fu.UpLoadTime,108),5) as UpLoadTime,
			fu.WhenUpLoaded
		FROM  tblFailedUpLoad fu
		WHERE Source = 'ClementPappas'
			AND TrailerID = '013904'
	select @@rowcount as 'inserted'

	delete from dbo.tblFailedUpLoad 
	where Source='ClementPappas'
		and TrailerID='013904'
	select @@rowcount as 'deleted'

END

Nathan Skerl

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-12 : 13:10:30

How does that go? Doh!

It was doing just what I told it to. I was asking the wrong thing. Thanks so much for helping me see the light Nathan.

Laura

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-12 : 13:46:54

No problem... glad to help.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources