Getting information dependant upon primary & foreign key

Hi All,

It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk),Dept(fk)

2.tblDept:Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,

Kunal

CREATE PROCEDURE dbo.ShowMe @.yourInputValue INT
AS
SELECT tblDept.Department
FROM tblDept JOIN tblRisk
ON tblDept.Ref = tblRisk.Dept
WHEREtblDept.Ref = @.yourInputValue
RETURN 0
GO

You stored procedure is ok

You can use SqlCommand to call stored procedures in your ASP .NET application, just set the CommandType property of the SqlCommand to CommandType.StoredProcedure and pass proper parameter(s) to the stored procedure.