January 15, 2006 at 9:57 pm
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 !!!**
January 15, 2006 at 10:04 pm
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
January 15, 2006 at 10:09 pm
1)
TEMPLATE table
CARD_ID
USER_ID
STUDENT Table
USER_ID
FIRST_NAME
LAST_NAME
PHONE
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
January 16, 2006 at 9:59 am
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