Click here to Skip to main content
14,640,725 members
Rate this:
Please Sign up or sign in to vote.
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
END
Posted
Updated 28-Jun-16 2:12am
v2
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

You 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, @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
SELECT @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 @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
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...
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[^]
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100