Stored procedure with temp table

Question

1.00/5 (2 votes)

See more:

I want to change the stored procedure without hardcoded the document name and get the same result.

What I have tried:

My SP is:

SQL

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
END

Posted 27-Jun-16 21:40pm

Kedarnath M S

Updated 28-Jun-16 2:12am

CHill60

v2

Add a Solution

Comments

CHill60 28-Jun-16 4:44am

Pass them through as parameters to the SP - How to create a SQL Server stored procedure with parameters[^]

ZurdoDev 28-Jun-16 7:29am

I don't understand what you are asking us.

1 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)

CHill60 · Answer 1 · 2016-06-28T02:12:00

You are going about this in a very long-winded way. For example

SQL

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

SQL

SELECT @PREFNO = REF_NO,  @PVAL = VALIDITY FROM EMPIDDETAILS
           WHERE DOCUMENT_TYPE = 'PASSPORT' AND EMPLOYEE = @ENAME

...and the same applies to @VREFNO, @VVAL, @SBREF, @SBVAL, @UVREF and @UVVAL

There is no need for the table @TEMPTABLEEMPNAME - you can get the information you get from that table directly from EMPIDDETAILS. For example

SQL

SELECT @INTSTOP = (SELECT COUNT(distinct(EMPLOYEE)) FROM EMPIDDETAILS);

but that can also lead to problems. You later use

SQL

SET @ENAME = (SELECT EMPLOYEE
              FROM EMPIDDETAILS
              WHERE PK_ID = @INTSTART)

where @INSTART iterates 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 use

SQL

SELECT @INTSTOP = (SELECT MAX(PK_ID) FROM EMPIDDETAILS);

to ensure you don't miss any records.

However, there is no need for the WHILE statement. 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...

SQL

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

SQL

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.

SQL

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[^]