May 17, 2007 at 8:45 am
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
May 17, 2007 at 10:02 am
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?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 17, 2007 at 10:13 am
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
May 17, 2007 at 10:57 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply