Error when creating a procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error when creating a procedure

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-12-01 : 09:38:34

I have following code that I run from the query analyzer and it works great:

create table	#JDE_JobQtyDetailTable
		(  jde_date datetime,
                   jde_cost_code nvarchar(15),
		   jde_detail_qty decimal(8,2)
                                       )

declare @JobNumber char(12)
declare @ItemNumber char(8)
declare @JdeDateFrom int
declare @JdeDateTo int       
set @JobNumber = '     1100281'
set @ItemNumber = '0080     ' 
set @JdeDateFrom = 0
set @JdeDateTo = 0


exec ('Call QGPL.get_jobdet(?,?,?,?)', @JobNumber, @ItemNumber, 
@JdeDateFrom, @JdeDateTo) AT as400srv_msdasql
insert into #JDE_JobQtyDetailTable 

select * from #JDE_JobQtyDetailTable
drop table #JDE_JobQtyDetailTable

Please note that I need to have the "insert into..." after the "exec..." for the code to work connection to a db2400 machine

Now, if I try to create a procedure I get error:

CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare 

create table	#JDE_JobQtyDetailTable
		(  jde_date datetime,
           jde_cost_code nvarchar(15),
		   jde_detail_qty decimal(8,2)
                                       )

declare @JobNumber char(12)
declare @ItemNumber char(8)
declare @JdeDateFrom int
declare @JdeDateTo int       
set @JobNumber = '     1100281'
set @ItemNumber = '0080     ' 
set @JdeDateFrom = 0
set @JdeDateTo = 0


exec ('Call QGPL.get_jobdet(?,?,?,?)', @JobNumber, @ItemNumber, 
@JdeDateFrom, @JdeDateTo) AT as400srv_msdasql
insert into #JDE_JobQtyDetailTable 

select * from #JDE_JobQtyDetailTable
drop table #JDE_JobQtyDetailTable
go

I get error:

Msg 156, Level 15, State 1, Procedure sp_Vecellio_ProductionQtyCompare, Line 3
Incorrect syntax near the keyword 'create'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:44:00

you missed as


CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare 
AS
create table	#JDE_JobQtyDetailTable
		(  jde_date datetime,
           jde_cost_code nvarchar(15),
		   jde_detail_qty decimal(8,2)
                                       )

declare @JobNumber char(12)
declare @ItemNumber char(8)
declare @JdeDateFrom int
declare @JdeDateTo int       
set @JobNumber = '     1100281'
set @ItemNumber = '0080     ' 
set @JdeDateFrom = 0
set @JdeDateTo = 0


exec ('Call QGPL.get_jobdet(?,?,?,?)', @JobNumber, @ItemNumber, 
@JdeDateFrom, @JdeDateTo) AT as400srv_msdasql
insert into #JDE_JobQtyDetailTable 

select * from #JDE_JobQtyDetailTable
drop table #JDE_JobQtyDetailTable
go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-01 : 09:44:39

CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare
as
create table #JDE_JobQtyDetailTable
( jde_date datetime,
jde_cost_code nvarchar(15),
jde_detail_qty decimal(8,2)
)

declare @JobNumber char(12)
declare @ItemNumber char(8)
declare @JdeDateFrom int
declare @JdeDateTo int
set @JobNumber = ' 1100281'
set @ItemNumber = '0080 '
set @JdeDateFrom = 0
set @JdeDateTo = 0

exec ('Call QGPL.get_jobdet(?,?,?,?)', @JobNumber, @ItemNumber,
@JdeDateFrom, @JdeDateTo) AT as400srv_msdasql
insert into #JDE_JobQtyDetailTable

select * from #JDE_JobQtyDetailTable
drop table #JDE_JobQtyDetailTable
go

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:46:19

quote:
Originally posted by madhivanan

CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare
as
create table #JDE_JobQtyDetailTable
( jde_date datetime,
jde_cost_code nvarchar(15),
jde_detail_qty decimal(8,2)
)

declare @JobNumber char(12)
declare @ItemNumber char(8)
declare @JdeDateFrom int
declare @JdeDateTo int
set @JobNumber = ' 1100281'
set @ItemNumber = '0080 '
set @JdeDateFrom = 0
set @JdeDateTo = 0

exec ('Call QGPL.get_jobdet(?,?,?,?)', @JobNumber, @ItemNumber,
@JdeDateFrom, @JdeDateTo) AT as400srv_msdasql
insert into #JDE_JobQtyDetailTable

select * from #JDE_JobQtyDetailTable
drop table #JDE_JobQtyDetailTable
go

Madhivanan

Failing to plan is Planning to fail

by 39 s

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-12-01 : 10:07:14

Thank you, got that one to work.
Here is another full procudre where I did not forget the "as" and gives me error:

CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare 

@JobNumber int,
@JobNumber1 char(12)

as

CREATE TABLE	#Equential_JobQtyTable
		(  eq_cost_code nvarchar(15),
		   eq_item_name char(15),
		   eq_sum_qty decimal(8,2)                                             
                                          )
insert into #Equential_JobQtyTable
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 



CREATE TABLE	#JDE_JobQtyTable
		(  jde_cost_code nvarchar(15),
		   jde_sum_qty decimal(8,2) 
                                         )
Exec ('Call QGPL.get_jobqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
insert into #JDE_JobQtyTable 



CREATE TABLE	#JDE_JobName
		(  jde_cost_code nvarchar(15),
		   jde_name char(30),
		   jde_uom char(2)
                                         )
Exec ('Call QGPL.get_jobnam(?)', @JobNumber1) AT AS400SRV_MSDASQL
insert into #JDE_JobName 

CREATE TABLE	#Combined_JobQtyTable
		(  costcode nvarchar(15) unique,
		   itemname char(30),
		   eqsumqty decimal(8,2),
           jdesumqty decimal(8,2),
           differenceqty decimal(8,2),
           jdeuom char(2)
                                 )

insert into #Combined_JobQtyTable(costcode, itemname, eqsumqty)
select eq_cost_code, eq_item_name, eq_sum_qty 
from #Equential_JobQtyTable                                                  

update #Combined_JobQtyTable 
set    jdesumqty = #JDE_JobQtyTable.jde_sum_qty, differenceqty = (eqsumqty - #JDE_JobQtyTable.jde_sum_qty)
from #Combined_JobQtyTable, #JDE_JobQtyTable
where #Combined_JobQtyTable.costcode = #JDE_JobQtyTable.jde_cost_code

insert into #Combined_JobQtyTable (costcode, jdesumqty, differenceqty) 
select distinct t2.jde_cost_code, t2.jde_sum_qty, t2.jde_sum_qty * -1
from #JDE_JobQtyTable AS t2
where not exists (select * from #Combined_JobQtyTable AS t1 where t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qty)

update #Combined_JobQtyTable
set itemname = #Jde_JobName.jde_name, jdeuom = #JDE_JobName.jde_uom 
from #Combined_JobQtyTable, #JDE_JobName
where #Combined_JobQtyTable.costcode = #JDE_JobName.jde_cost_code 

select  *  
from #Combined_JobQtyTable 
order by costcode

drop table #Equential_JobQtyTable
drop table #JDE_JobQtyTable
drop table #Combined_JobQtyTable
drop table #JDE_JobName

go

Msg 156, Level 15, State 1, Procedure sp_Vecellio_ProductionQtyCompare, Line 27
Incorrect syntax near the keyword 'CREATE'.
Msg 156, Level 15, State 1, Procedure sp_Vecellio_ProductionQtyCompare, Line 35
Incorrect syntax near the keyword 'CREATE'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 10:24:47

dont mix ddl and dml


CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare 

@JobNumber int,
@JobNumber1 char(12)

as
CREATE TABLE	#JDE_JobName
		(  jde_cost_code nvarchar(15),
		   jde_name char(30),
		   jde_uom char(2)
                                         )

CREATE TABLE	#Equential_JobQtyTable
		(  eq_cost_code nvarchar(15),
		   eq_item_name char(15),
		   eq_sum_qty decimal(8,2)                                             
                                          )

CREATE TABLE	#JDE_JobQtyTable
		(  jde_cost_code nvarchar(15),
		   jde_sum_qty decimal(8,2) 
                                         )
CREATE TABLE	#Combined_JobQtyTable
		(  costcode nvarchar(15) unique,
		   itemname char(30),
		   eqsumqty decimal(8,2),
           jdesumqty decimal(8,2),
           differenceqty decimal(8,2),
           jdeuom char(2)
                                 )

insert into #Equential_JobQtyTable
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 



insert into #JDE_JobQtyTable
Exec ('Call QGPL.get_jobqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
 



insert into #JDE_JobName 
Exec ('Call QGPL.get_jobnam(?)', @JobNumber1) AT AS400SRV_MSDASQL




insert into #Combined_JobQtyTable(costcode, itemname, eqsumqty)
select eq_cost_code, eq_item_name, eq_sum_qty 
from #Equential_JobQtyTable                                                  

update #Combined_JobQtyTable 
set    jdesumqty = #JDE_JobQtyTable.jde_sum_qty, differenceqty = (eqsumqty - #JDE_JobQtyTable.jde_sum_qty)
from #Combined_JobQtyTable, #JDE_JobQtyTable
where #Combined_JobQtyTable.costcode = #JDE_JobQtyTable.jde_cost_code

insert into #Combined_JobQtyTable (costcode, jdesumqty, differenceqty) 
select distinct t2.jde_cost_code, t2.jde_sum_qty, t2.jde_sum_qty * -1
from #JDE_JobQtyTable AS t2
where not exists (select * from #Combined_JobQtyTable AS t1 where t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qty)

update #Combined_JobQtyTable
set itemname = #Jde_JobName.jde_name, jdeuom = #JDE_JobName.jde_uom 
from #Combined_JobQtyTable, #JDE_JobName
where #Combined_JobQtyTable.costcode = #JDE_JobName.jde_cost_code 

select  *  
from #Combined_JobQtyTable 
order by costcode

drop table #Equential_JobQtyTable
drop table #JDE_JobQtyTable
drop table #Combined_JobQtyTable
drop table #JDE_JobName

go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources