Hi all
I am attempting to get data from a SQL Server database Stored Procedure.
I need to pass some variables to SQL, but cannot work out how to do this - well I can send a fixed set of variables but these tend to change 3 or 4 times a year.
This is my Source code:
The variables which I need to get from my Excel workbook are @SCE = '117,118,148,151,152,159,163'
Prior to Power Query I built the statement in VBA
The variables are stored in a table on Sheet1 in the form of
B8 to B50 have these numbers
C8 to C50 are a description
D8 to D50 are a check against which if it is 'x' then select
In the example above the source code needs to read
because they have an x against them in column D
Anyone have any ideas
I am attempting to get data from a SQL Server database Stored Procedure.
I need to pass some variables to SQL, but cannot work out how to do this - well I can send a fixed set of variables but these tend to change 3 or 4 times a year.
This is my Source code:
SQL:
= Sql.Database("SERVER", "DBNaame", [Query="EXEC stored_procedure @SCE = '117,118,148,151,152,159,163', @REG = '', @SBU = '20,64,69,63,67,68,93,65', @BUN = 0 ,@PRC = 0 ,@MPG = 0 ,@BPP = 0 ,@MON = '' ,@ACTIVE = 1 ,@VALUE = 1"])The variables which I need to get from my Excel workbook are @SCE = '117,118,148,151,152,159,163'
Prior to Power Query I built the statement in VBA
The variables are stored in a table on Sheet1 in the form of
B8 to B50 have these numbers
C8 to C50 are a description
D8 to D50 are a check against which if it is 'x' then select
| B | C | D | |
| 7 | 116 | FY14 | |
| 8 | 117 | FY15 | |
| 9 | 118 | FY16 | |
| 10 | 148 | FY17 | x |
| 11 | 156 | FY18 | x |
| 12 | 160 | FY19 | x |
| 13 | 163 | FY20 |
In the example above the source code needs to read
SQL:
= Sql.Database("SERVER", "DBNaame", [Query="EXEC stored_procedure @SCE = '148,156,160', @REG = '', @SBU = '20,64,69,63,67,68,93,65', @BUN = 0 ,@PRC = 0 ,@MPG = 0 ,@BPP = 0 ,@MON = '' ,@ACTIVE = 1 ,@VALUE = 1"])Anyone have any ideas
