select @iObjectCount = 0;CleanUp: select @vchProjectName select @iObjectCount returnE_General_Error: /* this is an all or nothing. No specific error messages */ goto CleanUpE_OAError: exec dbo.dt_displayoaerror @iObjectId, @iReturn goto CleanUpGO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOALTER PROCEDURE [DBO].[ADD_PRICE] @intPROD_ID INT, @intCOMPANY_ID INT, @intCOMPANY_PRICE NUMERIC(18,2), @intOPTION_USER_TYPE INT, --操作员类型1总站人员2分站人员3商家 @intOPTION_USER_ID INT, --操作员ID @intPRICE_AREA_ID INT, --分站地区ID @intIS_UPDATE INT, --0为新增报价,1为更新报价,2为删除报价 @intCOMPANY_PRICE_ID INT, --更新报价的ID @intReturn INT OUTPUT AS DECLARE @intMAX_PRICE NUMERIC(18,2) DECLARE @intMIN_PRICE NUMERIC(18,2) DECLARE @intAVG_PRICE NUMERIC(18,2) DECLARE @strSTART_DATE DATETIME DECLARE @strEND_DATE DATETIME DECLARE @intROWS INT DECLARE @intTREND INT DECLARE @intPRICE_DIFF_NOW NUMERIC(18,2) DECLARE @intPRICE_DIFF_OLD NUMERIC(18,2) DECLARE @intPRICE_DIFF NUMERIC(18,2) DECLARE @intCOUNTPRICERECORD INT DECLARE @intTODAY INT SELECT @intTODAY = datediff(d,STR(YEAR(GETDATE()),4),GETDATE()) SELECT @intROWS = 0 SELECT @strSTART_DATE = CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),103),103) SELECT @strEND_DATE = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(D,1,GETDATE()),103),103) --print @strSTART_DATE --print @strEND_DATE --向商家报价表插入记录 IF (@intIS_UPDATE = 1 OR @intIS_UPDATE=2 ) BEGIN INSERT INTO COMPANY_PRICE_HISTORY (COMPANY_PRICE_ID,PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE) SELECT COMPANY_PRICE_ID,PROD_ID,COMPANY_ID,COMPANY_PRICE,@intOPTION_USER_TYPE AS OPTION_USER_TYPE,@intOPTION_USER_ID AS OPTION_USER_ID,@intPRICE_AREA_ID AS PRICE_AREA_ID,2 AS IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE FROM COMPANY_PRICE WHERE COMPANY_PRICE_ID = @intCOMPANY_PRICE_ID --DELETE FROM COMPANY_PRICE WHERE COMPANY_PRICE_ID = @intCOMPANY_PRICE_ID --PRINT 'AAA' END DELETE FROM COMPANY_PRICE WHERE PROD_ID = @intPROD_ID AND COMPANY_ID=@intCOMPANY_ID IF (@intIS_UPDATE =0 OR @intIS_UPDATE =1) BEGIN INSERT INTO COMPANY_PRICE(PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY) VALUES(@intPROD_ID,@intCOMPANY_ID,@intCOMPANY_PRICE,@intOPTION_USER_TYPE,@intOPTION_USER_ID,@intPRICE_AREA_ID,@intIS_UPDATE,YEAR(GETDATE()),MONTH(GETDATE()),@intTODAY) INSERT INTO COMPANY_PRICE_HISTORY(PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE) VALUES(@intPROD_ID,@intCOMPANY_ID,@intCOMPANY_PRICE,@intOPTION_USER_TYPE,@intOPTION_USER_ID,@intPRICE_AREA_ID,@intIS_UPDATE,YEAR(GETDATE()),MONTH(GETDATE()),@intTODAY,GETDATE()) END SELECT @intCOUNTPRICERECORD = COUNT(*) FROM COMPANY_PRICE WHERE PROD_ID = @intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID IF @intCOUNTPRICERECORD > 0 BEGIN --向当前商品价格表插入记录 SELECT @intMAX_PRICE = MAX(COMPANY_PRICE) ,@intMIN_PRICE=MIN(COMPANY_PRICE) FROM COMPANY_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE>=@strSTART_DATE SELECT @intAVG_PRICE = (@intMAX_PRICE + @intMIN_PRICE) / 2
DELETE FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID INSERT INTO PROD_PRICE(PROD_ID,MAX_PRICE,MIN_PRICE,AVG_PRICE,PRICE_AREA_ID) VALUES(@intPROD_ID,@intMAX_PRICE,@intMIN_PRICE,@intAVG_PRICE,@intPRICE_AREA_ID)
--向历史记录表插入记录 DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE<@strEND_DATE INSERT INTO PROD_PRICE_HISTORY(PROD_ID,MAX_PRICE,MIN_PRICE,AVG_PRICE,PRICE_AREA_ID) VALUES(@intPROD_ID,@intMAX_PRICE,@intMIN_PRICE,@intAVG_PRICE,@intPRICE_AREA_ID)
--计算当前涨跌情况
DECLARE TREND_Cursor CURSOR FOR SELECT TOP 2 MIN_PRICE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID ORDER BY CREATE_DATE DESC
OPEN TREND_Cursor
FETCH NEXT FROM TREND_Cursor INTO @intPRICE_DIFF WHILE @@FETCH_STATUS = 0 BEGIN SELECT @intROWS = @intROWS + 1 IF (@intROWS = 1) BEGIN SELECT @intPRICE_DIFF_NOW = @intPRICE_DIFF END ELSE BEGIN SELECT @intPRICE_DIFF_OLD = @intPRICE_DIFF END FETCH NEXT FROM TREND_Cursor INTO @intPRICE_DIFF END
CLOSE TREND_Cursor DEALLOCATE TREND_Cursor
IF (@intROWS = 0) --OR (@intROWS = 1) BEGIN SELECT @intTREND = 0 SELECT @intPRICE_DIFF = 0 END ELSE BEGIN SELECT @intPRICE_DIFF = @intPRICE_DIFF_NOW - @intPRICE_DIFF_OLD --print @intPRICE_DIFF --SELECT @intPRICE_DIFF = ABS(@intPRICE_DIFF - @intMIN_PRICE) --print @intPRICE_DIFF_NOW --print @intMIN_PRICE --print @intPRICE_DIFF IF @intPRICE_DIFF < 0 BEGIN SELECT @intTREND = 3 --降价 END
IF @intPRICE_DIFF > 0 BEGIN SELECT @intTREND = 2 --涨价 END
IF @intPRICE_DIFF = 0 BEGIN SELECT @intTREND = 1 --平价 END
END
SELECT @intPRICE_DIFF = ABS(@intPRICE_DIFF)
UPDATE PROD_PRICE SET PRICE_TREND = @intTREND,PRICE_DIFF = @intPRICE_DIFF WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
--向历史记录表插入记录 --DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE>=@strSTART_DATE --INSERT INTO PROD_PRICE_HISTORY SELECT * FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID UPDATE PROD_PRICE_HISTORY SET PRICE_TREND = @intTREND,PRICE_DIFF = @intPRICE_DIFF WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE>=@strSTART_DATE IF @intIS_UPDATE<2 AND @intOPTION_USER_TYPE=3 BEGIN UPDATE COMPANY SET postprice_no = postprice_no -1 WHERE ID = @intCOMPANY_ID END END ELSE BEGIN DELETE FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID END SELECT @intReturn = @@ERRORGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
查询分析器有存储过程的模板,套着模板写就可以了,例如: -- ============================================= -- Create procedure basic template -- ============================================= -- creating the store procedure IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<procedure_name, sysname, proc_test>' AND type = 'P') DROP PROCEDURE <procedure_name, sysname, proc_test> GOCREATE PROCEDURE <procedure_name, sysname, proc_test> <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0> AS SELECT @p1, @p2 GO-- ============================================= -- example to execute the store procedure -- ============================================= EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2> GO
上面有一些是写成存储过程的,有些不是,希望对你有所帮助
@vchSourceSafeINI varchar(255) = '',
@vchProjectName varchar(255) ='',
@vchComment varchar(255) ='',
@vchLoginName varchar(255) ='',
@vchPassword varchar(255) =''asset nocount ondeclare @iReturn int
declare @iObjectId int
select @iObjectId = 0declare @iStreamObjectId int
select @iStreamObjectId = 0declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'declare @vchDatabaseName varchar(255)
select @vchDatabaseName = db_name()declare @iReturnValue int
select @iReturnValue = 0declare @iPropertyObjectId int
declare @vchParentId varchar(255)declare @iObjectCount int
select @iObjectCount = 0 exec @iReturn = master.dbo.sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError
/* Create Project in SS */
exec @iReturn = master.dbo.sp_OAMethod @iObjectId,
'AddProjectToSourceSafe',
NULL,
@vchSourceSafeINI,
@vchProjectName output,
@@SERVERNAME,
@vchDatabaseName,
@vchLoginName,
@vchPassword,
@vchComment
if @iReturn <> 0 GOTO E_OAError /* Set Database Properties */ begin tran SetProperties /* add high level object */ exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID' select @vchParentId = CONVERT(varchar(255),@iPropertyObjectId) exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL if @@error <> 0 GOTO E_General_Error commit tran SetProperties
select @iObjectCount = 0;CleanUp:
select @vchProjectName
select @iObjectCount
returnE_General_Error:
/* this is an all or nothing. No specific error messages */
goto CleanUpE_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUpGO
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE [DBO].[ADD_PRICE]
@intPROD_ID INT,
@intCOMPANY_ID INT,
@intCOMPANY_PRICE NUMERIC(18,2),
@intOPTION_USER_TYPE INT, --操作员类型1总站人员2分站人员3商家
@intOPTION_USER_ID INT, --操作员ID
@intPRICE_AREA_ID INT, --分站地区ID
@intIS_UPDATE INT, --0为新增报价,1为更新报价,2为删除报价
@intCOMPANY_PRICE_ID INT, --更新报价的ID
@intReturn INT OUTPUT
AS
DECLARE @intMAX_PRICE NUMERIC(18,2)
DECLARE @intMIN_PRICE NUMERIC(18,2)
DECLARE @intAVG_PRICE NUMERIC(18,2)
DECLARE @strSTART_DATE DATETIME
DECLARE @strEND_DATE DATETIME
DECLARE @intROWS INT
DECLARE @intTREND INT
DECLARE @intPRICE_DIFF_NOW NUMERIC(18,2)
DECLARE @intPRICE_DIFF_OLD NUMERIC(18,2)
DECLARE @intPRICE_DIFF NUMERIC(18,2)
DECLARE @intCOUNTPRICERECORD INT
DECLARE @intTODAY INT
SELECT @intTODAY = datediff(d,STR(YEAR(GETDATE()),4),GETDATE())
SELECT @intROWS = 0
SELECT @strSTART_DATE = CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),103),103)
SELECT @strEND_DATE = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(D,1,GETDATE()),103),103)
--print @strSTART_DATE
--print @strEND_DATE
--向商家报价表插入记录
IF (@intIS_UPDATE = 1 OR @intIS_UPDATE=2 )
BEGIN
INSERT INTO COMPANY_PRICE_HISTORY (COMPANY_PRICE_ID,PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE) SELECT COMPANY_PRICE_ID,PROD_ID,COMPANY_ID,COMPANY_PRICE,@intOPTION_USER_TYPE AS OPTION_USER_TYPE,@intOPTION_USER_ID AS OPTION_USER_ID,@intPRICE_AREA_ID AS PRICE_AREA_ID,2 AS IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE FROM COMPANY_PRICE WHERE COMPANY_PRICE_ID = @intCOMPANY_PRICE_ID
--DELETE FROM COMPANY_PRICE WHERE COMPANY_PRICE_ID = @intCOMPANY_PRICE_ID
--PRINT 'AAA'
END
DELETE FROM COMPANY_PRICE WHERE PROD_ID = @intPROD_ID AND COMPANY_ID=@intCOMPANY_ID
IF (@intIS_UPDATE =0 OR @intIS_UPDATE =1)
BEGIN
INSERT INTO COMPANY_PRICE(PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY) VALUES(@intPROD_ID,@intCOMPANY_ID,@intCOMPANY_PRICE,@intOPTION_USER_TYPE,@intOPTION_USER_ID,@intPRICE_AREA_ID,@intIS_UPDATE,YEAR(GETDATE()),MONTH(GETDATE()),@intTODAY)
INSERT INTO COMPANY_PRICE_HISTORY(PROD_ID,COMPANY_ID,COMPANY_PRICE,OPTION_USER_TYPE,OPTION_USER_ID,PRICE_AREA_ID,IS_UPDATE,THEYEAR,THEMONTH,THEDAY,CREATE_DATE) VALUES(@intPROD_ID,@intCOMPANY_ID,@intCOMPANY_PRICE,@intOPTION_USER_TYPE,@intOPTION_USER_ID,@intPRICE_AREA_ID,@intIS_UPDATE,YEAR(GETDATE()),MONTH(GETDATE()),@intTODAY,GETDATE())
END
SELECT @intCOUNTPRICERECORD = COUNT(*) FROM COMPANY_PRICE WHERE PROD_ID = @intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
IF @intCOUNTPRICERECORD > 0
BEGIN
--向当前商品价格表插入记录
SELECT @intMAX_PRICE = MAX(COMPANY_PRICE) ,@intMIN_PRICE=MIN(COMPANY_PRICE) FROM COMPANY_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE>=@strSTART_DATE
SELECT @intAVG_PRICE = (@intMAX_PRICE + @intMIN_PRICE) / 2
DELETE FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
INSERT INTO PROD_PRICE(PROD_ID,MAX_PRICE,MIN_PRICE,AVG_PRICE,PRICE_AREA_ID) VALUES(@intPROD_ID,@intMAX_PRICE,@intMIN_PRICE,@intAVG_PRICE,@intPRICE_AREA_ID)
--向历史记录表插入记录
DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE<@strEND_DATE
INSERT INTO PROD_PRICE_HISTORY(PROD_ID,MAX_PRICE,MIN_PRICE,AVG_PRICE,PRICE_AREA_ID) VALUES(@intPROD_ID,@intMAX_PRICE,@intMIN_PRICE,@intAVG_PRICE,@intPRICE_AREA_ID)
--计算当前涨跌情况
DECLARE TREND_Cursor CURSOR FOR SELECT TOP 2 MIN_PRICE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID ORDER BY CREATE_DATE DESC
OPEN TREND_Cursor
FETCH NEXT FROM TREND_Cursor INTO @intPRICE_DIFF
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @intROWS = @intROWS + 1
IF (@intROWS = 1)
BEGIN
SELECT @intPRICE_DIFF_NOW = @intPRICE_DIFF
END
ELSE
BEGIN
SELECT @intPRICE_DIFF_OLD = @intPRICE_DIFF
END
FETCH NEXT FROM TREND_Cursor INTO @intPRICE_DIFF
END
CLOSE TREND_Cursor
DEALLOCATE TREND_Cursor
IF (@intROWS = 0) --OR (@intROWS = 1)
BEGIN
SELECT @intTREND = 0
SELECT @intPRICE_DIFF = 0
END
ELSE
BEGIN
SELECT @intPRICE_DIFF = @intPRICE_DIFF_NOW - @intPRICE_DIFF_OLD
--print @intPRICE_DIFF
--SELECT @intPRICE_DIFF = ABS(@intPRICE_DIFF - @intMIN_PRICE)
--print @intPRICE_DIFF_NOW
--print @intMIN_PRICE
--print @intPRICE_DIFF
IF @intPRICE_DIFF < 0
BEGIN
SELECT @intTREND = 3 --降价
END
IF @intPRICE_DIFF > 0
BEGIN
SELECT @intTREND = 2 --涨价
END
IF @intPRICE_DIFF = 0
BEGIN
SELECT @intTREND = 1 --平价
END
END
SELECT @intPRICE_DIFF = ABS(@intPRICE_DIFF)
UPDATE PROD_PRICE SET PRICE_TREND = @intTREND,PRICE_DIFF = @intPRICE_DIFF WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
--向历史记录表插入记录
--DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE>=@strSTART_DATE
--INSERT INTO PROD_PRICE_HISTORY SELECT * FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
UPDATE PROD_PRICE_HISTORY SET PRICE_TREND = @intTREND,PRICE_DIFF = @intPRICE_DIFF WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID AND CREATE_DATE >=@strSTART_DATE AND CREATE_DATE>=@strSTART_DATE
IF @intIS_UPDATE<2 AND @intOPTION_USER_TYPE=3
BEGIN
UPDATE COMPANY SET postprice_no = postprice_no -1 WHERE ID = @intCOMPANY_ID
END
END
ELSE
BEGIN
DELETE FROM PROD_PRICE WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
DELETE FROM PROD_PRICE_HISTORY WHERE PROD_ID=@intPROD_ID AND PRICE_AREA_ID = @intPRICE_AREA_ID
END
SELECT @intReturn = @@ERRORGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GOCREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO