To call a custom function from a stored procedure?

lwhflovek

The stored procedure is as follows:
 CREATE PROCEDURE sp_Motive
(
Ranch
@EPUsersName nvarchar (50),
@Motive nvarchar (50),
@Type int
)
AS
DECLARE @StrSql nvarchar (4000)
IF (@ Type = 1)
BEGIN
SET @ StrSql = 'SELECT count (*) from BeiSen_EP_Log as b, BeiSen_Test as a, BeiSen_Users as c, Beisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogEPUserss = d. and d.EP_UsersName='''+@EPUsersName+ '' 'and b.EP_LogId> 3008 and b.EP_LogTestId = 42 and dbo.' + @ Motive + '(EP_LogTestTypePerson) <30'
EXEC sp_executesql @StrSql
END
ELSE IF (@ Type = 2)
BEGIN
SET @ StrSql = 'SELECT count (*) from BeiSen_EP_Log as b, BeiSen_Test as a, BeiSen_Users as c, Beisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogEPUserss = d. and d.EP_UsersName='''+@EPUsersName+ '' 'and b.EP_LogId> 3008 and b.EP_LogTestId = 42 and dbo.' + @ Motive + '(EP_LogTestTypePerson)> = 30 AND dbo.' + @ Motive + '(EP_LogTestTypePerson ) <= 70 '
EXEC sp_executesql @StrSql
END
ELSE IF (@ Type = 3)
BEGIN
SET @ StrSql = 'SELECT count (*) from BeiSen_EP_Log as b, BeiSen_Test as a, BeiSen_Users as c, Beisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogEPUserss = d. and d.EP_UsersName='''+@EPUsersName+ '' 'and b.EP_LogId> 3008 and b.EP_LogTestId = 42 and dbo.' + @ Motive + '(EP_LogTestTypePerson)> 70'
EXEC sp_executesql @StrSql
END
GO
The custom function is as follows:
 CREATE FUNCTION dbo.motivepower (@str nvarchar (50))
RETURNS int
BEGIN
DECLARE @start int
declare @end int
set @ start = charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str) +1) +1) +1) + 1
set @ end = charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str) + 1) +1) +1) +1)
return convert (float, SUBSTRING (@ str, @ start, @ end- @ start))
END other
 The database field EP_LogTestTypePerson has a value similar to 36.2 | 74.5 | 8.7 | 60 | 63.2 | 68.6 | 83.1 | 84 | 37.3 | 17 | 45.9
   The error message when calling is that an invalid length parameter was passed to the substring function.
 Everyone give some advice

zjwzjw

There should be examples in the help documentation .......

lwhflovek

re:zjwzjw
 I have read the examples of the help documents, but they did not solve the problem. That ’s why I ’m here to trouble everyone. This problem has been bothering me for a few days.

goodjianli

Check the value of @end and @start in the custom function. This problem may be that the value of @end-@start is less than 0

chuansnake

CREATE FUNCTION dbo.motivepower(@str nvarchar(50))
RETURNS int
BEGIN
DECLARE @start int
declare @end int
set @start=charindex('|',@str,charindex('|',@str,charindex('|',@str,charindex('|',@str)+1)+1)+1)+ 1
set @end=charindex('|',@str,charindex('|',@str,charindex('|',@str,charindex('|',@str,charindex('|',@str)+ 1)+1)+1)+1)
/*
BUG1:
It is not judged whether charindex('|',@str) is greater than 0.
If charindex('|',@str)>0, then @start=1, @end=0, @end-@start=-1, an error will be reported.

BUG2:
It is not judged how many values ​​@str has. The code should be the fifth value.
But when there are only 4 separators "|", @end=0, and @start>0, @end-@start<0, an error is reported.
*/
return convert(float,SUBSTRING(@str,@start,@end-@start))
END