Function execution error - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Function execution error

Author

Topic

kingsleen_sqlteam
Starting Member

4 Posts

Posted - 2007-10-25 : 01:10:23

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 = len(@val);
if (len(@val) >= len(@tempId))
Begin
set @val = 'PR' + @val;
End
else
Begin
while (len(@val) < len(@tempId))
Begin
set @val = '0' + @val;
End

set @val = 'PR' + @val;
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 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 = len(@val)
if (len(@val) >= len(@tempId))
begin
set @val = 'AD' + @val
end
else
Begin
while (len(@val) < len(@tempId))
Begin
set @val = 0 + @val
end
set @val = 'AD' + @val
End
set @AdjustmentId = @val + @contractNo
END -- main if
else
begin
set @AdjustmentId = 'AD0001' + @contractNo

end

RETURN @AdjustmentId

END
--main
GO

select dbo.sampleNewRequestId (99)

While executing the function i am getting the following error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'CORP' to data type int.

Kindly help me

Kingsleen.J

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-10-25 : 01:32:03

what i think that problem is when you are trying concatinating varchar and integer and trying putting it in int field.
set @tempId = Substring(@requestsId,2, (len(@requestsId) - @siteLength));
set @valint =@tempId+1;
set @val = @valint;
set @min = len(@val);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources