Stored Procedure_Output Parameters

[Studious01]

--Try this
Create Procedure sp_Insert_for_tables

@table2column2 Smallint Output,
@table2column3 Varchar(20),Output,
@table1column1 Smallint,
@table1column2 Smallint,
as
Begin

If Exists ( Select column1 From table1 Where column1=@column1)
begin
Raiserror(500020,16,1,@column1)
end
else
begin
Insert into table1
Values(@table1column1,@table1column2)

Select table2column2,table2column3

Declare @1stinsertfortb2 smallint
@2stinsertfortb2 varchar(20)

Set @table2colmn2=@insertfortb2
Set @table2colum3=@insertfortb2

Insert into table2
Values(@insertfortb2,@insertfortb2)

end

End

[MAK]

Oops....

your posting

Create Procedure sp_Insert_for_tables

@table2column2 Smallint Output,
@table2column3 Varchar(20),Output,
@table1column1 Smallint,
@table1column2 Smallint,
as
Begin

If Exists ( Select column1 From table1 Where column1=@column1)
Raiserror(500020,16,1,@column1)
else
Insert into table1
Values(@table1column1,@table1column2)

Select table2column2,table2column3

Declare @1stinsertfortb2 smallint
@2stinsertfortb2 varchar(20)

Set @table2colmn2=@insertfortb2
Set @table2colum3=@insertfortb2

Insert into table2
Values(@insertfortb2,@insertfortb2)



--Try my posting

[Studious01]

Hey isn't this similiar to what i posted?

The gist of what i am trying to do is,to take some of the passed in variables such as,

@Customer_Name
@Customer_Address
@order_no
@order_name

And insert the values for two of the varibles like:

@Customer_Name
@Customer_Address

into table one

then insert the values:
@order_no
@order_name

into table two.

From my understanding this can be done quite easily using a Stored Procedure,and output parameter. But i am experiencing difficulty doing this.

Any suggestions would definitely be appreciated!

Thanks.

[MAK]

I dont know why you need output parameter in your proc. See the following proc

Create Procedure sp_Insert_for_tables
@Customer_Name varchar(100),
@Customer_Address varchar(500),
@order_no int,
@order_name varchar(100)
as
If Exists ( Select customer_name From Customertable Where Customer_Name=@Customer_Name)
begin Raiserror(500020,16,1,"Customer Already Exist") end
else begin
Insert into Customertable Values(@Customer_Name ,@Customer_Address )
Insert into OrderTable Values(@order_no,@order_name)
end

[Studious01]

Thanks,this actually one of the fist ways i tried this, but for some reason the values aren't being passed into the columns of the second table.

Also, I have set the identity property for the 1st column in the 2nd table to true, so that an unique number is automatically generated.

And I have noticed that the only way that the values will get passed to the 2nd table is if the "If Exists" statement is true, then table 1 is bypassed.

Anyway I will not give up until this has been solved!

[MAK]

The values are not passed to the second table because you do not have a begin and end for if.

if .....
raiseerror
else
insert 1
insert 2

this will insert only 'insert 1'


if .....
begin
raiseerror
end
else
begin
insert 1
insert 2
end

this is do both 'insert 1 and insert 2'

[Studious01]

ok, I also set the identity property for the 1st column in table 2 to 'on', but when i exec the stored procedure without the parameter for this column provided like:

exec sp_accept_inserts @colomn 2=value,@colomn3=value,....

I get this message:

Procedure 'sp_accept_inserts' expects parameter '@Column_ID', which was not supplied.

Any suggests...



Thanks Again!

[MAK]

Please post an example like

create table table1
insert into table1......(identity...)
insert into table1......
insert into table1......


create table table2
insert into table2......
insert into table2......
insert into table2......

Then explain what you are really trying to do and what is your expected results.

[Studious01]

Thanks for all of your help!!!