将存储过程转换为内联函数

Question

我需要执行更新，但使用存储过程我无法使用 It.So 我别无选择，然后转换为函数。这是我的存储过程。

alter Proc spIsUnique
@columnname nvarchar(max),
@tablename nvarchar(max)
As
Begin
EXEC ('select IIf (count(*)>1,''False'',''True'') as [IsUnique-check] 
        from '+@tablename+' 
        group by '+@columnname)
End

我尝试将其转换为内联函数

create Function fn_IsUnique(
@columnname nvarchar(max),
@tablename nvarchar(max)
) returns table
As
return
select IIf (count(*)>1,'False','True') as [IsUnique-check] 
        from @tablename
        group by @columnname

它抛给我这个错误

Must declare the table variable "@tablename

帮助我将评分过程转换为内联函数。编辑：-如果无法转换为内联函数。任何替代方法 如何将此存储过程的结果用于更新语句。

Answer 1

试试这个

CREATE FUNCTION dbo.FnRetCnt
(
    @tablename nvarchar(max)
)
RETURNS BIT
AS
BEGIN
    DECLARE @Ret BIT
    DECLARE @t TABLE
    (
       Cnt INT
    )
    INSERT INTO @t
    SELECT
       SUM(st.row_count)
       FROM
         sys.dm_db_partition_stats st
       WHERE
          object_id = OBJECT_ID(@tablename)
    IF EXISTS(SELECT 1 FROM @t WHERE Cnt>0)
       SET @Ret = 0
    ELSE
       SET @Ret = 1
    RETURN @Ret
END
go
SELECT
    dbo.FnRetCnt('AddressType')