sql create string variable with column names

Question

i'm trying to create a dynamic sql statement either thru sql code or c# code. For example, I'd like to generate a result set based on a string containing a table name. Currently, I am doing this thru c# code.

My current issue is I'd like to generate a search similar to following

select * from customers
where ContactName+City like '%Berlin%'

so I'm thinking given a table name as a string parameter I need to somehow produce a string variable 'ContactName+City+etc' to build part of the search

I'm open to any other ideas as well.

var sql = string.Format(@"
select * from {0}
where {1} like '%criteria%'"
, variable_table
, "column1+column2+columnX"); //need function here to produce this string based on variable table?

Basically, how would I create a string that concatenates a variable number of columns together ('ContactName+City+etc') based on a variable_table?

Do you want to select all the columns in the variable_table or only specific set of columns ? — GayanSanjeewa
– GayanSanjeewa, Commented Dec 5, 2012 at 5:27
I'm thinking all columns because I can create a view if I want to limit the columns, right? — Rod
– Rod, Commented Dec 5, 2012 at 13:46

Yogendra Singh · Accepted Answer · 2012-12-05 03:08:31Z

2

Why not simply this:

      select * from variable_table_name
      WHERE column1+column2+columnX like '%criteria%'

answered Dec 5, 2012 at 3:08

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GayanSanjeewa · Accepted Answer · 2012-12-05 22:53:56Z

1

You can do this purely in SQL as well. But as you have already done this in C# and you need only to get the list of columns based on the table name, try this.

Create a SQL udf as below.

CREATE FUNCTION funcReturnAllColumns
(
    @tableName VARCHAR(50)
)
RETURNS VARCHAR(500)
BEGIN

DECLARE @ID INT
DECLARE @ALLColumns VARCHAR(500)

SET @ALLColumns = ''

SELECT @ID = id
FROM sys.sysobjects
WHERE name = @tableName


SELECT @ALLColumns = @ALLColumns + '+' + name 
FROM sys.syscolumns
WHERE id = @ID

RETURN SUBSTRING(@ALLColumns,2,LEN(@ALLColumns))

END


SELECT dbo.funcReturnAllColumns('table_name')

OUTPUT: Column1 + Column2 + ..... + ColumN

You may have to adjust varchar limits, validations as required.

answered Dec 5, 2012 at 22:53

GayanSanjeewa

3663 silver badges13 bronze badges

Collectives™ on Stack Overflow

sql create string variable with column names

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related