CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresinline-functions
Arpan Ghimire
Arpan Ghimire

Reputation: 95

Converting Stored Procedure to Inline Function

I need to perform update but with stored procedure I can't use It.So I m no choice then converting to function. This is my Stored procedure.

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

I try to convert it to inline function

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

It throws me this error

Must declare the table variable "@tablename

Help me convert scored procedure to inline function. Edited:- If it is not possible to convert into inline function. Any alternative way How can I use result of this stored procedure into update statement.

Upvotes: 2

Views: 1365

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

try This

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')

Upvotes: 0

Related Questions