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"

    What do you mean by this, raj?  Code_2 has a value matching what? Another column, or a parameter value being passed in?
     
    Thanks,
     
    Martin
  • 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