看看我如何更改Select from Sys.Columns以使用@ColumnName中的值而不是'columnName'文字。

See how I changed the Select from Sys.Columns to use the value in @ColumnName instead of a 'columnName' literal.

ALTER PROCEDURE [dbo].[GETDynamic]

    @columnName varchar(50),
    @batchmId  int
AS

    DECLARE @SQL1 AS VARCHAR(MAX)
    DECLARE @SQL1 AS VARCHAR(MAX)

    SET @SQL1 = 'select ' + @columnName + ' from Batch_Master'

    SET @SQL2 = 'select GTIN_Master.'+@columnName+'
         from GTIN_Master inner join Batch_Master
         on GTIN_Master.GTIN = Batch_Master.GTIN
          where Batch_M_id =' + CONVERT(VARCHAR,@batchmId)

    If Exists(Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS 
              Where TABLE_NAME='Batch_Master' and COLUMN_NAME=@columnName)
        BEGIN
            EXEC (@SQL1)
        END
    ELSE
        BEGIN
            EXEC (@SQL2)
        END

我知道了。我将columnName作为字符串传递，我只是复制它。我应该使用变量

这是解决方案

i got it. I were passing the columnName as a string and i just copied it. I should use the variable instead
this is solution

ALTER PROCEDURE [dbo].[GETDynamic]

    @columnName varchar(50),
    @batchmId  int
AS

    DECLARE @SQL1 AS VARCHAR(MAX)
    DECLARE @SQL2 AS VARCHAR(MAX)

    SET @SQL1 = 'select  ' + @columnName + ' from Batch_Master'

    SET @SQL2  = 'select GTIN_Master. ' + @columnName + '
         from GTIN_Master inner join Batch_Master
         on GTIN_Master.GTIN = Batch_Master.GTIN
         where Batch_M_id =' + CONVERT(VARCHAR,@batchmId)

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName and Object_ID = Object_ID(N'Batch_Master'))
        BEGIN
            EXEC (@SQL1)
        END
    ELSE
        BEGIN
            EXEC (@SQL2)
        END

first table（Batch_Master）

first table (Batch_Master)

IF EXISTS（SELECT * FROM sys.columns WHERE Name = N'columnName'和Object_ID = Object_ID（' N Batch_Master'）） - N应该在''之外''像Object_ID（N'Batch_Master' ）

IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'columnName' and Object_ID = Object_ID('NBatch_Master'))-- N Should be outside '' like Object_ID(N'Batch_Master')

那就行了......你不需要写两个语句......你可以简化代码，因为

That will do the trick... u dont' need to write two statements.. u can simplify the code as

ALTER PROCEDURE [dbo].[GETDynamic]
    @columnName varchar(50),
    @batchmId  int
AS
Begin
    DECLARE @SQL1 AS VARCHAR(MAX),@SQL1 AS VARCHAR(MAX)
    If Exists(Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS 
              Where TABLE_NAME='Batch_Master' and COLUMN_NAME=@columnName
              )
       SET @SQL1 = 'select ' + @columnName + ' from Batch_Master'
    Else
        SET @SQL1 = 'select GTIN_Master.'+@columnName+' 
                    from GTIN_Master 
                    inner join Batch_Master on GTIN_Master.GTIN = Batch_Master.GTIN 
                    where Batch_M_id =' + CONVERT(VARCHAR,@batchmId)

    EXEC (@SQL1)
End