stored procedure

  • Hi,

       i need help on this

    I have two tables Table A and Table B And Table c

    Table A has the following columns

    Building_id

    Building_name

    School_id

    Building_use

     

    Table B has the following columns

     

    School_id

    school_name

    city

    state

    Table C has the following columns

    Building_id

    Assessed_Value

    Assessed_Date

     

    what i want is TO WRITE A STORED PROCEDURE so i can  get the linked or related information from table A and Table B into table c

  • I am not sure what you want to get into table C. A simple

    Insert Into C

    (

    building_id

    )

    Select distinct

    A.building_id

    from

    A Left Join

    C On

    A.building_id = C.building_id

    WHere

    C.building_id Is Null

    Will insert new building_id's into table C.

    Is this what you were looking for? Or do you want an SP that accepts a building_id, assessed_date, and assessed_value as parameters and created a record in table C?

  • yes i need  a stored procedure which accepts parameters building_id, assessed_date, and assessed_value as parameters and create a record in table C.

    can you tell me how i can do that.

     

    thanks

  • Okay. This one assumes you have a foreign key relationship between table A and table C enforcing that the building actually exists.

    Create Procedure sp_name

    (

    @building_id Int,

    @assessed_date datetime or smalldatetime,

    @assessed_value float, decimal(18, 2)

    )

    As

    Insert Into C

    (

    building_id,

    assessed_date,

    assessed_value

    )

    Values

    (

    @building_id,

    @assessed_date,

    @assessed_value

    )

    Return

    This also assumes you want to keep a history of assessments so you are creating a new record for each assessment and not updating a record.

    If you do not have a foreign key relationship defined then you would just want to verify the building_Id exists before doing the insert with:

    If exists (Select building_id From A where building_id = @building_id)

    Begin -- do the insert

    /* Insert code above here */

    End

    Else

    Begin

    /* Either a RaisError or a specific return value which the calling application can interpret to present the user with a good message */

    End

Viewing 4 posts - 1 through 4 (of 4 total)

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