locked
How to pass temp table column to stored procedure input parameter without using while loop RRS feed

  • Question

  • User325386820 posted

    How to pass temp table column to stored procedure input parameter without using while loop..

    My SP will accept one input parameter.

    Create PROCEDURE dbo.GetShiftStartDate
    (
          @BatchDate Datetime
    )
    AS
        BEGIN
        -------
        ----
    END
    dbo.GetshiftStarDate @DateTime

    I have temp table like this..

    ID             EmpName       Date
    ------------------------------------------
    1              Pramod         2016-04-11 18:30:00.000
    2              Sachin         2014-05-11 18:30:00.000
    3              Sehwag         2015-09-11 18:30:00.000
    4              Yuvi           2015-12-11 18:30:00.000

    I want to pass date field from temp table to SP input parameter without using while loop..

    Sp will return like this..

    ShiftId      ShiftName       ShiftStartDate
    ------------------------------------------------------
    2              Noshift           2015-12-31 18:30:00.000
    3              Morning           2016-02-11 18:30:00.000

    Please help me..

    Saturday, April 23, 2016 8:05 AM

Answers

  • User475983607 posted

    1I believe you have a design issue because it looks like you are trying to figure out what shift an employee worked by the date rather then simply using a lookup table.   Plus you have a no shift which must be related to the date or maybe the shifts are dynamic?  Anyway, it does not make sense to me...  When I think of work shifts I think of 3 eight hours shifts per day.

    Here is a simple example of figuring out a shift given a date.

    if OBJECT_ID('tempdb..#MyTempTable') is not null
    	DROP TABLE #MyTempTable
    
    if OBJECT_ID('tempdb..#Shift') is not null
    	DROP TABLE #Shift
    
    CREATE TABLE #MyTempTable
    (
    	Id		INT,
    	EmpName	VARCHAR(16),
    	[Date]	DATETIME
    )
    
    CREATE TABLE #Shift
    (
    	ShiftId		INT,
    	ShiftName	VARCHAR(64),
    	StartTime	TIME,
    	EndTime		TIME
    
    )
    
    INSERT INTO #MyTempTable(Id, EmpName, [Date])
    VALUES(1, 'Pramod', '2016-04-11 18:30:00.000'),
    (2, 'Sachin', '2014-05-11 18:30:00.000'),
    (3, 'Sehwag', '2015-09-11 18:30:00.000'),
    (4, 'Yuvi', '2015-12-11 18:30:00.000');
    
    
    INSERT INTO #Shift(ShiftId, ShiftName, StartTime, EndTime)
    VALUES(1, 'First Shift', '08:00:00.000', '16:00:00.000'),
    (2, 'Second Shift', '16:00:00.000', '23:59:59.999'),
    (3, 'Third Shift', '00:00:00.000', '08:00:00.000');
    
    SELECT  EmpName, 
    		[Date],
    		(SELECT ShiftId FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftId,
    		(SELECT ShiftName FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftName
    FROM #MyTempTable AS t

    Results

    EmpName          Date                    ShiftId     ShiftName
    ---------------- ----------------------- ----------- -------------
    Pramod           2016-04-11 18:30:00.000 2           Second Shift
    Sachin           2014-05-11 18:30:00.000 2           Second Shift
    Sehwag           2015-09-11 18:30:00.000 2           Second Shift
    Yuvi             2015-12-11 18:30:00.000 2           Second Shift

    I think you have a design issue that needs to be worked out.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 2:58 PM
  • User475983607 posted

    Here's another example where the start times are different

    SET NOCOUNT ON;
    if OBJECT_ID('tempdb..#MyTempTable') is not null
    	DROP TABLE #MyTempTable
    
    if OBJECT_ID('tempdb..#Shift') is not null
    	DROP TABLE #Shift
    
    CREATE TABLE #MyTempTable
    (
    	Id		INT,
    	EmpName	VARCHAR(16),
    	[Date]	DATETIME
    )
    
    CREATE TABLE #Shift
    (
    	ShiftId		INT,
    	ShiftName	VARCHAR(64),
    	StartTime	TIME,
    	EndTime		TIME
    
    )
    
    INSERT INTO #MyTempTable(Id, EmpName, [Date])
    VALUES(1, 'Pramod', '2016-04-11 18:30:00.000'),
    (2, 'Sachin', '2014-05-11 08:30:00.000'),
    (3, 'Sehwag', '2015-09-11 18:30:00.000'),
    (4, 'Yuvi', '2015-12-11 01:30:00.000');
    
    
    INSERT INTO #Shift(ShiftId, ShiftName, StartTime, EndTime)
    VALUES(1, 'First Shift', '08:00:00.000', '16:00:00.000'),
    (2, 'Second Shift', '16:00:00.000', '23:59:59.999'),
    (3, 'Third Shift', '00:00:00.000', '08:00:00.000');
    
    SELECT  EmpName, 
    		[Date],
    		(SELECT ShiftId FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftId,
    		(SELECT ShiftName FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftName
    FROM #MyTempTable AS t
    
    SET NOCOUNT ON;

    Results

    EmpName          Date                    ShiftId     ShiftName
    ---------------- ----------------------- ----------- --------------
    Pramod           2016-04-11 18:30:00.000 2           Second Shift
    Sachin           2014-05-11 08:30:00.000 1           First Shift
    Sehwag           2015-09-11 18:30:00.000 2           Second Shift
    Yuvi             2015-12-11 01:30:00.000 3           Third Shift
    



    .

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 3:19 PM

All replies

  • User475983607 posted

    Local Temp tables are available to the current user throughout the connection.  There's no need to pass a temp table as the temp table is simply available during the current connection.  Global temp tables are available to all users until there are no more users referencing the table.  And finally, table variables are scoped within a batch. 

    Here's is a proc that selects all the records of a temp table name #MytempTable.

    IF EXISTS ( SELECT * 
                FROM   sysobjects 
                WHERE  id = object_id(N'[dbo].[SelectTempTable]') 
                       and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    BEGIN
        DROP PROCEDURE [dbo].[SelectTempTable]
    END
    GO
    
    CREATE PROCEDURE dbo.SelectTempTable
    AS 
    BEGIN
      SELECT * 
      FROM #MyTempTable
    END
    GO

    The script populates the temp table then invokes the SelectTempTable proc.

    if OBJECT_ID('tempdb..#MyTempTable') is not null
    	DROP TABLE #MyTempTable
    
    CREATE TABLE #MyTempTable
    (
    	Id		INT,
    	Name	VARCHAR(16)
    )
    
    INSERT INTO #MyTempTable(Id, Name)
    VALUES(1, 'Foo'),(2, 'Bar');
    
    EXECUTE dbo.SelectTempTable

    It works because the temp table is created in the same connection where the procedure is called.  If the temp table was not created then the procedure would throw and error; Invalid object name '#MyTempTable'.

    This code snippet is not meant for production. It is simply to illustrate how temp tables work.

    Saturday, April 23, 2016 12:32 PM
  • User325386820 posted

    Hai.. 
    Thanks for your reply...
    My question is different that your answers.
    I want to pass date column field(from temp table) to SP. But my SP will accept input parameter as DATETIME.
    I need to pass each and every row of date column from temp table to SP
    We can achieve this by using while loop. But better performance I don't want to use it.
    How to achieve this.

    Saturday, April 23, 2016 1:17 PM
  • User475983607 posted

    pammu141


    Hai.. <br>
    Thanks for your reply...<br>
    My question is different that your answers.<br>
    I want to pass date column field(from temp table) to SP. But my SP will accept input parameter as DATETIME.<br>
    I need to pass each and every row of date column from temp table to SP<br>
    We can achieve this by using while loop. But better performance I don't want to use it.<br>
    How to achieve this.

    You basically asked how to invoke set logic on a temp table within a proc.  The example above explicitly demonstrates how to accomplish this.  It's up to you to join the temp table to the shift table.  Again, a temp table is available to the current connection.  Maybe you have a different definition of a temp table?

    I guess the temp table is in code base and not SQL? If so, this is a design issue.  Consider moving the result set from the code base to SQL.  Then you're just doing a join in the proc.

    Saturday, April 23, 2016 1:32 PM
  • User325386820 posted

    I did some paper work did you look into this..

    I need to do like this..I(t can be done by using while loop. but i don't want to use while loop in my DB.

    Thanks..

    Saturday, April 23, 2016 1:55 PM
  • User475983607 posted

    pammu141

    I did some paper work did you look into this..

    I need to do like this..I(t can be done by using while loop. but i don't want to use while loop in my DB.

    Your question is unclear.  Post the while loop code and the relevant DB schema.

    Saturday, April 23, 2016 2:12 PM
  • User325386820 posted

    Hai..

    This is the table I need to pass date column to SP.

    Row_Number    ID             EmpName            Date
    --------------------------------------------------------------------
      1            1             Pramod         2016-04-11 18:30:00.000
      2            2             Sachin         2014-05-11 18:30:00.000
      3            3             Sehwag         2015-09-11 18:30:00.000
      4            4             Yuvi           2015-12-11 18:30:00.000

    Sudo code or sample code

    Declare @DateTime datetime, @Count int
    DECLARE @temptbl TABLE
    	    (
    		    ShiftId        INT,
    		    ShiftName      NVARCHAR(50),
    		    ShiftStartdate DATETIME
    	    );
    select @Count = 0
    While(@Count <= (select count(*) from tbl)
    (
        select @DateTime = Date from tbl where row_number = @count
        
    	    INSERT INTO @temptbl
    	    (
    		  ShiftId,
    		  ShiftName,
    		  ShiftStartdate
    	    )
    	    EXEC tcd.getshiftstartdate @DateTime
    	    @Count = @Count + 1;
    )


    Here I dont want to use while loop. Becoz If I has 1 lakh records it will decrease performance..

    And I want to join @temptbl to tbl. I mean for that emp what is the shiftid and shiftname and shiftdate,

    ID         EmpName       Date                      ShiftId   ShiftName       ShiftStartDate
    ----------------------------------------------------------------------------------------------------------
    1          Pramod        2016-04-11 18:30:00.000     2       NoShift         2015-12-31 18:30:00.000
    2          s--------     2016-04-11 18:30:00.000     3       Morning         2013-01-21 18:30:00.000
    3          ---------     2016-04-11 18:30:00.000     3       Morning         2015-12-31 18:30:00.000
    4          ---------     2016-04-11 18:30:00.000     2       NoShift         2015-12-31 18:30:00.000

    Saturday, April 23, 2016 2:38 PM
  • User325386820 posted

    Sp Returns like this

    SP Returns like this
    
    ShiftId      ShiftName       ShiftStartDate
    ------------------------------------------------------
    2              Noshift         2015-12-31 18:30:00.000
    

    Saturday, April 23, 2016 2:55 PM
  • User475983607 posted

    1I believe you have a design issue because it looks like you are trying to figure out what shift an employee worked by the date rather then simply using a lookup table.   Plus you have a no shift which must be related to the date or maybe the shifts are dynamic?  Anyway, it does not make sense to me...  When I think of work shifts I think of 3 eight hours shifts per day.

    Here is a simple example of figuring out a shift given a date.

    if OBJECT_ID('tempdb..#MyTempTable') is not null
    	DROP TABLE #MyTempTable
    
    if OBJECT_ID('tempdb..#Shift') is not null
    	DROP TABLE #Shift
    
    CREATE TABLE #MyTempTable
    (
    	Id		INT,
    	EmpName	VARCHAR(16),
    	[Date]	DATETIME
    )
    
    CREATE TABLE #Shift
    (
    	ShiftId		INT,
    	ShiftName	VARCHAR(64),
    	StartTime	TIME,
    	EndTime		TIME
    
    )
    
    INSERT INTO #MyTempTable(Id, EmpName, [Date])
    VALUES(1, 'Pramod', '2016-04-11 18:30:00.000'),
    (2, 'Sachin', '2014-05-11 18:30:00.000'),
    (3, 'Sehwag', '2015-09-11 18:30:00.000'),
    (4, 'Yuvi', '2015-12-11 18:30:00.000');
    
    
    INSERT INTO #Shift(ShiftId, ShiftName, StartTime, EndTime)
    VALUES(1, 'First Shift', '08:00:00.000', '16:00:00.000'),
    (2, 'Second Shift', '16:00:00.000', '23:59:59.999'),
    (3, 'Third Shift', '00:00:00.000', '08:00:00.000');
    
    SELECT  EmpName, 
    		[Date],
    		(SELECT ShiftId FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftId,
    		(SELECT ShiftName FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftName
    FROM #MyTempTable AS t

    Results

    EmpName          Date                    ShiftId     ShiftName
    ---------------- ----------------------- ----------- -------------
    Pramod           2016-04-11 18:30:00.000 2           Second Shift
    Sachin           2014-05-11 18:30:00.000 2           Second Shift
    Sehwag           2015-09-11 18:30:00.000 2           Second Shift
    Yuvi             2015-12-11 18:30:00.000 2           Second Shift

    I think you have a design issue that needs to be worked out.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 2:58 PM
  • User475983607 posted

    Here's another example where the start times are different

    SET NOCOUNT ON;
    if OBJECT_ID('tempdb..#MyTempTable') is not null
    	DROP TABLE #MyTempTable
    
    if OBJECT_ID('tempdb..#Shift') is not null
    	DROP TABLE #Shift
    
    CREATE TABLE #MyTempTable
    (
    	Id		INT,
    	EmpName	VARCHAR(16),
    	[Date]	DATETIME
    )
    
    CREATE TABLE #Shift
    (
    	ShiftId		INT,
    	ShiftName	VARCHAR(64),
    	StartTime	TIME,
    	EndTime		TIME
    
    )
    
    INSERT INTO #MyTempTable(Id, EmpName, [Date])
    VALUES(1, 'Pramod', '2016-04-11 18:30:00.000'),
    (2, 'Sachin', '2014-05-11 08:30:00.000'),
    (3, 'Sehwag', '2015-09-11 18:30:00.000'),
    (4, 'Yuvi', '2015-12-11 01:30:00.000');
    
    
    INSERT INTO #Shift(ShiftId, ShiftName, StartTime, EndTime)
    VALUES(1, 'First Shift', '08:00:00.000', '16:00:00.000'),
    (2, 'Second Shift', '16:00:00.000', '23:59:59.999'),
    (3, 'Third Shift', '00:00:00.000', '08:00:00.000');
    
    SELECT  EmpName, 
    		[Date],
    		(SELECT ShiftId FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftId,
    		(SELECT ShiftName FROM #Shift WHERE CAST([Date] AS TIME) between StartTime AND EndTime) AS ShiftName
    FROM #MyTempTable AS t
    
    SET NOCOUNT ON;

    Results

    EmpName          Date                    ShiftId     ShiftName
    ---------------- ----------------------- ----------- --------------
    Pramod           2016-04-11 18:30:00.000 2           Second Shift
    Sachin           2014-05-11 08:30:00.000 1           First Shift
    Sehwag           2015-09-11 18:30:00.000 2           Second Shift
    Yuvi             2015-12-11 01:30:00.000 3           Third Shift
    



    .

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 3:19 PM
  • User325386820 posted

    Hi..

    For shift calculation I have done in stored procedure...`

    No need to think about shift id and shift date. I have already implement logic in SP.

    Just want to send date from temtable to SP input parameter

    so I need to execute SP for shift id.. 

    Saturday, April 23, 2016 4:09 PM
  • User-219423983 posted

    Hi pammu141,

    I think you’d better have a look at mgebhard’s code that could return the same result of what you want. The solution provided by mgebhard is a new method to do it with using SP.

    In your initial method, you let the calculation of getting shit information comes from the SP “tcd.getshiftstartdate”, it also could be considered to be a function. Then you build a new SP to traverse the temp table to calculate each row. But in this new SP, it would traverse the entire table for each row, it would be very expensive and a bad design.

    In mgebhard’s suggestion, it first define the Shit result table that contains all the possible outcomes. Then, it just traverses the entire temp table one time to get the result. During traversing, it just traverses the Shit result table for each row and put the calculation of getting shit information in each row. This would be cheaper than your design, because the shit table is much smaller than the temp table as usual.

    Best Regards,

    Weibo Zhang

    Monday, April 25, 2016 2:23 AM