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 2000 Forums
 Import/Export (DTS) and Replication (2000)
 procedure not working

Author  Topic 

ramibn1
Starting Member

2 Posts
Posted - 2007-04-09 : 15:15:22
hey all
im trying to run this procedure but it's not running
and if i sperate each part it will work except the insertion part
could you give me any hint thanks
CREATE PROCEDURE [dbo].[SP_GM_GeographyByZone] AS

declare @sqlstr as varchar(8000)
DECLARE @RB AS VARCHAR(8000)
DECLARE @RB1 AS VARCHAR(8000)
DECLARE @Year AS INT
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[GM_GeographyByZone]') and type = 'U')
BEGIN transaction
TRUNCATE TABLE dbo.GM_GeographyByZone
DROP TABLE dbo.GM_GeographyByZone
commit

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[GM_GeographyByZone]') and type = 'U')

BEGIN
-- DECLARE @Year AS INT
SET @Year=year(getdate())
DECLARE @tbl varchar(8000)
SET @tbl = 'CREATE TABLE [dbo].[GM_GeographyByZone] (
[gmcustomeManufacturername_vc] [varchar] (50) NULL,
[makedescription_vc] [varchar] (50) NULL,
[' + convert(varchar(4),@year-6) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-5) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-4) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-3) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-2) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-1) + '][varchar] (4) NULL
) ON [PRIMARY]'
exec(@tbl)
END
DECLARE @year int
SET @Year=year(getdate())
declare @RB varchar(8000)
SET @RB = 'INSERT INTO [dbo].[GM_GeographyByZone](
[gmcustomeManufacturername_vc] ,
[makedescription_vc] ,
[' + convert(varchar(4),@year-6) + '],
[' + convert(varchar(4),@year-5) + '],
[' + convert(varchar(4),@year-4) + '],
[' + convert(varchar(4),@year-3) + '],
[' + convert(varchar(4),@year-2) + '],
[' + convert(varchar(4),@year-1) + ']
)'
exec(@RB)

-- DECLARE @YEAR as INT
SET @Year=year(getdate())
declare @sqlstr varchar(8000)

SET @sqlstr = 'SELECT distinct C.makedescription_vc,C.gmcustomemanufacturername_vc, (select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-6 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-5 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-4 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-3 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-2 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-1 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
from dbo.GM_TEMP_VehicleCountByDA C
order by C.makedescription_vc,C.gmcustomemanufacturername_vc'

exec(@sqlstr)

go

X002548
Not Just a Number

15586 Posts
Posted - 2007-04-09 : 15:19:05
ummm, why all the dynamic sql?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

ramibn1
Starting Member

2 Posts
Posted - 2007-04-09 : 15:41:11

ok this is another copy of it and i did it this way

the part that is not working is the insertion part



thanks again



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GM_GeographyByZone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_GM_GeographyByZone]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[SP_GM_GeographyByZone] AS

declare @sqlstr as varchar(8000)
DECLARE @RB AS VARCHAR(8000)
DECLARE @RB1 AS VARCHAR(8000)
DECLARE @Year AS INT
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[GM_GeographyByZone]') and type = 'U')
BEGIN transaction
TRUNCATE TABLE dbo.GM_GeographyByZone
DROP TABLE dbo.GM_GeographyByZone
commit

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[GM_GeographyByZone]') and type = 'U')

BEGIN
-- DECLARE @Year AS INT
SET @Year=year(getdate())
DECLARE @tbl varchar(8000)
SET @tbl = 'CREATE TABLE [dbo].[GM_GeographyByZone] (
[gmcustomeManufacturername_vc] [varchar] (50) NULL,
[makedescription_vc] [varchar] (50) NULL,
[' + convert(varchar(4),@year-6) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-5) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-4) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-3) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-2) + '][varchar] (4) NULL,
[' + convert(varchar(4),@year-1) + '][varchar] (4) NULL
) ON [PRIMARY]'
exec(@tbl)
END
-- DECLARE @year int
SET @Year=year(getdate())
-- declare @RB varchar(8000)
SET @RB = 'INSERT INTO [dbo].[GM_GeographyByZone](
[gmcustomeManufacturername_vc] ,
[makedescription_vc] ,
[' + convert(varchar(4),@year-6) + '],
[' + convert(varchar(4),@year-5) + '],
[' + convert(varchar(4),@year-4) + '],
[' + convert(varchar(4),@year-3) + '],
[' + convert(varchar(4),@year-2) + '],
[' + convert(varchar(4),@year-1) + ']
)'
exec(@RB)

-- DECLARE @YEAR as INT
SET @Year=year(getdate())
-- declare @sqlstr varchar(8000)

SET @sqlstr = 'SELECT distinct C.makedescription_vc,C.gmcustomemanufacturername_vc, (select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-6 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-5 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-4 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-3 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-2 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
,(select isnull(sum(vehicleCount_in),0)
gmcustomtypecode_vc from dbo.GM_TEMP_VehicleCountByDA b
inner join dbo.GMSQLDBDomesticGeographyData a
on a.PK_DA_vc=b.PK_DA_vc
where reportyear_in=' + convert(varchar(4),@year-1 ) + '
and ZoneName_vc=''ONTARIO''
and b.makedescription_vc=C.makedescription_vc
and gmprimarycode_vc=''R''
and gmcustomtypecode_vc = ''C'')
from dbo.GM_TEMP_VehicleCountByDA C
order by C.makedescription_vc,C.gmcustomemanufacturername_vc'

exec(@sqlstr)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site