October 24, 2007 at 11:22 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION sampleNewRequestId
(
@siteID int
)
RETURNS varchar(20)
AS
BEGIN
Declare @requestsId varchar(20)
DECLARE @contractNo varchar(20), @siteLength int, @requestId varchar(20), @tempId varchar(20)
select @contractNo = contractno from Site_Vw where SiteID=@SiteID
set @siteLength = Len(@contractNo + 2);
select @requestId = RequestId from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId
Declare @valint int,@val varchar,@min int
if ((select count(*) from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId) > 0)
Begin
select @requestsId = RequestId from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId
set @tempId = Substring(@requestsId,2, (len(@requestsId) - @siteLength));
set @valint =@tempId+1;
set @val = @valint;
set @min-2 = len(@val);
if (len(@val) >= len(@tempId))
Begin
End
else
Begin
while (len(@val) < len(@tempId))
Begin
End
End
set @requestsId = @val + @contractNo;
End
ELSE
set @requestsId = 'PR00001' + @contractNo
RETURN @requestsId
END
GO
select dbo.sampleNewRequestId (99)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION NewAdjustmentId
(
@siteID int
)
--declare @siteID int
--set @siteID = 99
RETURNS varchar(20)
AS
BEGIN --main
declare @AdjustmentId varchar(20)
DECLARE @contractNo varchar(20), @siteLength int, @requestId varchar(20)
select @contractNo= contractno from dbo.Site_Vw where SiteID=@SiteID
set @siteLength = len(@contractNo) + 2
select @AdjustmentId = AdjustmentsId from Adjustments where (substring(@AdjustmentId,3,(len(@AdjustmentId)-@siteLength))) =(select max((substring(AdjustmentsId,3,(len(AdjustmentsId)-@siteLength)))) from Adjustments where SiteId=@SiteId) and SiteId=@SiteId
Declare @tempId varchar(20), @valint int, @val varchar(20), @min-2 int
if ((select count(*) from Adjustments where (substring(@AdjustmentId,3,(len(@AdjustmentId)-@siteLength))) =(select max((substring(@AdjustmentId,3,(len(@AdjustmentId)-@siteLength)))) from Adjustments where SiteId=@SiteId) and SiteId=@SiteId)>0)
Begin -- main if
select @AdjustmentId = AdjustmentsId from Adjustments where (substring(@AdjustmentId,3,(len(@AdjustmentId)-@siteLength))) =(select max((substring(@AdjustmentId,3,(len(@AdjustmentId)-@siteLength)))) from Adjustments where SiteId=@SiteId) and SiteId=@SiteId
set @valint = 0
set @tempId = Substring(@AdjustmentId,2, len(@AdjustmentId) - @siteLength)
set @valint = @tempId + 1
set @val = @valint
set @min-2 = len(@val)
if (len(@val) >= len(@tempId))
begin
end
else
Begin
while (len(@val) < len(@tempId))
Begin
end
End
set @AdjustmentId = @val + @contractNo
END -- main if
else
begin
set @AdjustmentId = 'AD0001' + @contractNo
end
RETURN @AdjustmentId
END
--main
GO
select dbo.NewRequestId (99)
While Executing the function i am getting the following error kindly help me
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'CORP' to data type int.
October 24, 2007 at 11:47 pm
please find the similiar problems faced by others and the solution provided...
forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772833&SiteID=1
October 25, 2007 at 7:43 am
I can't see it, but somewhere in there you're adding or setting a string to a number.
Try debugging it by adding PRINT Statements to watch the values change. That's how I'd go about catching it if I couldn't identify readily where the error was occurring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2007 at 8:42 am
What is the data type of SiteId in these objects?
dbo.Site_Vw
Adjustments
PurchaseRequest
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply