End Stored Procedure if no data

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

General SQL Server Forums

New to SQL Server Programming

End Stored Procedure if no data

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2013-10-15 : 06:09:41

Hello I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run. I would be glad of any help.

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW

DECLARE @BcpHeader AS VARCHAR (2000)
DECLARE @BcpLine AS VARCHAR (2000)
DECLARE @FileNameHeader AS VARCHAR (2000)
DECLARE @FileNameLine AS VARCHAR (2000)
DECLARE @FileDateExtension AS VARCHAR (100)
SET @FileDateExtension = + CAST(DATEPART(yyyy,GETDATE()) AS VARCHAR (4))+ RIGHT('0'+ CAST(DATEPART(MM,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0' + CAST(DATEPART(DD,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0' + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0' + CAST(DATEPART(MI,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0'+ CAST(DATEPART(SS,GETDATE()) AS VARCHAR(2)),2)
SET @FileNameHeader = 'UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_' + @FileDateExtension+ '.csv'
SET @FileNameLine = 'UDEF_DISPATCHER_INTERFACE_ORDER_LINE_' + @FileDateExtension+ '.csv'
SET @BcpHeader = 'bcp "Select * FROM HBSLIVE.dbo.UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP " QUERYOUT \\appserver\Dispatcher\testing\' +@FileNameHeader+ ' -c -t, -T -S'
SET @BcpLine = 'bcp "Select * FROM HBSLIVE.dbo.UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP " QUERYOUT \\appserver\Dispatcher\testing\' +@FileNameLine+ ' -c -t, -T -S'

EXEC xp_cmdshell @BcpHeader

EXEC xp_cmdshell @BcpLine

INSERT INTO UDEF_DISPATCHER_DEL_NOTE_REPOSITORY
SELECT REPLACE(REPLACE(Order_Id,'"',''),'-VOW',''),CONVERT(DATETIME,GETDATE (),103) FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP

TRUNCATE TABLE UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
TRUNCATE TABLE UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-15 : 07:14:12

Maybe this as first statement in your proc?

if not exists(SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW)
begin
return 0
end

Too old to Rock'n'Roll too young to die.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2013-10-15 : 08:19:28

Great thanks it worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources