Stored Procedure
-
Need some help in Store Procedure
I have Table with the following columns.
Lesson_1,Code_1,Lesson_2,Code_2,Lesson_3,Code_3,Lesson_4,Code_4,Lesson_5,Code_5,Lesson_6,Code_6
I need to do a SQL Query, on all 6 columns = Code_1 to Code_6 to find a matching value.
So suppose Code_2 has a matching value. I need to output Lesson_2 in my stored procedure.
I hope you understand what im trying to say here.
Thanks in advance.
-
"So suppose Code_2 has a matching value"
-
lets say
Code_2 = '404' <-- This is the matching value.
-
If those are the only 3 columns you need to query, you could always do an "if statement"...
IF (SELECT COUNT(*) FROM myTable WHERE code_1 = '404') >= 1 BEGIN SELECT Lesson_1 FROM myTable WHERE..... END ELSE IF (SELECT COUNT(*) FROM myTable WHERE code_2 = '404') >= 1 BEGIN SELECT Lesson_2 FROM myTable WHERE..... END ELSE IF (SELECT COUNT(*) FROM myTable WHERE code_3 = '404') >= 1 BEGIN SELECT Lesson_3 FROM myTable WHERE..... END
**ASCII stupid question, get a stupid ANSI !!!** -
Any specific hint why the table hasn't been normalized?
-
first guess I'd venture is "legacy database"....
**ASCII stupid question, get a stupid ANSI !!!** -
SELECT
CASE
WHEN @param = Code_1 THEN Lesson_1
WHEN @param = Code_2 THEN Lesson_2
WHEN @param = Code_3 THEN Lesson_3
WHEN @param = Code_4 THEN Lesson_4
END Lesson
FROM SomeTable
WHERE SomeField = SomeOtherCriteria
-
...better alternative!!!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the above mentioned code.
I have DECLARE @Lesson as a variable in my SP. How am I going to store the result I got from Lesson Column into this @Lesson variable.
SELECT
CASE
WHEN @param = Code_1 THEN Lesson_1
WHEN @param = Code_2 THEN Lesson_2
WHEN @param = Code_3 THEN Lesson_3
WHEN @param = Code_4 THEN Lesson_4
END Lesson <>
FROM SomeTable
WHERE SomeField = SomeOtherCriteria
-
raj - this is all you have to do...
SELECT @Lesson = (store the result into a variable) CASE WHEN @param = Code_1 THEN Lesson_1 WHEN @param = Code_2 THEN Lesson_2 WHEN @param = Code_3 THEN Lesson_3 WHEN @param = Code_4 THEN Lesson_4 END FROM SomeTable WHERE SomeField = SomeOtherCriteria
**ASCII stupid question, get a stupid ANSI !!!** -
Another question I have here again. Sorry guys...
MY Class table is as follows :
CLASS_ID int
ROOM varchar
BLOCK varchar
I have a S.P here...
CREATE PROCEDURE GET_CLASSID (@BLOCK varchar, @ROOM varchar, @CLASS_ID int OUTPUT)
AS
SELECT
@CLASS_ID = CLASS_ID FROM
CLASS
WHERE BLOCK = @BLOCK AND ROOM = @ROOM
GO
The problem i am facing it that this procedure works,when i exec my S.P my @Class_ID output is displaying a NULL? How is this possible? My table has a few records in it.
Please help
-
Is this using the query analyzer? (or ODBC, ADO, OLEDB, .NET ...)
Have you specified the output parameter there too?
*side note
Please size your variables (default = 30 characters)
(@BLOCK varchar, @ROOM varchar, @CLASS_ID int OUTPUT)
-> (@BLOCK varchar(5), @ROOM varchar(5), @CLASS_ID int OUTPUT
*you can put SET NOCOUNT ON to reduce an extra roundtrip between client and server
-
Working on my stored procedure, but im confusing, since its returning me with NULL Values. My Stored Procedure is suppose to Return me 2 Output that is Name and Class. I have 4 input parameters. When I execute my SP i am getting NULL from both outputs. All my Tables have data in it, so i am sure there is no null values inside any columns. I have been stuck with this overnight and trying to rectify this error. Anyone care to help. Thanks in advance.
CREATE PROCEDURE STUDENT (@CARD varchar(27), @CODE int, @YEAR int, @SEMESTER char(10), @CLASS varchar(50) output, @NAME varchar(20))
AS
DECLARE @CLASS_ROOM as int, @USER_ID as varchar(9)
SELECT @USER_ID = USER_ID FROM TEMPLATE WHERE CARD_ID = @CARD
SELECT @NAME = FIRST_NAME FROM STUDENT WHERE USER_ID = @USER_ID
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 @CLASS = CLASS_ROOM FROM CLASS WHERE CLASS_ID = @Lesson
GO
-
Select from subquery:
SELECT Lesson_1,Code_1
FROM ...
UNION
SELECT Lesson_2,Code_2
FROM ...
UNION
SELECT Lesson_3,Code_3
FROM ...
UNION
SELECT Lesson_4,Code_4
FROM ...
UNION
SELECT Lesson_5,Code_5
UNION
FROM ...
SELECT Lesson_6,Code_6
FROM ...
_____________
Code for TallyGenerator -
hmmm....But my problem Im getting is that my OUTPUT is all NULL. ?? any clues??
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply