Assign multiple fields to one variable

Question

Assign multiple fields to one variable

Jeepisfun

Valued Member

Points: 62
More actions
September 8, 2021 at 8:14 am

#3927712

Hi
I need to write a query / procedure that will create the following string -
Column1Name=Column1Value;Column2Name=Column2Value...etc
I have no clue on how to do that with sql. In c# or vb.net is quite simple and straight forward.
It seems to me that in sql I have to know the columns names in advance, so I need a procedure for each table.
I'm not a sql programmer, so help will be greatly appreciated.
Thanks.

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Well, you could, with a ton of work, create a generic procedure that just needs a table name to make this work. You'd have to use dynamic SQL to build it out. You could query system tables to pull back info, something like this:

SELECT cu.name
FROM sys.tables AS t
JOIN sys.columns AS cu
ON cu.object_id = t.object_id
WHERE t.name = 'ContactType';

Then, you'd need to combine the results with an actual query of the same table to put together your string. Of course, you'll have to take into account that TableA has a primary key on the TableAID column, but TableB has a primary key on TableBDate & TableBGroupID, but TableC has a primary key on... You get the picture. You're not going to be able to simply pass the table name because you'll also have to pass the filter criteria and it's going to be different for every table, potentially, some numbers, some dates, some strings.

However, I wouldn't recommend this at all, for a bunch of reasons, not the least of which, it's going to be a giant pain in the bottom. Personally, this is the kind of thing I'd recommend you do in the application code or the reporting engine. Queries should be about querying. Leave funky formatting to coding methods that are better suited to it, not T-SQL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Jeepisfun wrote:

Hi
I need to write a query / procedure that will create the following string -
Column1Name=Column1Value;Column2Name=Column2Value...etc
I have no clue on how to do that with sql. In c# or vb.net is quite simple and straight forward.
It seems to me that in sql I have to know the columns names in advance, so I need a procedure for each table.
I'm not a sql programmer, so help will be greatly appreciated.
Thanks.

Sounds to me that you're trying to build the "C.R.U.D." (Create, Retrieve, Update, Delete) for each table.

Try the following Google search to find articles by folks that have already done such a thing.

https://www.google.com/search?q=automatically+create+crud+for+each+table+in+a+database+sql+server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)