CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Matt
Matt

Reputation: 109

Store selected column names in user-defined table

I'm currently facing a problem with searching values from columns specified in user-defined table. I've got my .NET program in which I define which parameters are going to be searched for. I've decided that the best way to achieve this is to store this values (selected columns) in a list. Now I have to do something like that:

SELECT
    @MyColumnNamesInAList <- I do not know how to iterate it properly
FROM 
    xyz

What is more, I have to insert NULL if there is no corresponding column in xyz.

In C# code I would do something like this:

foreach(var column in MyColumnNamesInAList)
{
    if(!xyz.Contains(column)
        "NULL AS " + column.Name;
}

Upvotes: 0

Views: 54

Answers (2)

RNA Team
RNA Team

Reputation: 279

@MyColumnNamesInAList <- I do not know how to iterate it properly

DECLARE @MyColumnNamesInAList VARCHAR(50) = 'ID,Name'
DECLARE @xml AS XML
SET @xml = CAST(('<X>'+REPLACE(@MyColumnNamesInAList,',' ,'</X><X>')+'</X>') AS XML)
SELECT N.value('.', 'varchar(10)') AS Columns FROM @xml.nodes('X') AS T(N)

/*

Columns
--------
ID
Name

*/

In C# code I would do something like this:

foreach(var column in MyColumnNamesInAList)

{

if(!xyz.Contains(column)

    "NULL AS " + column.Name;

}

Consider we have a table as 

CREATE TABLE [dbo].[tblTesttable]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](50) NULL)

Now If we execute the below query, we can figure out the extra columns - 

DECLARE @MyColumnNamesInAList VARCHAR(50) = 'ID,Name,NewColumn1,NewColumn2'
DECLARE @xml AS XML
SET @xml = CAST(('<X>'+REPLACE(@MyColumnNamesInAList,',' ,'</X><X>')+'</X>') AS XML)
SELECT X.Column_Name 
FROM (SELECT N.value('.', 'varchar(10)') AS Column_Name FROM @xml.nodes('X') AS T(N))X 
EXCEPT 
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'tblTesttable')

/*

Column_Name
-----------
NewColumn1
NewColumn2

*/

What is more, I have to insert NULL if there is no corresponding column in xyz.

Now here I will give you a hint (I want you to solve this part) (:

You need to issue

ALTER TABLE N'tblTesttable' ADD NewColumn1  VARCHAR(20) NULL
ALTER TABLE N'tblTesttable' ADD NewColumn2  VARCHAR(20) NULL

You need to build the query dynamically and using the previous stuffs that we shared.

Hope you understand.

All the best!!!

Upvotes: 1

Aka Guymelef
Aka Guymelef

Reputation: 111

You should dynamically generate the query directly in your .NET program.

You could do it with system view and the EXEC command but that will run poorly on SQL Server.

Watch out for SQL injection if the values you're using come from the end-user.

Upvotes: 0

Related Questions