存储过程如下:
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,Beisen_EP_Users as d where c.Users_Id=b.EP_LogUsersId and b.EP_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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
自定义函数如下:
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 其他的类似
数据库字段EP_LogTestTypePerson的值类似36.2|74.5|8.7|60|63.2|68.6|83.1|84|37.3|17|45.9
调用的时候提示错误是:向 substring 函数传递了无效的 length 参数。
各位给点意见阿
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,Beisen_EP_Users as d where c.Users_Id=b.EP_LogUsersId and b.EP_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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_LogTestId=a.Test_Id and b.EP_LogEPUsersId=d.EP_UsersId 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
自定义函数如下:
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 其他的类似
数据库字段EP_LogTestTypePerson的值类似36.2|74.5|8.7|60|63.2|68.6|83.1|84|37.3|17|45.9
调用的时候提示错误是:向 substring 函数传递了无效的 length 参数。
各位给点意见阿
上面这个函数有问题
@end-@start这个你最好用个变量保存计算出来的值然后再传到函数中去
@end-@start不是变量吗?能说的清楚点吗?
我也发现SUBSTRING(@str,@start,@end-@start)出的问题 直接执行这个自定义函数的时候最后以行会出现乱码,说清方案阿。
先打出来看看啊..到底是什么时候出错了...
re:lzzyfish @start, @end-@start的值有浮点型的,有整型的
-----------------------------------------------------------
你把他写成sql语句测试不行啊..非要在函数里测啊..
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))结果
-------------
18
22
63.2