asking for user input & displaying result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

asking for user input & displaying result

Author

Topic

jonekim
Starting Member

35 Posts

Posted - 2012-01-30 : 11:16:37

I am using MS SQL SERVER2005 & c#

I have designed a database having tables:

STUDENT table
(STUDENT_ID(PK),STUDENT_NAME)

STUDENT_ID = 1 STUDENT_NAME = SN1
STUDENT_ID = 2 STUDENT_NAME = SN2
STUDENT_ID = 3 STUDENT_NAME = SN3

COURSE table
(COURSE_CODE(PK), COURSE_NAME)

COURSE_CODE = CC1 COURSE_NAME = CN1
COURSE_CODE = CC2 COURSE_NAME = CN2
COURSE_CODE = CC3 COURSE_NAME = CN3

##STUDENT table & COURSE table exhibits many-to-many relation

CLASS table ##junction table
(CLASS_GRADE, STUDENT_ID, COURSE_CODE)

CLASS_GRADE = 1 STUDENT_ID = 1 COURSE_CODE = CC1
CLASS_GRADE = 2 STUDENT_ID = 2 COURSE_CODE = CC2
CLASS_GRADE = 3 STUDENT_ID = 3 COURSE_CODE = CC3

Now I want a table showing the name of the students and the course taught in an individual class.
User is asked to enter the class_grade and according to the user input, list of the students enrolled for that class and the course taught in that class should be displayed.

How to take user input in sql query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:30:55

you need to make a stored procedure for that with a parameter for passing grade value. then use parameter to filter and display the results
something like


CREATE PROC GetClassData
@Class_Grade int
AS
SELECT
FROM CLASS c
INNER JOIN STUDENT s
ON s.STUDENT_ID = c.STUDENT_ID
INNER JOIN COURSE cr
ON cr.COURSE_CODE = s.COURSE_CODE 
WHERE c.CLASS_GRADE = @Class_Grade 
GO


create the procedure and then use it like

EXEC GetClassData @Class_Grade = 1

EXEC GetClassData @Class_Grade = 2

EXEC GetClassData @Class_Grade = 3 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources