Hi guys. I'm pretty new to sql and I'm having some trouble with a store procedure. I tried to read about it by searching goggle but all the references i got to the topic contained to complex store procedures for me and my current level :P.
Here is my simple store procedure:

CREATE PROCEDURE INCERT_HOUSEADDRESS
	@postCode varchar(10),
	@areaName varchar(20), 
	@city varchar(20), 
	@country varchar(20), 
	@houseAddressID int OUTPUT 
AS
BEGIN
	INSERT INTO HouseAddress VALUES(@postalCode, @areaName, @city, @country)
	SELECT @houseAddressID FROM HouseAddress WHERE HouseAddress.postCode = @postCode
END

I need to get the houseAddressID so I can return it through ADO to my C# application so that I can insert Households(is a table that holds houseAddresID as a foreign key) connected to that address.

Recommended Answers

All 4 Replies

comment line 11 and write the line i have given below

--SELECT @houseAddressID FROM HouseAddress WHERE HouseAddress.postCode = @postCode
set @houseAddressID = SCOPE_IDENTITY()

Thank you. It fixed one of the problems but it is still complaining about the postCode that I must declare a scalar as well =/. Probably because I have declared it as UNIQUE in the table. Should I do the same for it or it is bad idea?

I am not sure about scalar error. Database design has nothing to do with store procedure error. But If you have kept postcode as unique and you trying to insert it twice then it will not allow you.

Ou I found my mistake. SYNTAX ERORR. I missed typed @postPode to @postalCode ... (stupid!). Thank you any way :).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.