Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
A table valued function in SQL Server can not have a variable number of columns and you can not execute dynamic SQL in a function. –  Mikael Eriksson Feb 7 '13 at 7:45
add comment

1 Answer 1

up vote 1 down vote accepted

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'        
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.