如何将列传递的条件作为SP中的参数传递 [英] How to put If condition where Column pass as a Parameter in SP

问题描述

我希望PassColumn名称作为我的SP中的一个参数

如果我的tat列存在于第一个表（Batch_Master）中，想要从该列中获取值，



如果我的第二个表格（GTIN_Master）中存在该列，想要从tat表格列中获取值，

每个表都有类似的列...

Batch_Master（Batch_M_id，GTIN（主键），等等

GTIN_Master（GTIN（外键），..等）



我有 Batch_M_id和列名称作为参数..



我试试以下SP

I want to PassColumn name as a paremeter in My SP
And if my tat column exists in first table (Batch_Master), want to fatch value from that column,

And if that column exists in my second table (GTIN_Master), want to fetch value from tat table column,
Each table have columns like..
Batch_Master (Batch_M_id, GTIN(primary key),....etc
GTIN_Master (GTIN (foreign key),..etc)

I have Batch_M_id, and column name as a parameter..

I Try following SP

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 * FROM sys.columns WHERE Name = N'columnName' and Object_ID = Object_ID('NBatch_Master'))
        BEGIN
            EXEC (@SQL1)
        END
    ELSE
        BEGIN
            EXEC (@SQL2)
        END

但它总是处于其他状态，从不检查是否有条件。

请帮助我纠正它!!

But it always go in else condition, never check if condition.
Kindly help me to correct it!!

推荐答案

看看我如何更改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)

Quote：

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

这篇关于如何将列传递的条件作为SP中的参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！