SQL server 利用存储过程查询任意字段的信息

╰+攻爆jí腚メ 2022-01-30 02:07 213阅读 0赞

创建如下数据表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70]

CREATE TABLE EMP(
    EMPNO INT  PRIMARY KEY, -- 员工编号
    ENAME VARCHAR(10), -- 员工姓名
    JOB VARCHAR(9), -- 员工工作
    MGR INT, -- 员工直属领导编号
    HIREDATE DATE, -- 入职时间
    SAL FLOAT, -- 工资
    COMM FLOAT, -- 奖金
    DEPTNO INT,  -- 所在部门
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));  -- 关联dept表  
    
    -- ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
    INSERT INTO EMP VALUES(7369,'SMITH','职员',7566,'1980-12-17',800,NULL,20);
    INSERT INTO EMP VALUES(7499,'ALLEN','销售员',7698,'1981-02-20',1600,300,30);
    INSERT INTO EMP VALUES(7521,'WARD','销售员',7698,'1981-02-22',1250,500,30);
    INSERT INTO EMP VALUES(7566,'JONES','经理',7839,'1981-04-02',2975,NULL,20);
    INSERT INTO EMP VALUES(7654,'MARTIN','销售员',7698,'1981-09-28',1250,1400,30);
    INSERT INTO EMP VALUES(7698,'BLAKE','经理',7839,'1981-05-01',2850,NULL,30);
    INSERT INTO EMP VALUES(7782,'CLARK','经理',7839,'1981-06-09',2450,NULL,10);
    INSERT INTO EMP VALUES(7788,'SCOTT','职员',7566,'1987-07-03',3000,2000,20);
    INSERT INTO EMP VALUES(7839,'KING','董事长',NULL,'1981-11-17',5000,NULL,10);
    INSERT INTO EMP VALUES(7844,'TURNERS','销售员',7698,'1981-09-08',1500,50,30);
    INSERT INTO EMP VALUES(7876,'ADAMS','职员',7566,'1987-07-13',1100,NULL,20);
    INSERT INTO EMP VALUES(7900,'JAMES','职员',7698,'1981-12-03',1250,NULL,30);
    INSERT INTO EMP VALUES(7902,'FORD','销售员',7566,'1981-12-03',3000,NULL,20);
    INSERT INTO EMP VALUES(7934,'MILLER','职员',7782,'1981-01-23',1300,NULL,10);

现在想查询部门10中经理 和部门30中职员的编号和某个字段信息（如 员工的姓名）  
我开始是这么写的

if (exists (select * from sys.objects where name = 'search'))
        drop proc search
    GO
    CREATE PROC search(
    @some char(5))
    AS
    	BEGIN
    		SELECT EMPNO ,@some FROM EMP 
    		WHERE DEPTNO = 10 AND JOB = '经理' 
    		OR DEPTNO = 20 AND JOB = '职员';
    	END
    GO
    exec search 'ENAME'

执行结果是这样的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70 1]  
后来我问了问带我的师傅 他说要这么改

if (exists (select * from sys.objects where name = 'search'))
        drop proc search
    GO
    CREATE PROC search(
    @some char(5))
    AS
    	BEGIN
    		exec ('SELECT EMPNO ,'+@some+ ' FROM EMP '
    		+'WHERE DEPTNO = 10 AND JOB = ''经理'' '
    		+'OR DEPTNO = 20 AND JOB = ''职员''');
    	END
    GO
    exec search 'ENAME'

执行结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70 2]  
在编写查询任意字段的存储过程时，要注意将传入的变量“字段”化，即如果直接传入，在编译过程中，只会把该变量作为一个字符使用，而不会作为一个要查询字段名。改变方法是将查询语句及传入的变量以字符串的形式拼接，然后执行拼接后的查询语句。  
还有位大佬对比各种方法做了下分析，有兴趣的可以看一下  
[https://www.cnblogs.com/wy123/p/5958047.html][https_www.cnblogs.com_wy123_p_5958047.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70]: /images/20220130/923a1a6857784a87a46fdf50cc7bfcd2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70 1]: /images/20220130/1d519eb4c042411180a0b10b0a794921.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70 2]: /images/20220130/51ec019c7c9b4db09be79dac314a1f33.png
[https_www.cnblogs.com_wy123_p_5958047.html]: https://www.cnblogs.com/wy123/p/5958047.html