1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
|
CREATE PROCEDURE sp_ExportTransInfo @TransNo VarChar(20) AS
DECLARE @transline as varchar(255)
DECLARE @DestinationFile as varchar(255)
/*SET @TransLine = (SELECT LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.TRANS_NO)) + REPLICATE(' ', 10), 10) + LEFT(overdrive.TRANSACTION_TABLE.STATUS, 1)
+ LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19)
+ LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19)
+ LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.VEHICLE_ID)) + REPLICATE(' ', 20), 20)
+ LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.CARRIER_ID)) + REPLICATE(' ', 20), 20)
+ overdrive.TRANSACTION_DETAIL.PRODUCT_ID AS TransLine
FROM overdrive.TRANSACTION_TABLE INNER JOIN
overdrive.TRANSACTION_DETAIL ON overdrive.TRANSACTION_TABLE.TRANS_NO = overdrive.TRANSACTION_DETAIL.TRANS_NO
WHERE (overdrive.TRANSACTION_TABLE.TRANS_NO = @TransNo))*/
SELECT @TransLine = ISNULL(@TransLine, '')
+ LEFT(RTRIM(LTRIM(TRANS.TRANS_NO)) + REPLICATE(' ', 10), 10)
+ LEFT(TRANS.STATUS, 1)
+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19)
+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19)
+ LEFT(RTRIM(LTRIM(TRANS.VEHICLE_ID)) + REPLICATE(' ', 20), 20)
+ LEFT(RTRIM(LTRIM(TRANS.CARRIER_ID)) + REPLICATE(' ', 20), 20)
+ DETAIL.PRODUCT_ID
+ ',' AS TransLine
FROM overdrive.TRANSACTION_TABLE
INNER JOIN overdrive.TRANSACTION_DETAIL ON overdrive.TRANSACTION_TABLE.TRANS_NO = overdrive.TRANSACTION_DETAIL.TRANS_NO
INNER JOIN dbo.Split AS SPLIT ON CAST(SPLIT.DATA AS INT) = overdrive.TRANSACTION_TABLE.TRANS_NO
SET @DestinationFile = 'C:\'+@TransNo+'.odrive'
EXEC sp_AppendToFile @DestinationFile, @transline
GO |
Partager