forked from MRGhidini/SAP_Queries
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPROC Concatena Valores em todas as colunas.sql
61 lines (52 loc) · 1.55 KB
/
PROC Concatena Valores em todas as colunas.sql
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Drop Procedure PROC_RSD_DadosWest
Go
--EXEC PROC_RSD_DadosWest '20180101', '20181201', '', 'N', ''
Create Procedure PROC_RSD_DadosWest
(
@DtIni as DateTime,
@DtFim as Datetime,
@Tipo as CHAR, /*1 NotaWB - 2 PedWB - 3 NFCosan */
@CTE as VARCHAR(1),
@SerieNF as VARCHAR(10)
)
as
Declare @ColumnName as VARCHAR(100),
@Columns as VARCHAR(MAX) = '',
@Where as Varchar(500) = ' Where 1=1 ',
@Query as VARCHAR(MAX)
/*Motando Colunas*/
Declare xCur Cursor for
Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'RSD_DadosWest' AND LEFT(COLUMN_NAME, 6) != 'FILTRO' Order By ORDINAL_POSITION
OPEN xCur
FETCH NEXT FROM xCur Into @ColumnName
WHILE(@@FETCH_STATUS = 0 )
Begin
Select @Columns += CONCAT(' ,[', @ColumnName, '] = CONCAT(''.'',[', @ColumnName, '],''.'')')
FETCH NEXT FROM xCur Into @ColumnName
END
Close xCur
DEALLOCATE xCur
/*Motando Where*/
IF(LEN(ISNULL(@DtIni, ''))>0)
Begin
SET @Where += CONCAT(' And FILTRO_Data >= ''', @DtIni, '''')
End
IF(LEN(ISNULL(@DtFim, ''))>0)
Begin
SET @Where += CONCAT(' And FILTRO_Data <= ''', @DtFim, '''')
End
IF(LEN(ISNULL(@Tipo, ''))>0)
Begin
SET @Where += CONCAT(' And FILTRO_Tipo = ''', @Tipo, '''')
End
IF(ISNULL(@CTE, 'N') = 'N')
Begin
SET @Where += ' And FILTRO_CTE <> ''CTE'''
End
IF(LEN(ISNULL(@SerieNF, ''))>0)
Begin
SET @Where += CONCAT(' And FILTRO_Serie = ''', @SerieNF, '''')
End
/*Juntando Query Final e Executando*/
SET @Query = CONCAT('SELECT ', STUFF(@Columns, 1, 2, ''), 'FROM RSD_DadosWest', @Where)
Print(@Query)