Stored Procedure

  • 1) Pl. post some sample rows from your tables.

    2) If you run with actual values, do you get the right results ?!

    3) what is the application used to call the stored procedure ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • 2) I tried to execute each SQL Query with Exact values and it works. But when i execute my S.P with the require parameters, it returns NULL/

    3) I am using VB.net to call the S.P

  • 1)

    TEMPLATE table

    CARD_ID

    USER_ID

    STUDENT Table

    USER_ID

    FIRST_NAME

    LAST_NAME

    PHONE

    EMAIL

    Lesson Table

    USER_ID

    YEAR

    SEMESTER

    CODE_1

    LESSON_1

    CODE_2

    LESSON_2

    CODE_3

    LESSON_3

    CODE_4

    LESSON_4

    CODE_5

    LESSON_5

    Class Table

    CLASS_ID

    CLASS_ROOM

  • since you expect name as output parameter

    CREATE PROCEDURE STUDENT (@CARD varchar(27), @CODE int, @YEAR int, @SEMESTER char(10), @CLASS varchar(50) output, @NAME varchar(20) OUTPUT) (output was missing)

    Sure semester is char(10) and not varchar(10)?

    If you pass 'hello' it will be reformed to '      hello'

     

    CREATE PROCEDURE STUDENT (@CARD varchar(27), @CODE int, @YEAR int, @SEMESTER char(10), @CLASS varchar(50) output, @NAME varchar(20) OUTPUT)

    AS

    SELECT @CARD  AS CARD_INPUT,@CODE  AS CODE_INPUT,@YEAR AS YEAR_INPUT,@SEMESTER AS SEMESTER_INPUT /*comment out for release*/

    DECLARE @CLASS_ROOM as int, @USER_ID as varchar(9)

    SELECT @USER_ID = USER_ID FROM TEMPLATE WHERE CARD_ID = @CARD

    SELECT @USER_ID  AS USERID /*comment out for release*/

    SELECT @NAME = FIRST_NAME FROM STUDENT WHERE USER_ID = @USER_ID

    SELECT @NAME AS STUDENTNAME /*comment out for release*/

    /*only 1 lesson expected ! */

    /*Code is numeric ! see int type*/

    SELECT @Lesson =

    CASE

    WHEN CODE_1 = @CODE THEN Lesson_1

    WHEN CODE_2 = @CODE THEN Lesson_2

    WHEN CODE_3 = @CODE THEN Lesson_3

    WHEN CODE_4 = @CODE THEN Lesson_4

    WHEN CODE_5 = @CODE THEN Lesson_5

    END

    FROM LESSON WHERE USER_ID = @USER_ID AND YEAR = @YEAR AND SEMESTER = @SEMESTER

    SELECT @NAME AS STUDENTNAME,@Lesson AS LESSON,@USER_ID  AS USERID,@YEAR AS myYear, @SEMESTER AS SEMESTER  /*comment out for release*/

    SELECT @CLASS = CLASS_ROOM FROM CLASS WHERE CLASS_ID = @Lesson

    SELECT @CLASS as MyClass  /*comment out for release*/

    RETURN /*END OF STORED PROCEDURE*/

    What is the result when you execute this stored procedure in query analyzer?

    DECLARE @tmpCLASS varchar(50)

    DECLARE @tmpName varchar(20)

    SET @tmpCLASS ='nothing'

    SET @tmpName ='nothing'

    EXECUTE STUDENT @CARD ='card',@CODE =1,@YEAR =2006

    ,@SEMESTER ='first',@CLASS= @tmpCLASS OUTPUT, @NAME =@tmpName OUTPUT

    SELECT @tmpCLASS AS CLASS_OUTPUT,@tmpName AS NAME_OUTPUT

    Can the owner of the database be persuaded to normalize the database (lesson table) so your queries will be a lot easier to construct and maintain?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply