April 23, 2014 at 4:40 am
hoseam (4/23/2014)
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
For the sake of clarification and disambiguation, are the parameters @Product_Id and @Fund_Id column names in @TableName table? Or are these values in specific colums?
😎
April 23, 2014 at 4:48 am
hoseam (4/23/2014)
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
DECLARE @Product_Id VARCHAR(100), @Fund_Id VARCHAR(100), @NewProduct_Id VARCHAR(100), @NewFund_Id VARCHAR(100), @TableName VARCHAR(100)
DECLARE @SQLscript VARCHAR(2000)
SELECT @TableName = 'permTable', @Product_Id = 'AGP1', @Fund_Id = 'E016', @NewProduct_Id = 'PIC1', @NewFund_Id = 'D016'
SELECT
@SQLscript =
'INSERT INTO [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF(input.ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + CHAR(10) +
' ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'FROM [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
'WHERE product_id = ' + QUOTENAME(@Product_Id,'''') + ' AND fund_id = ' + QUOTENAME(@Fund_Id,'''') + ''
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = 'product_id' THEN ' = ' + QUOTENAME(@NewProduct_Id,'''')
WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) input (ColumnList)
WHERE t.table_name = @TableName
PRINT @SQLscript
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 23, 2014 at 5:03 am
ChrisM@Work (4/23/2014)
hoseam (4/23/2014)
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
DECLARE @Product_Id VARCHAR(100), @Fund_Id VARCHAR(100), @NewProduct_Id VARCHAR(100), @NewFund_Id VARCHAR(100), @TableName VARCHAR(100)DECLARE @SQLscript VARCHAR(2000)
SELECT @TableName = 'permTable', @Product_Id = 'AGP1', @Fund_Id = 'E016', @NewProduct_Id = 'PIC1', @NewFund_Id = 'D016'
SELECT
@SQLscript =
'INSERT INTO [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF(input.ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + CHAR(10) +
' ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'FROM [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
'WHERE product_id = ' + QUOTENAME(@Product_Id,'''') + ' AND fund_id = ' + QUOTENAME(@Fund_Id,'''') + ''
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = 'product_id' THEN ' = ' + QUOTENAME(@NewProduct_Id,'''')
WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) input (ColumnList)
WHERE t.table_name = @TableName
PRINT @SQLscript
Nice:-)
now what if the user creates a table with a malicious name like:
CREATE TABLE dbo.[permTable''; drop table dbo.permTable;--]
(
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL
)
Just a thought 😎
April 23, 2014 at 5:10 am
Eirikur Eiriksson (4/23/2014)
ChrisM@Work (4/23/2014)
hoseam (4/23/2014)
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
DECLARE @Product_Id VARCHAR(100), @Fund_Id VARCHAR(100), @NewProduct_Id VARCHAR(100), @NewFund_Id VARCHAR(100), @TableName VARCHAR(100)DECLARE @SQLscript VARCHAR(2000)
SELECT @TableName = 'permTable', @Product_Id = 'AGP1', @Fund_Id = 'E016', @NewProduct_Id = 'PIC1', @NewFund_Id = 'D016'
SELECT
@SQLscript =
'INSERT INTO [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF(input.ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + CHAR(10) +
' ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'FROM [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
'WHERE product_id = ' + QUOTENAME(@Product_Id,'''') + ' AND fund_id = ' + QUOTENAME(@Fund_Id,'''') + ''
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = 'product_id' THEN ' = ' + QUOTENAME(@NewProduct_Id,'''')
WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) input (ColumnList)
WHERE t.table_name = @TableName
PRINT @SQLscript
Nice:-)
now what if the user creates a table with a malicious name like:
CREATE TABLE dbo.[permTable''; drop table dbo.permTable;--](
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL
)
Just a thought 😎
Little Bobby Tables is never very far away 😀
Care to write the tablename string validation code? I'd like to think OP will collect it from a list...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 23, 2014 at 5:16 am
ChrisM@Work (4/23/2014)
I'd like to think OP will collect it from a list...
My point exactly!
😎
April 23, 2014 at 6:12 am
Thank you for the code, I'm testing it and it's working, as for other malicious events I'll look into them
April 23, 2014 at 6:16 am
Can you present the user with a list of relevant tables? If not, you could check the passed-in tablename against a list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 23, 2014 at 7:59 am
@ChrisM@Work
Can you please explain to me your code, and why the concept of CROSS APPLY, why did you choose to go with it, you also used both of them. I also see FOR XML PATH(''), why that, why did you choose this solution. I would like to understand the idea behind your solution.
April 23, 2014 at 9:17 am
The two CROSS APPLY blocks collect the column lists. FOR XML PATH concatenates the lists into a string. You can read about APPLY in the two excellent articles by Paul White, linked in my sig below. FOR XML PATH is well-documented online.
Try disassembling the query: chop bits out and run them, like this:
DECLARE @Product_Id VARCHAR(100), @Fund_Id VARCHAR(100), @NewProduct_Id VARCHAR(100), @NewFund_Id VARCHAR(100), @TableName VARCHAR(100)
DECLARE @SQLscript VARCHAR(2000)
SELECT @TableName = 'permTable', @Product_Id = 'AGP1', @Fund_Id = 'E016', @NewProduct_Id = 'PIC1', @NewFund_Id = 'D016'
-- column list for chosen table
SELECT column_name
FROM information_schema.columns c
WHERE c.table_name = @TableName
-- column list with identity column removed
SELECT column_name
FROM information_schema.columns c
WHERE c.table_name = @TableName
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
-- FOR XML PATH concatenates column list
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_name = @TableName
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
-- remove ', ' from concatenated column list
SELECT
t.*,
ColumnList = STUFF(input.ColumnList,1,2,'')
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) input (ColumnList)
WHERE t.table_name = @TableName
-- source columns with new values
SELECT
t.*,
ColumnList = STUFF([source].ColumnList,1,2,'')
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = 'product_id' THEN ' = ' + QUOTENAME(@NewProduct_Id,'''')
WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 9, 2014 at 6:29 am
There has been few changes to this code..
Before I used to pass parameters
declare @Fund_Id varchar(50),
@Product_Id varchar(50),
@NewFund_Id varchar(50),
@NewProduct_Id varchar(50),
@TableName varchar(50
Now I have created a table to get them from a table,
CREATE TABLE [dbo].[Hosea_tblDef_Cloning_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLONE_ID] [int] NOT NULL,
[TABLE_NAME] [varchar](150) NULL,
[COLUMN_NAME] [varchar](150) NULL,
[OLD_VALUE] [varchar](150) NULL,
[NEW_VALUE] [varchar](50) NULL
)
COLUMN_NAME being all the columns that I need to change.
I have to rows in this column, have same CLONE_ID (1), same TABLE_NAME ([Hosea_tblDef_RETURNS]) then different COLUMN_NAME (Product_Id and Fund_Id) and their different OLD_VALUE and NEW_VALUE.
Now I have this code:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @Col = [COLUMN_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = @Col THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
This code only runs for one row in the table ([Hosea_tblDef_Cloning_Table]). I'm not sure how to get it right, please help.
May 9, 2014 at 6:32 am
Can you provide a few sample rows for table [dbo].[Hosea_tblDef_Cloning_Table] please?
As INSERT's to the table ddl you've already posted. Cheers
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 9, 2014 at 6:39 am
INSERT INTO [Hosea_tblDef_Cloning_Table] ([CLONE_ID]
,[TABLE_NAME]
,[COLUMN_NAME]
,[OLD_VALUE]
,[NEW_VALUE])
VALUES (1, 'Hosea_tblDef_RETURNS', 'Product_Id', 'AGP1', 'PIC1'),
(1, 'Hosea_tblDef_RETURNS', 'Fund_Id', 'E016', 'D061')
May 9, 2014 at 6:45 am
When I run this code:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @Col = [COLUMN_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = @Col THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
I get:
[dbo].[Hosea_tblDef_RETURNS]
[REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID], [PRODUCT_DESC], [FUND_ID] = 'D061', [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER])
which only took results on the second row.
May 9, 2014 at 6:56 am
Same for table Hosea_tblDef_RETURNS please - ddl and dml. Cheers.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 9, 2014 at 7:09 am
CREATE TABLE [dbo].[Hosea_tblDef_RETURNS](
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL,
[FUND_INCEPTION_DATE] [varchar](6) NULL,
[RETURNS_TYPE_KEY] [varchar](150) NULL,
[RETURNS_TYPE_FILTER] [varchar](150) NULL,
[RETURNS_MONTH_FILTER] [varchar](150) NULL,
CONSTRAINT [PK_tblDef_RETURNS] PRIMARY KEY CLUSTERED
(
[RETURNS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Hosea_tblDef_RETURNS] ([REPORT_ID]
,[COMPANY]
,[PRODUCT_TYPE]
,[PRODUCT_ID]
,[PRODUCT_DESC]
,[FUND_ID]
,[FUND_INCEPTION_DATE]
,[RETURNS_TYPE_KEY]
,[RETURNS_TYPE_FILTER]
,[RETURNS_MONTH_FILTER])
VALUES (1, 'ABC', 'MYCOM', NULL, 'AGP1', 'Scheme', 'E016', '200704', 'SS', 'RETURNS_TYPE_CD = '16'', NULL)
The whole idea behind this is that, if we have this product, AGP1, and we want to have another product exactly as AGP1, we will have to clone it, but the clone will have another name instead of AGP1.. so we take everything as it is and change only the PRODUCT_ID, but in other cases we might need to change FUND_ID. But that's like on this table..
On other tables, they might need to change other columns, hence the creation of that table, [Hosea_tblDef_Cloning_Table].
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply