SELECT IN CLAUSE

Question

Post reply

SELECT IN CLAUSE

hoseam

SSCertifiable

Points: 5533
More actions
May 12, 2014 at 2:00 am

#291143

I have this 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
)
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')
and I have this query which is wrong so far
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_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 IN (select [COLUMN_NAME] from [Hosea_tblDef_Cloning_Table]) 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
And when I run this code this is the end results:
[dbo].[Hosea_tblDef_RETURNS] ([REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID] = 'D061', [PRODUCT_DESC], [FUND_ID] = 'D061', [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER])
What I need to achieve is to put 'PIC1' next to [PRODUCT_ID] in my end results.. I know my error it's in CASE statement in the query, I'm having a problem to correcting it.
Please help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

The problem is that you only have a single value with your variables and you need more than one.

Using a JOIN instead of the variables, you can get it working.

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 ', [' + c.COLUMN_NAME + '] = ''' + cl.NEW_VALUE + '''' AS [text()]

FROM information_schema.columns c

JOIN [Hosea_tblDef_Cloning_Table] cl ON c.TABLE_NAME = cl.TABLE_NAME AND c.COLUMN_NAME = cl.COLUMN_NAME

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 wonder what are you trying to do with this.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Luis

Sorry mate, answered here. I hadn't noticed this thread or I would have put up a dupe notice.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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