USP_SMARTFIELD_GETINFORMATIONBYID

CREATE procedure dbo.USP_SMARTFIELD_GETINFORMATIONBYID
(
    @ID as uniqueidentifier
)
as
    select
        SMARTFIELD.ID as SMARTFIELDID,
        SMARTFIELDCATALOG.DATATYPECODE,
        SMARTFIELD.NAME as SMARTFIELDNAME,
        TABLECATALOG.TABLENAME as SMARTFIELDTABLENAME,
        RECORDTYPE.BASETABLENAME as RECORDTABLENAME,
        SMARTFIELD.VALUECOLUMNNAME as VALUECOLUMNNAME,
        SMARTFIELDCATALOG.PROCEDURENAME as SMARTFIELDPROCEDURENAME,
        coalesce(SMARTFIELD.SMARTFIELDDATAFORMITEM,'') AS SMARTFIELDDATAFORMITEM,
        cast((case when 
            SMARTFIELD.USEVALUEGROUP = 0
        then 
            0 
        else 
            case when 
                exists(select top 1 ID from dbo.SMARTFIELDVALUEGROUP where SMARTFIELDVALUEGROUP.SMARTFIELDID = SMARTFIELD.ID)
            then 
                1
            else
                0
            end
        end) as bit) as HASVALUEGROUP,
        SMARTFIELD.LASTRUNON as LASTRUNON,
        SMARTFIELDCATALOG.ID as SMARTFIELDCATALOGID,
        SMARTFIELDCATALOG.FORMMETADATAXML as SMARTFIELDCATALOGFORMMETADATA,
        RECORDTYPE.ID as RECORDTYPEID,
        (select top 1 QUERYVIEWCATALOG.PRIMARYKEYTYPENAME from dbo.QUERYVIEWCATALOG where QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID and isnull(PRIMARYKEYTYPENAME,'') <> '') as PRIMARYKEYTYPENAME,
        (select [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME] from dbo.[QUERYVIEWCATALOG] where [QUERYVIEWCATALOG].[ID] = [SMARTFIELD].[SOURCEQUERYVIEWCATALOGID]) as [SOURCEPRIMARYKEYTYPENAME],
        [SMARTFIELDCATALOG].[VALUERECORDTYPEID] as [SMARTFIELDCATALOGVALUERECORDTYPEID],
    SMARTFIELDCATALOG.SMARTFIELDSPECXML,
    SMARTFIELD.CURRENCYID
    from
        dbo.SMARTFIELD
        inner join dbo.TABLECATALOG on TABLECATALOG.ID = SMARTFIELD.TABLECATALOGID
        inner join dbo.SMARTFIELDCATALOG on SMARTFIELDCATALOG.ID = SMARTFIELD.SMARTFIELDCATALOGID
        inner join dbo.RECORDTYPE on RECORDTYPE.ID = SMARTFIELDCATALOG.RECORDTYPEID
    where
        SMARTFIELD.ID = @ID
    order by
        SMARTFIELD.NAME;