T-SQL SP's

greentoymonkey

Lads,
Im trying build a SP that will take 6 params, put four of which into one table, and the other table then is inherited from the first table so i need to take the identity (primary key) from the first table and intpu it into my second table.

I was hoping to do this using the input/output functions but im not getting anywhere with it! well no, i am getiing somewhere ive just got one small error with my code.

create proc Register_individual @address varchar(20), @town varchar(20), @county varchar(20), @phone varchar(20), @fname varchar(20), @lname varchar(20)

as

insert into customer values(@address, @town, @county, @phone)

declare @cust_ID int 
select @cust_ID = (select output inserted.Customer_ID) 
insert into individual values(@cust_ID, @fname, @lname)

Im getting the following error:

" Msg 102, Level 15, State 1, Procedure Register_individual, Line 8
Incorrect syntax near '.'. "

Can someone please hint me in the right direction?

Evil Phil

The simplest solution is often the best:

create proc Register_individual @address varchar(20), @town varchar(20), @county varchar(20), @phone varchar(20), @fname varchar(20), @lname varchar(20)

as

insert into customer values(@address, @town, @county, @phone)

declare @cust_ID int 
select @cust_ID = @@Identity
insert into individual values(@cust_ID, @fname, @lname)

But you've decide to use the OUTPUT function so this should work, note however that I'm new to SqlServer 2005 myself

create proc Register_individual @address varchar(20), @town varchar(20), @county varchar(20), @phone varchar(20), @fname varchar(20), @lname varchar(20)

as

DECLARE @TempTable TABLE (CustomerID INT)

insert into customer 
OUTPUT INSERTED.CustomerID into @TempTable
values (@address, @town, @county, @phone) 

declare @cust_ID int 
select @cust_ID = CustomerID from @TempTable
insert into individual values(@cust_ID, @fname, @lname)

ob

I think the @@IDENTITY function would be usefull here, and wrap both inserts in a transaction.

Beano

@IDENTITY can possibly contain an identity value that was inserted on an entirely different table.

kayos

@Identity solution will work it can lead to some problems depending on your setup.

@IDENTITY is session limited.
@IDENTITY.

SP1 - inserts into a table with an identity column
SP1 - Calls SP2
SP2 - inserts into a table with an identity column
SP2 - returns control to SP1
@Identity and gets the last IDENTITY value within the session i.e. the IDENTITY value created in SP2's insert.

SCOPE_IDENTITY() is Scope limited i.e. limited to only the current t-sql sproc/udf/trigger etc and ignores all other scopes within the session.

SP1 - inserts into a table with an identity column
SP1 - Calls SP2
SP2 - inserts into a table with an identity column
SP2 - returns control to SP1
SP1 - Calls SCOPE_IDENTITY() and gets the last IDENTITY value within the scope i.e. the IDENTITY value created in SP1's insert.

So while you might not be effected by the above right now you would still be better off using SCOPE_IDENTITY().

Your code would be as simple as

CREATE PROCEDURE Register_individual
	@address VARCHAR(20), 
	@town VARCHAR(20), 
	@county VARCHAR(20), 
	@phone VARCHAR(20), 
	@fname VARCHAR(20), 
	@lname VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON

	INSERT 
		customer 
		(
		Address,
		Town,
		Country,
		Phone
		)
	VALUES
		(
		@address, 
		@town, 
		@county, 
		@phone
		)

	DECLARE @cust_ID INT 
	SELECT 
		@cust_ID = SCOPE_IDENTITY()

	INSERT 
		individual 
		(
		CustomerID,
		FName,
		LName
		)
	VALUES
		(
		@cust_ID, 
		@fname, 
		@lname
		)
END

To be honest I would actually do this slightly different with the use of 3 procedures, 2 that would accept and insert the values only for their tables e.g. Customer_Save and Individual_Save. Then one Procedure above these two to take all the params at once and call off to the two save procedures.

@IDENTITY within my current session.

greentoymonkey

thanks for the help lads,

i got that working grand but see the database is just the back end of my asp written website!

Im trying to now call this SP from the database to input a form into the database. Ive been at this all day! heres the code ive been tryin out:

<%

Dim Sp__address
Sp__address = ""
if(Request("address") <> "") then Sp__address = Request("address")

Dim Sp__town
Sp__town = ""
if(Request("town") <> "") then Sp__town = Request("town")

Dim Sp__county
Sp__county = ""
if(Request("county") <> "") then Sp__county = Request("county")

Dim Sp__phone
Sp__phone = ""
if(Request("phone") <> "") then Sp__phone = Request("phone")

Dim Sp__fname
Sp__fname = ""
if(Request("fname") <> "") then Sp__fname = Request("fname")

Dim Sp__lname
Sp__lname = ""
if(Request("lname") <> "") then Sp__lname = Request("lname")

%>
<%

set Sp = Server.CreateObject("ADODB.Command")
Sp.ActiveConnection = MM_Driwash_STRING
Sp.CommandText = "dbo.Register_individual"
Sp.CommandType = 4
Sp.CommandTimeout = 0
Sp.Prepared = true
Sp.Parameters.Append Sp.CreateParameter("@RETURN_VALUE", 3, 4)
Sp.Parameters.Append Sp.CreateParameter("@address", 200, 1,20,Sp__address)
Sp.Parameters.Append Sp.CreateParameter("@town", 200, 1,20,Sp__town)
Sp.Parameters.Append Sp.CreateParameter("@county", 200, 1,20,Sp__county)
Sp.Parameters.Append Sp.CreateParameter("@phone", 200, 1,20,Sp__phone)
Sp.Parameters.Append Sp.CreateParameter("@fname", 200, 1,20,Sp__fname)
Sp.Parameters.Append Sp.CreateParameter("@lname", 200, 1,20,Sp__lname)
set rs_proc = Sp.Execute
rs_proc_numRows = 0

%>

as you might notice ive been doing it through dreamweaver but im confident enough to work with code too so if theres a code or dreamweaver solution i dont care once i got it fixed! haha

Im not even getting an error its just putting 3 or 4 blank entries each time with the primary keys. so i duno what the problem is?! any clues?

Beano

Have you checked whether there is anything in the variables you pass to the stored proc? Have you tried running the stored proc in query analyser?

greentoymonkey

ye i did and it worked grand! Duno what the story is! any clues?

Beano

if the stored proc itself works in query analyser then the only thing i can suggest is that you walk through the parameters collection and check what it contains at the point that you execute the query.

greentoymonkey

ah rite thanks beano

Beano

greentoymonkey wrote: »

ah rite thanks beano

Did you get this working?

greentoymonkey

ehhhhh!! ye kind of! i hadnt named the textfields the same as the name of the runtime values. But now wen this data goes into my database, its showing the data i put in but with about 6 blanks before the actual data aswell? tryin to sort it out tho so if you have any clues let me know but im guessing its just something small and stupid!

Thanks for the help.

Beano

do you not think think that the blank rows were put in when the code was incorrect? Delete all the rows from the table and try it again.

greentoymonkey

DDDDuuuhhhh!!! lol ye corse i did that! haven worked on it in a few days so might cop on to it when i go back to it tomorrow

cheers gain for the help