带临时表的存储过程 [英] Stored procedure with temp table
问题描述
我想更改存储过程而不对文档名称进行硬编码并获得相同的结果。
我尝试了什么：
我的SP是：
ALTER PROCEDURE [dbo]。[SPEMPIDDETAILS]
AS
BEGIN
DECLARE @ INTSTART AS INTEGER;
DECLARE @ INTSTOP AS 整数;
DECLARE @ ENAME AS VARCHAR （ 50 ）;
DECLARE @ PREFNO AS VARCHAR （ 50 ）;
DECLARE @ PVAL AS DATE ;
DECLARE @ VREFNO AS VARCHAR （ 50 ）;
DECLARE @ VVAL AS DATE ;
DECLARE @ SBREFNO AS VARCHAR （ 50 ）;
DECLARE @ SBVAL AS VARCHAR （ 50 ）;
DECLARE @ UVREFNO AS VARCHAR （ 50 ）;
DECLARE @ UVVAL AS DATE ;
- 保存员工姓名的临时表
DECLARE @ TEMPTABLEEMPNAME TABLE （INTEMPNAME_ID INT IDENTITY （ 1 ， 1 ），
EMPNAME VARCHAR （ 50 ））
- 保存ID的临时表Ref.No和有效期
DECLARE @ TEMPTABLEEMP TABLE （INTEMPID_ID INT IDENTITY （ 1 ， 1 ），
ENAME VARCHAR （ 50 ），
PREFNO VARCHAR （ 50 ），PVAL DATE ，
VREFNO VARCHAR （ 50 ），VVAL DATE ，
SBREFNO < span class =code-keyword> VARCHAR （ 50 ），SBVAL DATE ，
UVREFNO VARCHAR （ 50 ），UVVAL DATE ）
INSERT INTO @TEMPTABLEEMPNAME （EMPNAME）
SEL ECT 员工
FROM EMPIDDETAILS
SELECT < span class =code-sdkkeyword> @ INTSTART = 1 ;
SELECT @ INTSTOP =（ SELECT COUNT（INTEMPNAME_ID） FROM @ TEMPTABLEEMPNAME ）;
WHILE @ INTSTART < = @INTSTOP
BEGIN
SET @ ENAME =（ SELECT EMPLOYEE
FROM EMPIDDETAILS
WHERE PK_ID = @ INTSTART ）
SELECT
@ PREFNO =（ SELECT REF_NO < span class =code-keyword> FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' PASSPORT' AND EMPLOYEE = @ ENAME ），
@ PVAL =（ SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' PASSPORT' AND EMPLOYEE = @ ENAME ）
SELECT
@ VREFNO = （ SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' VISA' AND EMPLOYEE = @ ENAME ），
@VVAL =（< span class =code-keyword> SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' VISA' AND EMPLOYEE = @ ENAME ）
SELECT
@ SBREFNO =（ SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' SEAMAN BOOK' AND EMPLOYEE = @ ENAME ），
@ SBVAL =（ SELECT VALIDITY FROM EMPIDDET AILS
WHERE DOCUMENT_TYPE = ' SEAMAN BOOK' AND EMPLOYEE = @ ENAME ）
SELECT
@ UVREFNO =（ SELECT REF_NO < span class =code-keyword> FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' USVISA' AND EMPLOYEE = @ ENAME ），
@ UVVAL =（ SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' USVISA' AND EMPLOYEE = @ ENAME ）
INSERT INTO @ TEMPTABLEEMP
VALUES （ @ENAME ， @ PREFNO ， @ PVAL ， @ VREFNO ， @ VVAL ， @ SBREFNO ， @ SBVAL ， @ UVREFNO ， @ UVVAL ）
SET @ ENAME = 0
SET @ PREFNO = 0
SET @ PVAL = NULL
SET @ VREFNO = 0
SET @VVAL = NULL
SET @ SBREFNO = 0
SET @ SBVAL = NULL
SET < span class =code-sdkkeyword> @ UVREFNO = 0
SET @ UVVAL = NULL
SET @ INTSTART = @ INTSTART + 1 ;
END
SELECT DISTINCT
ENAME AS EMPLOYEE，
PREFNO AS REF_NO ，PVAL as VALIDITY，
VREFNO AS REF_NO，VVAL AS 有效期，
SBREFNO AS REF_NO，SBVAL AS 有效期，
UVREFNO AS REF_NO，UVVAL AS VALIDITY
FROM
@ TEMPTABLEEMP
END
你正在以一种非常冗长的方式解决这个问题。例如
SELECT
@ PREFNO =（ SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' PASSPORT' AND EMPLOYEE = @ ENAME ），
@ PVAL =（ SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = < span class =code-string>' PASSPORT' AND EMPLOYEE = @ ENAME ）
可替换为
SELECT @ PREFNO = REF_NO， @ PVAL = VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = ' PASSPORT' AND EMPLOYEE = @ ENAME
...这同样适用于
@VREFNO，@VVAL，@SBREF，@SBVAL，@UVREF和@UVVAL
不需要表@TEMPTABLEEMPNAME- 您可以直接从该表中获取信息来自EMPIDDETAILS。例如SELECT @ INTSTOP =（ SELECT COUNT（ distinct （EMPLOYEE）） FROM EMPIDDETAILS）;但这也可能导致问题。您稍后使用
SET @ ENAME =（ SELECT EMPLOYEE
FROM EMPIDDETAILS
WHERE PK_ID = @ INTSTART ）
其中
@INSTART从1到1迭代员工人数 - 但如果您删除了员工记录怎么办？您可能没有匹配的记录，更重要的是，表上的最终PK_ID可能大于计数。目前还不清楚PK_ID究竟是什么......这个名字暗示它是一个主键，但它看起来好像它对于名称而不是名称+ DOCUMENT_TYPE是唯一的。无论哪种方式，最好使用SELECT @ INTSTOP =（ SELECT MAX（PK_ID） FROM EMPIDDETAILS）;确保您不会错过任何记录。
但是，不需要WHILE语句。 SQL-Server（大多数，如果不是所有RDBMS）都是 基于集合 - 无论你想要对每一行做什么，都可以描述一次并针对整个表执行。
例如这将获得与整个WHILE语句相同的数据到临时表中...SELECT A.EMPLOYEE，A.REF_NO，A。 VALIDITY，B.REF_NO，B.VALIDITY，C.REF_NO，C.VALIDITY，D.REF_NO，D.VALIDITY
FROM EMPIDDETAILS A
LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE = B.EMPLOYEE AND B.DOCUMENT_TYPE = ' VISA'
LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE = C.EMPLOYEE AND C.DOCUMENT_TYPE = ' SEAMAN BOOK'
LEFT OUTER JOIN EMPIDDETAILS D ON A .EMPLOYEE = D.EMPLOYEE AND D.DOCUMENT_TYPE = ' USVISA'
WHERE A.DOCUMENT_TYPE = ' PASSPORT'
事实上，一起摆脱临时表，这样你的整个SP就变成
ALTER PROCEDURE [dbo]。[SPEMPIDDETAILS]
AS
BEGIN
SELECT A.EMPLOYEE as ENAME，A.REF_NO AS PREFNO，A.VALIDITY AS PVAL，
B.REF_NO AS VREFNO，B.VALIDITY AS VVAL，
C.REF_NO AS SBREFNO，C。有效期SBVAL，
D.REF_NO AS UVREFNO，D.VALIDITY AS UVVAL
FROM EMPIDDETAILS A
LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE = B.EMPLOYEE AND B.DOCUMENT_TYPE = ' VISA'
LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE = C.EMPLOYEE AND C.DOCUMENT_TYPE = ' SEAMAN BOOK'
LEFT OUTER JOIN EMPIDDETAILS D < span class =code-keyword> ON A.EMPLOYEE = D.EMPLOYEE AND D.DOCUMENT_TYPE = ' USVISA'
WHERE A.DOCUMENT_TYPE = ' PASSPORT'
END
要回到原始问题，您可以将文档类型作为参数传递，而不是硬编码（请参阅如何使用参数创建SQL Server存储过程 [ ^ ]）
例如创建 PROCEDURE [dbo]。[SPEMPIDDETAILS]
@ DocType1 varchar （ 20 ）= ' PASSPORT'，
@ DocType2 varchar （ 20 ）= ' VISA'，
@ DocType3 varchar （ 20 ）= ' SEAMAN BOOK'，
@ DocType4 varchar （ 20 ）= ' USVIS '
AS
BEGIN
SELECT A.EMPLOYEE as ENAME，A.REF_NO AS PREFNO，A.VALIDITY AS PVAL，
B.REF_NO AS VREFNO ，B.VALIDITY AS VVAL，
C.REF_NO AS SBREFNO，C.VALIDITY SBVAL，
D.REF_NO AS UVREFNO，D.VALIDITY AS UVVAL
FROM EMPIDDETAILS A
LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE = B.EMPLOYEE AND B.DOCUMENT_TYPE = @ DocType2
LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE = C.EMPLOYEE AND C.DOCUMENT_TYPE = @ DocType3
LEFT OUTER JOIN EMPIDDETAILS D ON A.EMPLOYEE = D.EMPLOYEE AND D.DOCUMENT_TYPE = @ DocType4
WHERE A.DOCUMENT_TYPE = @ DocType1
END
我没有对你的表架构发表过多评论但是我怀疑应该至少再进行一次标准化 - 请参阅 1NF，2NF，3NF和数据库规范化中的BCNF | DBMS教程| Studytonight [ ^ ]
I want to change the stored procedure without hardcoded the document name and get the same result.
What I have tried:
My SP is:
ALTER PROCEDURE [dbo].[SPEMPIDDETAILS]
AS
BEGIN
DECLARE @INTSTART AS INTEGER;
DECLARE @INTSTOP AS INTEGER;
DECLARE @ENAME AS VARCHAR(50);
DECLARE @PREFNO AS VARCHAR(50);
DECLARE @PVAL AS DATE;
DECLARE @VREFNO AS VARCHAR(50);
DECLARE @VVAL AS DATE;
DECLARE @SBREFNO AS VARCHAR(50);
DECLARE @SBVAL AS VARCHAR(50);
DECLARE @UVREFNO AS VARCHAR(50);
DECLARE @UVVAL AS DATE;
--Temp Table for Save Employee Name
DECLARE @TEMPTABLEEMPNAME TABLE (INTEMPNAME_ID INT IDENTITY(1,1),
EMPNAME VARCHAR(50))
--Temp Table for Save ID Ref.No and Validity
DECLARE @TEMPTABLEEMP TABLE (INTEMPID_ID INT IDENTITY(1,1),
ENAME VARCHAR(50),
PREFNO VARCHAR(50), PVAL DATE,
VREFNO VARCHAR(50), VVAL DATE,
SBREFNO VARCHAR(50), SBVAL DATE,
UVREFNO VARCHAR(50), UVVAL DATE)
INSERT INTO @TEMPTABLEEMPNAME(EMPNAME)
SELECT EMPLOYEE
FROM EMPIDDETAILS
SELECT @INTSTART = 1;
SELECT @INTSTOP = (SELECT COUNT(INTEMPNAME_ID) FROM @TEMPTABLEEMPNAME);
WHILE @INTSTART <= @INTSTOP
BEGIN
SET @ENAME = (SELECT EMPLOYEE
FROM EMPIDDETAILS
WHERE PK_ID = @INTSTART)
SELECT
@PREFNO = (SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME),
@PVAL = (SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME)
SELECT
@VREFNO = (SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'VISA' AND EMPLOYEE = @ENAME),
@VVAL = (SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'VISA' AND EMPLOYEE = @ENAME)
SELECT
@SBREFNO = (SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'SEAMAN BOOK' AND EMPLOYEE = @ENAME),
@SBVAL = (SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'SEAMAN BOOK' AND EMPLOYEE = @ENAME)
SELECT
@UVREFNO = (SELECT REF_NO FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'USVISA' AND EMPLOYEE = @ENAME),
@UVVAL = (SELECT VALIDITY FROM EMPIDDETAILS
WHERE DOCUMENT_TYPE = 'USVISA' AND EMPLOYEE = @ENAME)
INSERT INTO @TEMPTABLEEMP
VALUES(@ENAME, @PREFNO, @PVAL, @VREFNO, @VVAL, @SBREFNO, @SBVAL, @UVREFNO, @UVVAL)
SET @ENAME = 0
SET @PREFNO = 0
SET @PVAL = NULL
SET @VREFNO = 0
SET @VVAL = NULL
SET @SBREFNO = 0
SET @SBVAL = NULL
SET @UVREFNO = 0
SET @UVVAL = NULL
SET @INTSTART = @INTSTART + 1;
END
SELECT DISTINCT
ENAME AS EMPLOYEE,
PREFNO AS REF_NO, PVAL as VALIDITY,
VREFNO AS REF_NO, VVAL AS VALIDITY,
SBREFNO AS REF_NO, SBVAL AS VALIDITY,
UVREFNO AS REF_NO, UVVAL AS VALIDITY
FROM
@TEMPTABLEEMP
ENDYou are going about this in a very long-winded way. For example
SELECT @PREFNO = (SELECT REF_NO FROM EMPIDDETAILS WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME), @PVAL = (SELECT VALIDITY FROM EMPIDDETAILS WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME)could be replaced with
SELECT @PREFNO = REF_NO, @PVAL = VALIDITY FROM EMPIDDETAILS WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME...and the same applies to
@VREFNO,@VVAL,@SBREF,@SBVAL,@UVREFand@UVVAL
There is no need for the table@TEMPTABLEEMPNAME- you can get the information you get from that table directly fromEMPIDDETAILS. For exampleSELECT @INTSTOP = (SELECT COUNT(distinct(EMPLOYEE)) FROM EMPIDDETAILS);but that can also lead to problems. You later use
SET @ENAME = (SELECT EMPLOYEE FROM EMPIDDETAILS WHERE PK_ID = @INTSTART)where
@INSTARTiterates from 1 through to the number of employees - but what if you have deleted an employee record? You might not have a matching record, and more importantly the final PK_ID on the table might be larger than the count. It's not clear what PK_ID actually is ... the name implies that it is a Primary Key but it also looks as if it is unique to the name not the name + DOCUMENT_TYPE. Either way it would have been better to useSELECT @INTSTOP = (SELECT MAX(PK_ID) FROM EMPIDDETAILS);to ensure you don't miss any records.
However, there is no need for theWHILEstatement. SQL-Server (most, if not all RDBMS) are Set-based - whatever you want to be done with each row can be described once and executed against the whole table.
E.g. This gets the same data into your temporary table as the entire WHILE statement...SELECT A.EMPLOYEE, A.REF_NO, A.VALIDITY, B.REF_NO, B.VALIDITY, C.REF_NO, C.VALIDITY, D.REF_NO, D.VALIDITY FROM EMPIDDETAILS A LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE=B.EMPLOYEE AND B.DOCUMENT_TYPE = 'VISA' LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE=C.EMPLOYEE AND C.DOCUMENT_TYPE = 'SEAMAN BOOK' LEFT OUTER JOIN EMPIDDETAILS D ON A.EMPLOYEE=D.EMPLOYEE AND D.DOCUMENT_TYPE = 'USVISA' WHERE A.DOCUMENT_TYPE = 'PASSPORT'In fact, get rid of the temporary table all together so your entire SP becomes
ALTER PROCEDURE [dbo].[SPEMPIDDETAILS] AS BEGIN SELECT A.EMPLOYEE as ENAME, A.REF_NO AS PREFNO, A.VALIDITY AS PVAL, B.REF_NO AS VREFNO, B.VALIDITY AS VVAL, C.REF_NO AS SBREFNO, C.VALIDITY SBVAL, D.REF_NO AS UVREFNO, D.VALIDITY AS UVVAL FROM EMPIDDETAILS A LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE=B.EMPLOYEE AND B.DOCUMENT_TYPE = 'VISA' LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE=C.EMPLOYEE AND C.DOCUMENT_TYPE = 'SEAMAN BOOK' LEFT OUTER JOIN EMPIDDETAILS D ON A.EMPLOYEE=D.EMPLOYEE AND D.DOCUMENT_TYPE = 'USVISA' WHERE A.DOCUMENT_TYPE = 'PASSPORT' END
To get back to your original question, you can pass the document types through as parameters instead of being hard-coded (see How to create a SQL Server stored procedure with parameters[^])
E.g.CREATE PROCEDURE [dbo].[SPEMPIDDETAILS] @DocType1 varchar(20) = 'PASSPORT', @DocType2 varchar(20) = 'VISA', @DocType3 varchar(20) = 'SEAMAN BOOK', @DocType4 varchar(20) = 'USVISA' AS BEGIN SELECT A.EMPLOYEE as ENAME, A.REF_NO AS PREFNO, A.VALIDITY AS PVAL, B.REF_NO AS VREFNO, B.VALIDITY AS VVAL, C.REF_NO AS SBREFNO, C.VALIDITY SBVAL, D.REF_NO AS UVREFNO, D.VALIDITY AS UVVAL FROM EMPIDDETAILS A LEFT OUTER JOIN EMPIDDETAILS B ON A.EMPLOYEE=B.EMPLOYEE AND B.DOCUMENT_TYPE = @DocType2 LEFT OUTER JOIN EMPIDDETAILS C ON A.EMPLOYEE=C.EMPLOYEE AND C.DOCUMENT_TYPE = @DocType3 LEFT OUTER JOIN EMPIDDETAILS D ON A.EMPLOYEE=D.EMPLOYEE AND D.DOCUMENT_TYPE = @DocType4 WHERE A.DOCUMENT_TYPE = @DocType1 END
I haven't commented much on your table schema but I suspect there is at least one more level of normalisation that should be done - see 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]
这篇关于带临时表的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
