-- =============================================

-- updated 8/17/2009

-- =============================================

IF EXISTS (SELECT name 

       FROM   sysobjects 

       WHERE  name = N'sp_select_byID' 

       AND       type = 'P')

    DROP PROCEDURE sp_select_byID

GO

CREATE PROCEDURE sp_select_byID 

@tablename varchar(50)

AS

set nocount on

declare @vchrFieldName varchar(500)

declare @vchrFieldType varchar(50)

declare @vchrParamName varchar(255)

declare @vchrParamList varchar(255)

declare @vchrSelectList varchar(8000)

declare @vchrProcName varchar(255)

declare @vchrOrdinalPosition int

DECLARE @vchrWhere varchar(1000)

declare @vchrAnd varchar(5)

select

    @vchrParamList = '',

    @vchrSelectList = '',

    @vchrProcName = 'FP_' + @tablename + '_SEL_byID',

    @vchrWhere = '',

    @vchrAnd = ''

print 'IF EXISTS (SELECT name '

print '    FROM   sysobjects '

print '    WHERE  name = N' + char(39) + @vchrProcName + char(39)

print '        AND type = ' + char(39) + 'P' + char(39) + ')'

print '    DROP PROCEDURE ' + @vchrProcName

print 'GO'

print ''

print 'CREATE PROCEDURE ' + @vchrProcName

declare curFields CURSOR for

select c.name,

        dbo.f_4P_columnType (xusertype,length, xprec , xscale),

        char(64) + c.name,

        k.ordinal_position

    from sysobjects o

        left join syscolumns c on c.id = o.id

        left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS con 

            on con.table_name = o.name and con.constraint_type = 'PRIMARY KEY'

        left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 

            on Con.TABLE_NAME = K.TABLE_NAME AND Con.CONSTRAINT_NAME = K.CONSTRAINT_NAME 

            and k.column_name = c.name

    where o.name = @tablename

    order by c.colorder

OPEN curFields

FETCH NEXT FROM curFields INTO @vchrFieldName, @vchrFieldType, 

    @vchrParamName, @vchrOrdinalPosition

WHILE @@FETCH_STATUS = 0

BEGIN

    if not @vchrOrdinalPosition is null begin

        --build a where clause

        set @vchrWhere = @vchrWhere  + '        ' + @vchrAnd + @vchrFieldName 

            + ' = ' + @vchrParamName + char(13)

        set @vchrAnd = 'and '

        --build a param list

        set @vchrParamList = @vchrParamList + @vchrParamName 

            + ' ' + @vchrFieldType + ',' + char(13)

    end

    --build a select list

    set @vchrSelectList = @vchrSelectList + ', ' + @vchrFieldName 

    FETCH NEXT FROM curFields INTO @vchrFieldName, @vchrFieldType, 

        @vchrParamName, @vchrOrdinalPosition

-- sp_select_byID 'sop10202'

END

CLOSE curFields

DEALLOCATE curFields

set @vchrSelectList = stuff(@vchrSelectList,1,2,'')

if len(@vchrParamList) > 0 begin

    set @vchrParamList = left(@vchrParamList,len(@vchrParamList) - 2)

end

print ''

print @vchrParamList

print ''

print 'AS'

print ''

print 'SELECT ' + @vchrSelectList

print '    FROM ' + @tablename + ' (nolock)'

PRINT '    WHERE '

print @vchrWhere

print 'GO'

print 'grant all on ' + @vchrProcName + ' to public'

print ''

print ''

go

grant all on sp_select_byID to public