locked
passing dates in stored procedure? RRS feed

  • Question

  • I am trying to pass dates in  a stored procedure and keep getting an error - here is the code and error.  eventually the sproc will be used in SSRS.

     

     

    ALTER PROC [dbo].[USP_SPROC_X]
    
    	@DATE DATETIME 
    AS
    
    
    SELECT ITEM
         , PRICE
         , SELL_DSCR 
         , PRC_EFF_DT 
         , PRC_END_DT 
    		 , PROPOSED_BY
    		 ,CONFIRMED_BY
    
         FROM SALES 
    WHERE @DATE BETWEEN PRC_EFF_DT AND PRC_END_DT
    

     

    Msg 4104, Level 16, State 1, Procedure [PROCNAME], Line 141

    The multi-part identifier "PRC_EFF_DT" could not be bound.

    Tuesday, August 31, 2010 4:16 AM

Answers

  • The error is not in the code you posted as long as the field the error is complaining about is indeed in the table. As I mentioned before, you need to look into other parts of the stored procedure so you can find the error that says it happened at line 141. It might be referring to another stored procedure.


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by KJian_ Tuesday, September 7, 2010 5:40 AM
    Tuesday, August 31, 2010 4:54 AM
  • some other procedure is executing..

    if you see the error, "Msg 4104, Level 16, State 1, Procedure [PROCNAME], Line 141"

    it is telling that, the stored procedure name is "ProcName" but your stored procedure name is USP_SPROC_X

     

    check that procedure and solve that issues.

    • Marked as answer by KJian_ Tuesday, September 7, 2010 5:40 AM
    Tuesday, August 31, 2010 4:56 AM

All replies

  • Hi,

    Does your SALES table have column PRC_EFF_DT?

    It looks like column does not exists in table.

     


    -Chintak
    • Proposed as answer by Kalman Toth Tuesday, August 31, 2010 4:24 AM
    • Unproposed as answer by siera_gld Tuesday, August 31, 2010 4:25 AM
    • Proposed as answer by Naomi N Tuesday, August 31, 2010 1:27 PM
    Tuesday, August 31, 2010 4:21 AM
  • yes - it does -

     

    this rpt works fine without paramaters - i just have to put them in.

    Tuesday, August 31, 2010 4:24 AM
  • Is this all the code for your stored procedure? The error happens on line 141, and apparently this procedure doesn't have all these lines?

    Do you have nested stored procedure? Please post the table structure, some test data, and the entire code for the sp for further assistance.

    Thank you


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, August 31, 2010 4:26 AM
  • I wish I could but cant
    Tuesday, August 31, 2010 4:46 AM
  • The error is not in the code you posted as long as the field the error is complaining about is indeed in the table. As I mentioned before, you need to look into other parts of the stored procedure so you can find the error that says it happened at line 141. It might be referring to another stored procedure.


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by KJian_ Tuesday, September 7, 2010 5:40 AM
    Tuesday, August 31, 2010 4:54 AM
  • some other procedure is executing..

    if you see the error, "Msg 4104, Level 16, State 1, Procedure [PROCNAME], Line 141"

    it is telling that, the stored procedure name is "ProcName" but your stored procedure name is USP_SPROC_X

     

    check that procedure and solve that issues.

    • Marked as answer by KJian_ Tuesday, September 7, 2010 5:40 AM
    Tuesday, August 31, 2010 4:56 AM
  • If you know that all these fields are indeed in the Sales table, when you probably didn't show us the whole code. Say, this procedure has only a few lines, so why do you get the error on Line 141?

    Are you calling some other SP from this one?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, August 31, 2010 1:30 PM
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, September 7, 2010 5:27 AM