ramibn1 بتاريخ: 9 أبريل 2007 تقديم بلاغ مشاركة بتاريخ: 9 أبريل 2007 الرجاء ايضاح لماذا لايضيف البيانات الى الجدول وشكرا CREATE PROCEDURE [dbo].[sP_GM_GeographyByZone] ASdeclare @sqlstr as varchar(8000)DECLARE @RB AS VARCHAR(8000)DECLARE @RB1 AS VARCHAR(8000)DECLARE @Year AS INTif exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[dbo].[GM_GeographyByZone]') and type = 'U')BEGIN transactionTRUNCATE TABLE dbo.GM_GeographyByZoneDROP TABLE dbo.GM_GeographyByZonecommitif 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 اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
hanyfreedom بتاريخ: 23 أبريل 2007 تقديم بلاغ مشاركة بتاريخ: 23 أبريل 2007 ?from what source you come by that اقتباس رابط هذا التعليق شارك المزيد من خيارات المشاركة
Recommended Posts
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.