March 20, 2017 at 12:04 pm
The issue is the ability to substitute the library name in the SET @list = statement. -- from[' + @Library + '].information_schema.columns
in order to get the list of column names of Table06 table.
Table06 can be in different libraries and schemas.
DECLARE @Library nvarchar(50) = 'ProdLib';
DECLARE @Schema nvarchar(50); = 'Dflt';
DECLARE @list nvarchar(MAX);
DECLARE @SQL07V nvarchar(MAX);
SET @list =
stuff ((select distinct ',' +
quotename(column_name)
from [MYLib].information_schema.columns
where table_name ='Table06' and column_name not in ('RecID', 'StartDate')
for xml path(''), type).value ('.','VARCHAR(MAX)'),1,1,'')
SET @SQL07V =
'
CREATE VIEW [dflt].[Table07V] as
SELECT RecID, StartDate, col,value
from (select * from [' + @Library + '].[' + @Schema + '].[Table06]) p
unpivot (value for col in (' +@List+')) as unpvt'
EXEC (@SQL07V);
I have tried unsuccessfully to make it work.
Do you have any suggestions?
March 20, 2017 at 12:38 pm
As long as you're creating a view, you will not be able to parameterize your query. Views don't have that functionality. If you need to use parameters, then you have to use either a stored procedure or a function (ideally, an inline table-valued one).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2017 at 12:59 pm
Actually the above example is from a stored procedure, but when I substitute @library for MyLib I get
the error 'Invalid syntax near '.'
The @list value is not enclosed in quotes. It returns all the column names within the table.
March 20, 2017 at 1:08 pm
chas.1 - Monday, March 20, 2017 12:59 PMActually the above example is from a stored procedure, but when I substitute @library for MyLib I get
the error 'Invalid syntax near '.'
The @list value is not enclosed in quotes. It returns all the column names within the table.
Okay, but the error you're getting is because you can't use variables that way. You'll either have to use dynamic SQL to accomplish the task, or a series of IF statements that check for all the possible values and only execute the code appropriate to the value supplied, which is messy, error prone, and duplicates code for every possible value of the variable. Dynamic SQL is usually best for this kind of thing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2017 at 1:19 pm
Okay, thanks for the tip Steve.
Charlie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply