Click here to Skip to main content
15,860,859 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I want to pass column names from coding side rather than in database side.
coz i have a Table named 'studentAttendance' having columns like d1,d2,d3,d4...d30 and this columns have values like 'P','A','L','C' for daily attendance.
Plz do suggest a right way to do it.

SQL
declare @col nchar(50)
        set @col= 'd2'
        
            select count(*) from bat.studentbatch sb
            inner join bat.student_attendance sa on sb.sid=sa.sid
            inner join bat.batchmaster bm on sb.batchid=bm.batchid
            where  bm.facultyid=151-- and bm.batchstatus !='Completed'
            and  @col   ='P'

but this query should result "3" as answer but it always gives "0", means this does not take as valid column name or else..
Posted
Updated 21-Oct-19 10:13am
v3
Comments
OriginalGriff 4-Jul-14 6:01am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Why do you want to pass names as parameters?
What kind of query are we talking about?
What have you tried?
Use the "Improve question" widget to edit your question and provide better information.
Magic Wonder 4-Jul-14 6:24am    
Well, required comments already put by OriginalGriff. All i can say here and whatever i understand from your question is ....Use Dynamic SQL Query. Hope that will help you.

Hi,

Try this ...

SQL
DECLARE @@SQL VARCHAR(2000)
DECLARE @COL VARCHAR(50)

SET @COL= 'D2'

SET @@SQL =' SELECT COUNT(*) FROM BAT.STUDENTBATCH SB INNER JOIN BAT.STUDENT_ATTENDANCE SA ON SB.SID=SA.SID INNER JOIN BAT.BATCHMASTER BM ON SB.BATCHID=BM.BATCHID WHERE  BM.FACULTYID=151 AND ' +  @COL + '  =''P'' '

PRINT @@SQL

EXEC @@SQL


hope this will help you.
 
Share this answer
 
Yes you can pass column name as parameter from coding side but not in this way. You need to design a dynamic query to get column name into query. See below code:

SQL
declare @col nchar(50)
declare @query nvarchar(max)
set @col= 'd2'

SET @query = 'select count(*) from bat.studentbatch sb
            inner join bat.student_attendance sa on sb.sid=sa.sid
            inner join bat.batchmaster bm on sb.batchid=bm.batchid
            where  bm.facultyid=151'+-- and bm.batchstatus !=''Completed''
            'and '+ @col+' =''P'''

            --SELECT @query
EXEC(@query)


Note: comments should not include in @Query
 
Share this answer
 
Further to solution 1 and the comment from Magic Wonder you might find this article useful execute dynamic sql commands[^]
 
Share this answer
 
 
Share this answer
 
v3
Right - no it won't. Why not? Simple: it compiles the SQL too early for you, so it rightly assumes that @COL is a variable, not a column name, and at run time it compares the content of @COL against 'P' rather than using "the content of the column named by @COL".
You can do it - it's clumsy and dangerous because you have to have to concatenate the content of the variable together with the rest of the SQL into an string and execute that, which leaves you wide open to an SQL Injection problem and that could damage or destroy your database. I don;t recommend it.

Instead, check the content of @COL and execute the appropriate SELECT based on that.
SQL
IF @COL='d1'
    SELECT COUNT(*) FROM bat.studentbatch sb
    INNER JOIN bat.student_attendance sa ON sb.sid=sa.sid
    INNER JOIN bat.batchmaster bm ON sb.batchid=bm.batchid
    WHERE bm.facultyid = 151
       AND bm.batchstatus !='Completed'
       AND d1 ='P'
ELSE IF @COL='d2'
    SELECT COUNT(*) FROM bat.studentbatch sb
    INNER JOIN bat.student_attendance sa ON sb.sid=sa.sid
    INNER JOIN bat.batchmaster bm ON sb.batchid=bm.batchid
    WHERE bm.facultyid = 151
       AND bm.batchstatus !='Completed'
       AND d2 ='P'
...
ELSE
   SELECT -1
 
Share this answer
 
Comments
[no name] 17-Sep-17 5:51am    
how can i pass column name as parameter in sql command?
No, you *cannot* use column names as SQL parameters. Yes, you can replace text with other strings, however -- that was not the question being asked. The answer is NO.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900