Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: Re: Stored procedure not executing correctly

    Hi everyone,

    I am having trouble with this stored procedure. The @curr_type stores the first value of inspection type of the recordset , @inspec_type stores the current record of the inspection type. @inspec_type moves to the next record while @curr_type's value remain the same for comparisons.

    The error is in the Else statment. These variables,@curr_type and @inspec_type, values are = 3 the else statement should not be executed.

    Is something wrong with my code or logic?
    Thank you for any assistance.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Mind posting some code ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, without seeing it you want us to guess? Well, I guess that the ELSE is not executed because a check for a value of the variable does not take into account a possibility of NULL. Good enough, hey?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2002
    Posts
    395

    Re: stored procedues is not working

    so Sorry! absent minded me.

    Here is my code:

    CREATE PROCEDURE dbo.spCheck_inspection_type
    @inspec_type smallint Out, @curr_type smallint Out
    AS--ve

    declare insp_type cursor


    For
    Select inspection_type
    From tblTmp_inspec
    Where partial <> 0 or complete <> 0;
    set @curr_type = 0;

    Open insp_type
    Fetch Next From insp_type Into @inspec_type

    While @@FETCH_STATUS = 0
    Begin
    if @curr_type = 0
    set @curr_type = @inspec_type;
    Else
    --last inspection type is @curr_type
    Begin
    if @curr_type <> @inspec_type
    close insp_type
    deallocate insp_type
    return(1);
    End

    Fetch Next From insp_type Into @inspec_type

    End

    close insp_type
    deallocate insp_type

    return(0);
    GO

    The ELSE statment executed eventhough both variables 's values is 3 . Why?

    Thank you much!!

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Indeting is SO overlooked...

    You have logic problems...I guess they don't call them scop termintors here...

    BUT...for now, to make it easier to read, use a BEGIN and END for every logic block...AND make sure to match them up...

    BUT....This is WAY to over done for what you're trying to do....which is?

    Here, look at this

    Code:
    CREATE PROC spCheck_inspection_type
    	  @inspec_type smallint OUT, @curr_type   smallint OuT
    AS
    DECLARE insp_type CURSOR
    FOR 
    SELECT inspection_type FROM tblTmp_inspec WHERE partial <> 0 or complete <> 0;
    SET @curr_type = 0;
    
    Open insp_type
    FETCH NEXT FROM insp_type INTO @inspec_type
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	IF @curr_type = 0
    		SET @curr_type = @inspec_type;
    	  ELSE
    	    BEGIN
    		IF @curr_type <> @inspec_type
    		  CLOSE insp_type
    	      DEALLOCATE insp_type
    	      RETURN 1
    	    END
    	FETCH NEXT FROM insp_type INTO @inspec_type
      END
    
    CLOSE insp_type
    DEALLOCATE insp_type
    
    RETURN 0
    GO
    Last edited by Brett Kaiser; 09-28-04 at 13:48.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's too many colors,Brett, andno comment on what they mean!

    All the guy is missing is BEGIN...END on the test for @curr_type <> @inspec_type:
    Code:
       if @curr_type <> @inspec_type begin
    	  close insp_type
    	  deallocate insp_type
    	  return(1);
       end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    fine...fine...fine....

    BUT!

    Would you do this?

    I mean, what does it even mean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, there are problems with the code. Most obvious one is that the cursor declaration does not take into account the order of records as they get returned. This would yield unpredictable results when the app is running on a single CPU machine while being tested, compared to a prod environment when it gets deployed onto a SMP system.

    To avoid usage of cursor, while fixing the ORDERing issue, would be to do the following:

    declare @curr_type int, @record_id int
    select @curr_type = inspection_type, @record_id = <record_id> from (
    select top 1 <record_id>, inspection_type from tblTmp_inspec
    WHERE partial <> 0 or complete <> 0
    order by <record_id>) x

    if exists (select 1 from tblTmp_inspec where <record_id> > @record_id
    and partial <> 0 or complete <> 0 and inspection_type <> @curr_type)
    return 1
    else
    return 0
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, that was the most beautiful piece of SQL I've ever seen.

    I am reminded of the Old Testament story of Joseph and his "Code of many colors". It must have looked something like that.

    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Brett, that was the most beautiful piece of SQL I've ever seen.

    And you know that's saying something...because he's, well, blind



    Thanks Dude....

    Seriously, alicejwz, Lettuce know what you're doing, and we can hook you up.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •