如何使用动态查询在SQL Server中连接两个变量 [英] How to concat two variable in SQL server using dynamic query

问题描述

我想将这两个变量连接到另一个..请参考代码



我尝试过：

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

What I have tried:

-- 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

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

或

像这样

or
like this

SET @PKSelect = @PKFieldSelect +   @PKValueSelect

推荐答案

建议之词 - 而不只是问问题，实际上是尝试的东西。

你将学习更多的是通过试验只是问问题。



如果你会这样做那么你就会意识到第一个选项会产生

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

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

，第二个产生

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

（或者您选择的表的任何适当的位和部分 - 我使用来自MSDN的Northwind示例数据库中的Employees表）。



无论您使用哪种格式的结果，都可以使用生成它的选项。

(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.

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

这篇关于如何使用动态查询在SQL Server中连接两个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！