如何编写引用变量的select语句 [英] how to write select statement that has quoted variables

问题描述

我有以下选择

，用于测试具有特定表名的特定架构是否具有特定列

i have the following select
that tests if specific schema with specific table name has specific column

Declare @ColumnName nvarchar(255)
Declare @ColumnType nvarchar(255)
Declare @ColumnLength nvarchar(255)
Declare @DataBaseName nvarchar(255)

SELECT @ColumnName=COLUMN_NAME,@ColumnType=DATA_TYPE,@ColumnLength=CHARACTER_MAXIMUM_LENGTH
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME =  @TableName
          and COLUMN_NAME = @ColumnName  and TABLE_SCHEMA=@DataBaseName

我的问题是变量必须在select语句中引用，如果我把语句放在一个字符串然后执行字符串作为解决方案@ ColumnName，@ ColumnType，@ ColumnLength将没有值，除非我把它们作为out参数，我最近尝试这种方式，但它没有工作

有什么方法

来处理这个问题，这个问题总是面对我，让我处于无人状态





抱歉我没有用一个好的方式描述我的意思是引用变量

where子句中的变量

i会写查询agian

折叠|复制代码



声明@ColumnName nvarchar（255）

声明@ColumnType nvarchar（255）

声明@ ColumnLength nvarchar（255）

声明@DataBaseName nvarchar（255）

声明@x nvarchar（255）



SELECT @ x = COLUMN_NAME，@ ColumnType = DATA_TYPE，@ ColumnLength = CHARACTER_MAXIMUM_LENGTH

来自INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

和COLUMN_NAME = @ColumnName和TABLE_SCHEMA = @ DataBaseName



变量@TableName，@ ColumnName，@ DataBaseName必须引用

假设我必须写以下查询



SELECT @ x = COLUMN_NAME，@ ColumnType = DATA_TYPE，@ ColumnLength = CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME ='TableA'

和COLUMN_NAME ='ColumnA'和TABLE_SCHEMA ='DataBaseA'



所以我的问题是怎么能我在where语句中引用变量而不在字符串中编写查询语句，这导致我将变量放在select语句中作为out变量

i意味着我需要变量在哪里使用双引号解析字符串

My Problem is that the variables must be quoted in select statement and if i put the statement in a string and then executes the string as a solution @ColumnName,@ColumnType,@ColumnLength will not have value unless i put them as out parameters and i tried this way recently but it didnt work
is there any way
to handle that cause this problem always faces me and put me in a state of hopless


Sorry i didnt discribe in a good way i mean by quoted variables
the variables in where clause
i will write the query agian
Collapse | Copy Code

Declare @ColumnName nvarchar(255)
Declare @ColumnType nvarchar(255)
Declare @ColumnLength nvarchar(255)
Declare @DataBaseName nvarchar(255)
Declare @x nvarchar(255)

SELECT @x=COLUMN_NAME,@ColumnType=DATA_TYPE,@ColumnLength=CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
and COLUMN_NAME = @ColumnName and TABLE_SCHEMA=@DataBaseName

the variables @TableName ,@ColumnName,@DataBaseName must be quoted
Suppose i must write the following query

SELECT @x=COLUMN_NAME,@ColumnType=DATA_TYPE,@ColumnLength=CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableA'
and COLUMN_NAME = 'ColumnA' and TABLE_SCHEMA='DataBaseA'

so my question is how can i quote the variables in where statement without writing query statement in string which leads me to put the variables in select statement as out variables
i mean i need the variables in where string to be parsed with double quotation

推荐答案

使用...

Either use...

Declare @ColumnLength int

匹配正确的类型

或...

to match up the correct type
Or...

,@ColumnLength=CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(255))

转换返回的 int 到你要存储的nvarchar。



你不需要引号。

to convert the returned int to the nvarchar you want to store it in.

You don't need the quotes.

对不起我没有用一个好的方式描述我的意思是引用变量

where子句中的变量

i将写入查询agian

sorry i didnt discribe in a good way i mean by quoted variables
the variables in where clause
i will write the query agian

Declare @ColumnName nvarchar(255)
Declare @ColumnType nvarchar(255)
Declare @ColumnLength nvarchar(255)
Declare @DataBaseName nvarchar(255)
Declare @x nvarchar(255)

SELECT @x=COLUMN_NAME,@ColumnType=DATA_TYPE,@ColumnLength=CHARACTER_MAXIMUM_LENGTH
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME =  @TableName
          and COLUMN_NAME = @ColumnName  and TABLE_SCHEMA=@DataBaseName

变量@TableName，@ ColumnName，@ DataBaseName必须引用

假设我必须写下面的查询



SELECT @ x = COLUMN_NAME，@ ColumnType = DATA_TYPE，@ ColumnLength = CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME ='TableA'

和COLUMN_NAME ='ColumnA'和TABLE_SCHEMA ='DataBaseA'



所以我的问题是如何引用where语句中的变量而不编写查询语句字符串，它导致我把变量放在select语句中作为输出变量

i意味着我需要用双引号解析字符串的变量

the variables @TableName ,@ColumnName,@DataBaseName must be quoted
Suppose i must write the following query

SELECT @x=COLUMN_NAME,@ColumnType=DATA_TYPE,@ColumnLength=CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableA'
and COLUMN_NAME = 'ColumnA' and TABLE_SCHEMA='DataBaseA'

so my question is how can i quote the variables in where statement without writing query statement in string which leads me to put the variables in select statement as out variables
i mean i need the variables in where string to be parsed with double quotation

这篇关于如何编写引用变量的select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！