用STORE PROCEDURE AND #TEMPTABLE也很好阿?为何一定要用UDF?????
因为我要在SQL语句中使用,比如 select dbo.funGetCountPort(entityid,'21,23,34,45') from entitytable 但procedure 不能办到!!!!! 行吗?
UP有分,UP越多分也越多,多谢了!!!
我瞎说的,千万别信!!!CREATE FUNCTION [dbo].[funGetCountPort] ( @EntityId NUMERIC(10), @IdListStr Varchar(3000) ) RETURNS Int AS BEGIN DECLARE @resultCount Int SELECT @resultCount=count(*) from NeiEntityContainmentTable WHERE entityId = @EntityId AND myentitytype in ( @IdListStr ) return @resultCount END经过我的改造,发现你好像不需要动态sql 说着玩的,请别上火。
非常感谢楼上兄, 根据你的代码我编译通过了,但运行时报错 Server: Msg 8114, Level 16, State 5, Procedure funGetCountPort, Line 10 Error converting data type varchar to numeric. 等待你的回复!
类型转换出错 估计是: AND myentitytype in ( @IdListStr ) 这句有问题
好像是类型转换时出的错:WHERE entityId = @EntityId AND myentitytype in ( @IdListStr )这句可能用问题entityId ,myentitytype 是什磨类型的?myentitytype in ( @IdListStr )这句最有可能语句写的都不对
CREATE FUNCTION [dbo].[funRecursionTypeId] ( @parentId NUMERIC(10), @IdListStr Varchar(3000) ) RETURNS Varchar(3000) AS BEGIN DECLARE @chidId NUMERIC(10), @resultListStr Varchar(3000); SET @resultListStr = rtrim(@IdListStr) + ','+ @parentId; DECLARE curs_NeiTypeContainmentTable cursor for SELECT childId FROM NeiTypeContainmentTable WHERE @parentId=21 FOR read only; OPEN curs_NeiTypeContainmentTable FETCH FIRST FROM curs_NeiTypeContainmentTable INTO @chidId WHILE (@@fetch_status=0) BEGIN SET @resultListStr = dbo.funRecursionTypeId(@chidId,@resultListStr); FETCH NEXT FROM curs_NeiTypeContainmentTable INTO @chidId END; return @resultListStr END
对不起,贴错了抱歉,真的在下面: CREATE FUNCTION [dbo].[funGetCountPort] ( @EntityId NUMERIC(10), @IdListStr Varchar(3000) ) RETURNS Int AS BEGIN DECLARE @resultCount Int set @IdListStr = rtrim(@IdListStr) + ',' SELECT @resultCount=count(*) from NeiEntityContainmentTable WHERE entityId = @EntityId AND patindex( '%'+rtrim(cast(myentitytype as char)) + ',%' ,@IdListStr) >0 return @resultCount END
各位Up也会有分的!!!!!!
select dbo.funGetCountPort(entityid,'21,23,34,45') from entitytable
但procedure 不能办到!!!!!
行吗?
(
@EntityId NUMERIC(10),
@IdListStr Varchar(3000)
)
RETURNS Int AS
BEGIN
DECLARE
@resultCount Int
SELECT @resultCount=count(*) from NeiEntityContainmentTable WHERE entityId = @EntityId AND myentitytype in ( @IdListStr )
return @resultCount
END经过我的改造,发现你好像不需要动态sql
说着玩的,请别上火。
根据你的代码我编译通过了,但运行时报错
Server: Msg 8114, Level 16, State 5, Procedure funGetCountPort, Line 10
Error converting data type varchar to numeric.
等待你的回复!
估计是:
AND myentitytype in ( @IdListStr )
这句有问题
(
@parentId NUMERIC(10),
@IdListStr Varchar(3000)
)
RETURNS Varchar(3000) AS
BEGIN
DECLARE
@chidId NUMERIC(10),
@resultListStr Varchar(3000);
SET @resultListStr = rtrim(@IdListStr) + ','+ @parentId;
DECLARE curs_NeiTypeContainmentTable cursor for
SELECT childId FROM NeiTypeContainmentTable WHERE @parentId=21
FOR read only;
OPEN curs_NeiTypeContainmentTable
FETCH FIRST FROM curs_NeiTypeContainmentTable INTO @chidId
WHILE (@@fetch_status=0)
BEGIN
SET @resultListStr = dbo.funRecursionTypeId(@chidId,@resultListStr);
FETCH NEXT FROM curs_NeiTypeContainmentTable INTO @chidId
END;
return @resultListStr
END
CREATE FUNCTION [dbo].[funGetCountPort]
(
@EntityId NUMERIC(10),
@IdListStr Varchar(3000)
)
RETURNS Int AS
BEGIN
DECLARE
@resultCount Int
set @IdListStr = rtrim(@IdListStr) + ','
SELECT @resultCount=count(*) from NeiEntityContainmentTable WHERE entityId = @EntityId
AND patindex( '%'+rtrim(cast(myentitytype as char)) + ',%' ,@IdListStr) >0
return @resultCount
END