我可以从编码端传递列名作为SQL存储过程中的参数吗？ [英] Can I pass column name as parameter in SQL stored Procedure from coding side ?

问题描述

我想从编码方而不是数据库方传递列名。

因为我有一个名为'studentAttendance'的表，其中包含d1，d2，d3，d4 ... d30等列这列的每日出勤率都有'P'，'A'，'L'，'C'等值。

Plz确实建议一个正确的方法。

 声明  @ col   nchar （ 50 ）
  set  @col = '  d2' 
 
 从 bat.studentbatch中选择 count（*） sb 
  inner   join  bat.student_attendance sa  on  sb.sid = sa.sid 
  INNE r   join  bat.batchmaster bm  on  sb.batchid = bm.batchid 
 其中 bm.facultyid = 151   -   和bm.batchstatus ！='已完成' 
 和  @ col  = '  P' 

但是这个查询应该得到3答案，但它总是给出0，意味着这不作为有效的列名或其他..

解决方案

试试这个...

  DECLARE   @@ SQL   VARCHAR （ 2000 ） 
  DECLARE   @ COL   VARCHAR （ 50 ）
 
  SET  @ COL = '  D2' 
 
  SET   @@ SQL  = '  SELECT COUNT（*）FROM BAT.STUDENTBATCH SB INNER JOIN BAT.SSTENTENT_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  

希望这对你有帮助。

是的你可以从编码方面传递列名作为参数但不是这样。您需要设计动态查询以将列名称添加到查询中。见下面的代码：

 声明  @ col   nchar （ 50 ）
 声明  @ query   nvarchar （max）
  set  @ col = '  d2' 
 
  SET   @ query  = ' 从bat.studentbatch中选择count（*）sb 
内部连接bat.student_attendance sa on sb.sid = sa.sid 
 inner join bat.batchmaster bm on sb.batchid = bm.batchid 
其中bm.facultyid = 151' +   -   和bm.batchstatus！=''已完成'' 
 ' 和 + @ col + '  =''P''' 
 
   -    SELECT @query  
  EXEC （ @ query ）

注意：评论不应包含在@Query

进一步解决方案1和Magic Wonder的评论你可能会发现这个有用的文章执行动态sql命令 [ ^ ]

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.

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

解决方案

Hi,

Try this ...

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.

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:

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

Further to solution 1 and the comment from Magic Wonder you might find this article useful execute dynamic sql commands[^]

这篇关于我可以从编码端传递列名作为SQL存储过程中的参数吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！