how to return table on sql function

Question

I am very new to sql create function

i wanted to check data column value at table

the purpose is to return full table where data is null , <0 , >1

the problem is i am trying make data is dynamic so the table column more than 10 or more than that ( can 50 column)

my sql syntax is

CREATE FUNCTION Checkdatavalue
(
     @tables varchar(50)
)
RETURNS table
AS
BEGIN
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)  


        DECLARE oCur CURSOR FOR 
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56' 

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END 

            SET @ssql =  left (@ssql,len(@ssql)-3)   
            set @sql = 'select * from '+@tables+' where ' + @ssql 
            --select   @sql

           close ocur;
        deallocate ocur;
            return @sql 
END
GO

the @sql statement like this

 select * from tablename where  [column1] is null or
 [column1] < 0 or [column1]>1 or [column2] is null
 or [column2] < 0 or [column2]>1 or etc...

there is following error like

1. A RETURN statement with a return value cannot be used in this context

2. Incorrect syntax near 'BEGIN'.

Answer 1

The table-valued function requires static columns (names and types). If you want dynamic columns in result-set, use procedure instead of function.

E.G.

Create Procedure X(@tables varchar(50)) as
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)  


        DECLARE oCur CURSOR FOR 
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56' 

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END 

            SET @ssql =  left (@ssql,len(@ssql)-3)   
            set @sql = 'select * from '+@tables+' where ' + @ssql 
            exec(   @sql)

           close ocur;
        deallocate ocur;
GO
X 'Tree'