The problem of calling a custom function from a stored procedure

lwhflovek

The stored procedure is as follows:
 CREATE PROCEDURE sp_Motive
(

@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, Beiisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogTestId = a.Test_Id and b.EP_LogEPUserIds = 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, Beiisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogTestId = a.Test_Id and b.EP_LogEPUserIds = 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, Beiisen_EP_Users as d where c.Users_Id = b.EP_LogUsersId and b.EP_LogTestId = a.Test_Id and b.EP_LogEPUserIds = 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

ellerywang

SUBSTRING (@ str, @ start, @ end- @ start)
There is a problem with this function
@ end- @ start, you better use a variable to save the calculated value and then pass it to the function

lwhflovek

re:ellerywang
 Isn't @ end- @ start a variable? Can you make it clear?
 I also found out the problem with SUBSTRING (@ str, @ start, @ end- @ start). When this custom function is executed directly, garbled characters will appear at the end of the line. . . .

rcloud

print SUBSTRING (@ str, @ start, @ end- @ start)
Just type it out first ... when did it go wrong ...

everever30

First look at @start, what is the value of @ end- @ start, the parameter is not correct.

lwhflovek

re:rcloudThe custom function I called directly, I don't know how to print. . sweat.
 re: lzzyfish @start, the value of @ end- @ start has floating point type and integer type

rcloud

re:rcloudThe custom function I called directly, I don't know how to print. . sweat.
-------------------------------------------------- ---------
You can write him as a sql statement to test it .. It must be tested in the function ..

rcloud

declare @str nvarchar (50)
set @ str = '36 .2 | 74.5 | 8.7 | 60 | 63.2 | 68.6 | 83.1 | 84 | 37.3 | 17 '
DECLARE @start int
declare @end int
set @ start = charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str) +1) +1) +1) + 1
print @start
set @ end = charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str, charindex ('|', @ str) + 1) +1) +1) +1)
print @end
print convert (float, SUBSTRING (@ str, @ start, @ end- @ start))

result
-------------
18
twenty two
63.2

lwhflovek

Can all the database fields be printed?