今天遇到个小问题大家看看。
CREATE PROCEDURE prcInsertProject @proID char(8) output, @proName varchar(100),@startDate datetime,@endDate datetime,@freeOrNo char(2),
@softVersion varchar(100),@mendInfo varchar(1000),@dataVersion varchar(1000),@Module varchar(1000),@money float,@status char(10),
@domin varchar(50)
AS
begin
select @proID=Max(proID) from Project
select @proID=
case
when @proID >=0 and @proID<9 then
'0000000'+Convert(char,@proID)
when @proID>=9 and @proID<99 then
'000000'+Convert(char,@proID)
when @proID>=99 and @proID<999 then
'00000'+Convert(char,@proID)
when @proID>999 and @proID<9999 then
'0000'+Convert(char,@proID)
when @proID>9999 and @proID<99999 then
'000'+Convert(char,@proID)
when @proID>99999 and @proID<999999 then
'00'+Convert(char,@proID)
when @proID>999999 and @proID<9999999 then
'0'+Convert(char,@proID)
when @proID>9999999 and @proID<99999999 then
Convert(char,@proID)
else
'00000000'
end
print @proID
insert Project values(@proID,@proName,@startDate,@endDate,@freeOrNo,@softVersion,@mendInfo,@dataVersion,@Module,@money,@status,@domin)
end
GO其中startdate和enddate是两个dattme类型,
存储过程检查正确。
但是exec 执行存储过程时,加参数‘2006-11-22’或,2006-11-22,时都会报错,说,从varchar转道dattime时出错。
但是用insert 语句是却可以
CREATE PROCEDURE prcInsertProject @proID char(8) output, @proName varchar(100),@startDate datetime,@endDate datetime,@freeOrNo char(2),
@softVersion varchar(100),@mendInfo varchar(1000),@dataVersion varchar(1000),@Module varchar(1000),@money float,@status char(10),
@domin varchar(50)
AS
begin
select @proID=Max(proID) from Project
select @proID=
case
when @proID >=0 and @proID<9 then
'0000000'+Convert(char,@proID)
when @proID>=9 and @proID<99 then
'000000'+Convert(char,@proID)
when @proID>=99 and @proID<999 then
'00000'+Convert(char,@proID)
when @proID>999 and @proID<9999 then
'0000'+Convert(char,@proID)
when @proID>9999 and @proID<99999 then
'000'+Convert(char,@proID)
when @proID>99999 and @proID<999999 then
'00'+Convert(char,@proID)
when @proID>999999 and @proID<9999999 then
'0'+Convert(char,@proID)
when @proID>9999999 and @proID<99999999 then
Convert(char,@proID)
else
'00000000'
end
print @proID
insert Project values(@proID,@proName,@startDate,@endDate,@freeOrNo,@softVersion,@mendInfo,@dataVersion,@Module,@money,@status,@domin)
end
GO其中startdate和enddate是两个dattme类型,
存储过程检查正确。
但是exec 执行存储过程时,加参数‘2006-11-22’或,2006-11-22,时都会报错,说,从varchar转道dattime时出错。
但是用insert 语句是却可以
@softVersion varchar(100),@mendInfo varchar(1000),@dataVersion varchar(1000),@Module varchar(1000),@money float,@status char(10),
@domin varchar(50)
AS
begin
select @proID=Max(proID) from Project
select @proID=
case
when @proID >=0 and @proID<99999999 then
right('00000000'+Convert(char,@proID),8)
else
'00000000'
end
print @proID
insert Project values(@proID,@proName,@startDate,@endDate,@freeOrNo,@softVersion,@mendInfo,@dataVersion,@Module,@money,@status,@domin)
end
GO
declare @s datetime
set @s = '2006-11-22'
exec prcInsertProject ...,@s,...