How to concat two variable in SQL server using dynamic query

Question

0.00/5 (No votes)

See more:

I want to concat this two variables in to another..pls refer code

What I have tried:

SQL

-- Get primary key fields select for insert       
SELECT @PKFieldSelect = COALESCE(@PKFieldSelect+'+','') 
       + '''' + COLUMN_NAME +''''
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME  
       
select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

SQL

SET @PKSelect = 'select ' + @PKFieldSelect +  '+'',''+'  + @PKValueSelect + 'from'+ @TableName

or
like this

SQL

SET @PKSelect = @PKFieldSelect +   @PKValueSelect

Posted 13-Mar-16 0:42am

Member 11363211

Updated 15-Mar-16 6:48am

CHill60

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RahamanATR · Accepted Answer · 2016-03-15T06:48:00

Solution 2

C#

select CONCAT('EmployeeID',coalesce(i.EmployeeID,d.EmployeeID) )from Employees

Posted 15-Mar-16 6:48am

RahamanATR

Comments

CHill60 15-Mar-16 13:03pm

It would have been better to use the OP's variables as this query produces errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.EmployeeID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d.EmployeeID" could not be bound.

Member 11363211 16-Mar-16 8:04am

thank u so much sir ..i have got it

CHill60 · Accepted Answer · 2016-03-13T10:00:00

Solution 1

A word of advice - instead of just asking the question, actually TRY stuff.
You will learn more by experimenting that just asking questions.

If you would have done that then you would realise that the first option produces

SQL

select 'EmployeeID'+','+convert(varchar(100), coalesce(i.EmployeeID,d.EmployeeID))fromEmployees

and the second produces

C#

'EmployeeID'convert(varchar(100), coalesce(i.EmployeeID,d.EmployeeID))

(or whatever the appropriate bits and pieces are for the table you chose - I used the Employees table from the Northwind sample database from MSDN).

Whichever format of results you are after then the use the option that produces it.

Posted 13-Mar-16 10:00am

CHill60

Comments

Member 11363211 14-Mar-16 6:03am

Can u give me from above example which i have coded...Thanks

CHill60 14-Mar-16 9:48am

Give you what? I've told you what each of the queries produces but only you know which of the two formats is the one you want.
What are you expected results - give an example

Member 11363211 15-Mar-16 0:08am

Sir My expected result is two concat two variables in to another variable pls see above coded
example :SET @PKSelect = @PKFieldSelect + @PKValueSelect -- its not working just eg:
and i need to add single quotes for String
thanks

CHill60 15-Mar-16 6:42am

"its not working" does not help anyone to solve your issue.
Both of your attempts
1) SET @PKSelect = 'select ' + @PKFieldSelect + '+'',''+' + @PKValueSelect + 'from'+ @TableName
and
2) SET @PKSelect = @PKFieldSelect + @PKValueSelect
work.
Both those statements concatenate strings using the + operator
You already have the single quotes added to @PKFieldSelect e.g. '''' + COLUMN_NAME +''''
So give us a clue what the problem is, please!
I'm trying to help but you are not giving enough information (and having a solution voted down I'm still trying to get help is not encouraging me to spend more time on you)