how to get values from another table before runing the insert statment in sql server 2005

ziorrinfotech
ziorrinfotech used Ask the Experts™
on
I have written an Store proc which insert a new records into Job table.
Now this Job table has a foreign key column which is linked with customer table
and the column name is customerOID in JOB  table and OID in customer table

now i am running a sub query to get the customer oid from customer table so that this id gets inserted into the job table
but this gives me compile error that subqueries are not allowed .....

could any one help me out here what i am doing wrong here
ALTER PROCEDURE [sp_InsertJob]
	@JobNum int, @JobName nvarchar(100), @Nixt datetime, @Crew int, @Amount float, @Hr_Rate float, @Mhrs float,
	@MapLoc nvarchar(10),@Lsq int, @Cuts_ID int, @Sch_Type nvarchar(1), @Hold bit, @Last datetime, @Jb_Mstype nvarchar(1),
	@Freq int, @Jan bit, @Feb bit, @Mar bit, @Apr bit, @May bit, @Jun bit, @Jul bit,@Aug bit,@Sep bit,@Nov bit, @Dec bit,
	@Sun bit, @Mon bit, @Tue bit, @Wed bit, @Thu bit, @Fri bit, @Sat bit, @Wom1 bit,@Wom2 bit, @Wom3 bit, @Wom4 bit,
	@Woml bit, @RtNotes ntext, @JbNotes ntext, @InvNotes ntext, @Bil_Type nvarchar(1), @Route int, @Downcount int,
	@JobID nvarchar(20), @Jb_pjb_ID nvarchar(15), @Jb_program bit, @Jb_jbp_ID nvarchar(15), @Jb_Pdocode nvarchar(3),
	@Jb_Cat nvarchar(20), @Jb_s_Cat nvarchar(20), @Jb_Type nvarchar(4), @Jb_Diff float, @TaxType nvarchar(1), 
	@Avtime float, @Jb_addon bit, @Jb_ArelID nvarchar(15), @Jb_adon_ID nvarchar(15), @Jb_pseq int,@ClipSerialNumberID int,
	@Oct bit, @Jb_cu_ID nvarchar(20), @Jb_Def_Qty numeric, @Jb_QtyUnit numeric
 
AS
BEGIN
	INSERT INTO [Job] ([JobNum],[JobName],[Nixt],[Crew],[Amount],[Hr_Rate],[Mhrs],[MapLoc],[Lsq],[Cuts_ID],[Sch_Type],
	[Hold],[Last],[Jb_Mstype],[Freq],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Nov],[Dec],[Sun],[Mon],[Tue],
	[Wed],[Thu],[Fri],[Sat],[Wom1],[Wom2],[Wom3],[Wom4],[Woml],[RtNotes],[JbNotes],[InvNotes],[Bil_Type],[Route],
	[Downcount],[JobID],[Jb_pjb_ID] ,[Jb_program],[Jb_jbp_ID],[Jb_Pdocode],[Jb_Cat],[Jb_s_Cat],[Jb_Type],[Jb_Diff],
	[TaxType],[Avtime],[Jb_addon],[Jb_ArelID],[Jb_adon_ID],[Jb_pseq],[ClipSerialNumberID], [Oct], [Jb_cu_ID],
	[Jb_Def_Qty] , [Jb_QtyUnit], [CustomerOID])
    VALUES (@JobNum , @JobName , @Nixt , @Crew , @Amount , @Hr_Rate , @Mhrs , @MapLoc ,@Lsq , @Cuts_ID , @Sch_Type ,
	@Hold, @Last , @Jb_Mstype , @Freq , @Jan , @Feb , @Mar , @Apr , @May , @Jun , @Jul ,@Aug ,@Sep ,@Nov , @Dec ,
	@Sun , @Mon , @Tue , @Wed , @Thu , @Fri , @Sat , @Wom1 ,@Wom2 , @Wom3 , @Wom4 , @Woml , @RtNotes, @JbNotes ,
	@InvNotes , @Bil_Type , @Route , @Downcount , @JobID , @Jb_pjb_ID , @Jb_program , @Jb_jbp_ID , @Jb_Pdocode,@Jb_Cat,
	@Jb_s_Cat , @Jb_Type , @Jb_Diff , @TaxType ,@Avtime , @Jb_addon , @Jb_ArelID , @Jb_adon_ID , @Jb_pseq,
	@ClipSerialNumberID, @Oct, @Jb_cu_ID, @Jb_Def_Qty , @Jb_QtyUnit,
	(select OID from Customer where ClipSerialNumberID = @ClipSerialNumberID and Cu_ID = @Jb_cu_ID ) 
 
	)
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
why don't you use this?
ALTER PROCEDURE [sp_InsertJob]
        @JobNum int, @JobName nvarchar(100), @Nixt datetime, @Crew int, @Amount float, @Hr_Rate float, @Mhrs float,
        @MapLoc nvarchar(10),@Lsq int, @Cuts_ID int, @Sch_Type nvarchar(1), @Hold bit, @Last datetime, @Jb_Mstype nvarchar(1),
        @Freq int, @Jan bit, @Feb bit, @Mar bit, @Apr bit, @May bit, @Jun bit, @Jul bit,@Aug bit,@Sep bit,@Nov bit, @Dec bit,
        @Sun bit, @Mon bit, @Tue bit, @Wed bit, @Thu bit, @Fri bit, @Sat bit, @Wom1 bit,@Wom2 bit, @Wom3 bit, @Wom4 bit,
        @Woml bit, @RtNotes ntext, @JbNotes ntext, @InvNotes ntext, @Bil_Type nvarchar(1), @Route int, @Downcount int,
        @JobID nvarchar(20), @Jb_pjb_ID nvarchar(15), @Jb_program bit, @Jb_jbp_ID nvarchar(15), @Jb_Pdocode nvarchar(3),
        @Jb_Cat nvarchar(20), @Jb_s_Cat nvarchar(20), @Jb_Type nvarchar(4), @Jb_Diff float, @TaxType nvarchar(1), 
        @Avtime float, @Jb_addon bit, @Jb_ArelID nvarchar(15), @Jb_adon_ID nvarchar(15), @Jb_pseq int,@ClipSerialNumberID int,
        @Oct bit, @Jb_cu_ID nvarchar(20), @Jb_Def_Qty numeric, @Jb_QtyUnit numeric
 
AS
BEGIN
		declare @customerOID int
		select @customerOID=OID from Customer where ClipSerialNumberID = @ClipSerialNumberID and Cu_ID = @Jb_cu_ID 
        
        INSERT INTO [Job] ([JobNum],[JobName],[Nixt],[Crew],[Amount],[Hr_Rate],[Mhrs],[MapLoc],[Lsq],[Cuts_ID],[Sch_Type],
        [Hold],[Last],[Jb_Mstype],[Freq],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Nov],[Dec],[Sun],[Mon],[Tue],
        [Wed],[Thu],[Fri],[Sat],[Wom1],[Wom2],[Wom3],[Wom4],[Woml],[RtNotes],[JbNotes],[InvNotes],[Bil_Type],[Route],
        [Downcount],[JobID],[Jb_pjb_ID] ,[Jb_program],[Jb_jbp_ID],[Jb_Pdocode],[Jb_Cat],[Jb_s_Cat],[Jb_Type],[Jb_Diff],
        [TaxType],[Avtime],[Jb_addon],[Jb_ArelID],[Jb_adon_ID],[Jb_pseq],[ClipSerialNumberID], [Oct], [Jb_cu_ID],
        [Jb_Def_Qty] , [Jb_QtyUnit], [CustomerOID])
    VALUES (@JobNum , @JobName , @Nixt , @Crew , @Amount , @Hr_Rate , @Mhrs , @MapLoc ,@Lsq , @Cuts_ID , @Sch_Type ,
        @Hold, @Last , @Jb_Mstype , @Freq , @Jan , @Feb , @Mar , @Apr , @May , @Jun , @Jul ,@Aug ,@Sep ,@Nov , @Dec ,
        @Sun , @Mon , @Tue , @Wed , @Thu , @Fri , @Sat , @Wom1 ,@Wom2 , @Wom3 , @Wom4 , @Woml , @RtNotes, @JbNotes ,
        @InvNotes , @Bil_Type , @Route , @Downcount , @JobID , @Jb_pjb_ID , @Jb_program , @Jb_jbp_ID , @Jb_Pdocode,@Jb_Cat,
        @Jb_s_Cat , @Jb_Type , @Jb_Diff , @TaxType ,@Avtime , @Jb_addon , @Jb_ArelID , @Jb_adon_ID , @Jb_pseq,
        @ClipSerialNumberID, @Oct, @Jb_cu_ID, @Jb_Def_Qty , @Jb_QtyUnit,@customerOID
         
 
        )
END

Open in new window

Author

Commented:
RiteshShah

thanks for the help. Your solution works fine

but i have one question ,the thing which you just did
declare @customerOID int
select @customerOID=OID from Customer where ClipSerialNumberID = @ClipSerialNumberID and Cu_ID = @Jb_cu_ID

I need to do this in almost 40 SP and if the copy this same lines in 40 SP that would not what i need as in future i have change in 40 places.

is it possible that i create a new sp for this which will return the customer OID for me
and i call this SP in all 40 SP to get the customer OID
       
well you can do it, have a look at example proc below. I don't have your data so did it on my table, you have to use output parameter.

create proc retID
@name varchar(10),
@dept varchar(10) output
as
select @dept=dept from emps where Name11=@name
GO

--run
declare @d varchar(10)
exec retID @name='Ritesh',@dept=@d output
print @d

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial