Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Unanswered: Stored Procedure Syntax - Value not returned

    I have the following stored procedure. The good thing is I finally got the syntax to the point where it doesn't blow up, but I was under the impression the "print" statement would print my result. In this case all I get is the statement ' 1 Row Affected ' which by the way is not the answer I was expecting.

    Here's the code:
    '
    CREATE PROCEDURE createevents
    AS

    declare @myvariable varchar(700)

    declare @myvar varchar(700)

    begin
    Select @myvariable = d.id
    from table1 d, table2 dc where d.class = dc.class

    If @myvariable > 0
    begin
    Select @myvar =dp. class
    from table3 dp, table2 dc
    where dp.class = dc.class

    If @myvariable >= 1
    begin
    print @myvariable + (',') + @myvar
    end
    end

    else
    begin
    print @myvariable + ('is empty')
    end
    end

    '
    Thanks in advance for any help.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't make a lot of sense...try lining the BEGIN and ENDs together...maybe taht'll help

    Code:
    CREATE PROCEDURE createevents
    AS
    
    BEGIN
    	DECLARE @myvariable varchar(700), @myvar varchar(700)
    	    
    	    SELECT @myvariable = d.id 
    	      FROM table1 d
    	INNER JOIN table2 dc 
    		ON d.class = dc.class
    
    	IF @myvariable > 0
    	  BEGIN
    		SELECT @myvar = dp. class
    		  FROM table3 dp, table2 dc 
    		 WHERE dp.class = dc.class
    
    		IF @myvariable >= 1
    		  BEGIN
    			PRINT @myvariable + (',') + @myvar
    		  END
    	  END
    
    	  ELSE
    
    	  BEGIN
    		PRINT @myvariable + ('is empty')
    	  END
    END
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    37
    Brett,
    Thanks for the input. I lined up the begin/end but it still
    doesn't print the value of my variables.

    What part of it doesn't make sense?

    Tammy


    Originally posted by Brett Kaiser
    Doesn't make a lot of sense...try lining the BEGIN and ENDs together...maybe taht'll help

    Code:
    CREATE PROCEDURE createevents
    AS
    
    BEGIN
    	DECLARE @myvariable varchar(700), @myvar varchar(700)
    	    
    	    SELECT @myvariable = d.id 
    	      FROM table1 d
    	INNER JOIN table2 dc 
    		ON d.class = dc.class
    
    	IF @myvariable > 0
    	  BEGIN
    		SELECT @myvar = dp. class
    		  FROM table3 dp, table2 dc 
    		 WHERE dp.class = dc.class
    
    		IF @myvariable >= 1
    		  BEGIN
    			PRINT @myvariable + (',') + @myvar
    		  END
    	  END
    
    	  ELSE
    
    	  BEGIN
    		PRINT @myvariable + ('is empty')
    	  END
    END

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not just do....

    Code:
    CREATE PROCEDURE createevents
    AS
    
    BEGIN
    	DECLARE @myvariable varchar(700), @myvar varchar(700)
    	    
    	    SELECT ISNULL(d.id+dp.class,'Empty') 
    	      FROM table1 d
    	INNER JOIN table2 dc 
    		ON d.class = dc.class
    	INNER JOIN table3 dp
    		ON dc.class = dp.class
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And what's with the > 0 and > 1 part?

    What are their datatypes? Seems like varchar to me....
    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
    Oct 2003
    Posts
    37
    Hi Brett,
    Let me see if I can shed some light on this one.
    I have a trigger that will fire when there is an insert to table1.
    I have to grab the id and class and then look into tables 2 & 3 for the corresponding datatype.
    If I have a match on the class I then look into another table (table4) to grab a second id (not the same as the id in table1) value. When I have that value I update the field in table 1 that was initially null when the record gets inserted. (There are two different id fields in table1)

    The first variable is holding that first id so I know which records to update when I get the second id.

    As for the if statement, I'm trying to determine if the variable has a value in it. Obviously I'm not using it in the right context.
    Thanks again.
    Tammy

    Originally posted by Brett Kaiser
    And what's with the > 0 and > 1 part?

    What are their datatypes? Seems like varchar to me....

Posting Permissions

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