How to put If condition where Column pass as a Parameter in SP

Question

0.00/5 (No votes)

See more:

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

SQL

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

Posted 6-Sep-13 0:46am

Varun_nayak

Add a Solution

3 solutions

Solution 1

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

SQL

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

Posted 6-Sep-13 1:02am

Mike Meinz

Updated 6-Sep-13 1:15am

v2

Comments

Varun_nayak 6-Sep-13 7:18am

NO its Not working..
I simply need to Execute SQL1 query when My table Batch_Master has column which we pass as a parameter
otherwise SQL2 query Execute.
Here every time "else" condition execute.

Solution 2

Quote:
first table (Batch_Master)

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

SQL

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

Posted 6-Sep-13 1:03am

Raja Sekhar S

Updated 6-Sep-13 2:03am

v4

Comments

Raja Sekhar S 6-Sep-13 7:22am

is it working...?

Varun_nayak 6-Sep-13 8:30am

NO, its not working. here you use two times SQL1 so, store procedure doesnt allow to save this sp.
check my answer.

Raja Sekhar S 7-Sep-13 0:51am

Really.... i got it... u haven't checked the solution....

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)

Varun_nayak · Accepted Answer · 2013-09-06T01:32:00

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

SQL

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