自己整了半天才弄好,写成博客纪念下:
这个是sql上写的测试用:
declare @CODE varchar(100) declare @TITLE varchar(100) declare @TABLE varchar(100) declare @type varchar(100) declare @sql varchar(max) set @sql='' SET @type='index_02' declare myCur cursor for SELECT distinct CONTENT_TYPE,CONTENT_CODE_NAME,CONTENT_TITLE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@type open myCur fetch next from myCur into @TABLE,@CODE,@TITLE while @@FETCH_STATUS <>-1 begin set @sql= @sql+'SELECT CT_LOCATOR_CONTENT.AUTO_ID AS AUTO_ID, CT_LOCATOR_CONTENT.CONTENT_SEQUENCE AS CONTENT_SEQUENCE,CT_LOCATOR_CONTENT.CONTENT_TYPE AS CONTENT_TYPE,' +@TABLE+'.'+@TITLE+' AS TITLE FROM CT_LOCATOR_CONTENT,' +@TABLE+' WHERE CT_LOCATOR_CONTENT.LOCATOR_CODE='''+@type +''' AND CT_LOCATOR_CONTENT.CONTENT_CODE='+@TABLE+'.'+@CODE set @sql=@sql+' union all ' fetch next from myCur into @TABLE,@CODE,@TITLE end if len(@sql)>9 Set @sql=SUBSTRING(@sql,0,len(@sql)-9) else SET @sql=@sql print(@sql) close myCur deallocate myCur IF EXISTS (SELECT DISTINCT CONTENT_CODE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@type) BEGIN EXEC(@sql); END ELSE BEGIN SELECT '-1' AS AUTO_ID,'-1' AS CONTENT_SEQUENCE,LOCATOR_CATEGORY AS CONTENT_TYPE,'-1' AS TITLE FROM T_SM_LOCATOR WHERE LOCATOR_CODE=@type END
具体代码中的:
/// <summary> /// 根据内容版位的Code获取版位上的内容信息(创建:001,2013.8.27) /// </summary> /// <param name="locatorCode">内容版位的CODE</param> /// <returns></returns> public DataTable GetContentTableByLocator(string locatorCode) { string sqlText = @" declare @sql varchar(max) set @sql='' declare myCur cursor for SELECT distinct CONTENT_TYPE,CONTENT_CODE_NAME,CONTENT_TITLE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE open myCur fetch next from myCur into @TABLE,@CODE,@TITLE while @@FETCH_STATUS <>-1 begin set @sql= @sql+'SELECT CT_LOCATOR_CONTENT.AUTO_ID AS AUTO_ID, CT_LOCATOR_CONTENT.CONTENT_SEQUENCE AS CONTENT_SEQUENCE,CT_LOCATOR_CONTENT.CONTENT_TYPE AS CONTENT_TYPE,' +@TABLE+'.'+@TITLE+' AS TITLE FROM CT_LOCATOR_CONTENT,' +@TABLE+' WHERE CT_LOCATOR_CONTENT.LOCATOR_CODE='''+@LOCATOR_CODE +''' AND CT_LOCATOR_CONTENT.CONTENT_CODE='+@TABLE+'.'+@CODE set @sql=@sql+' union ' fetch next from myCur into @TABLE,@CODE,@TITLE end IF len(@sql)>5 Set @sql=SUBSTRING(@sql,0,len(@sql)-5) else SET @sql=@sql close myCur deallocate myCur IF EXISTS (SELECT DISTINCT CONTENT_CODE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE) BEGIN EXEC(@sql); END ELSE BEGIN SELECT '-1' AS AUTO_ID,'-1' AS CONTENT_SEQUENCE,LOCATOR_CATEGORY AS CONTENT_TYPE,'-1' AS TITLE FROM T_SM_LOCATOR WHERE LOCATOR_CODE=@LOCATOR_CODE END "; SqlParameter[] queryParam = new SqlParameter[] { new SqlParameter("@LOCATOR_CODE", SqlDbType.NVarChar), new SqlParameter("@CODE", SqlDbType.NVarChar), new SqlParameter("@TITLE", SqlDbType.NVarChar), new SqlParameter("@TABLE", SqlDbType.NVarChar) }; queryParam[0].Value = locatorCode; queryParam[1].Value = ""; queryParam[2].Value = ""; queryParam[3].Value = ""; DataSet ds = null; using(IDbConnection conn = ConnectionConstruct.LoadInterNetConnection()) { ds = sqlServer.ExecuteDataset(conn, CommandType.Text, sqlText, queryParam); } return ds.Tables[0]; }
小测试的一并贴上(上面的是多个的,这个是单个的):
declare @temp varchar(100) declare @sql varchar(max) set @sql='' declare myCur cursor for select a from ( select 't_hdh_DY' as a union select 't_hdh_EY' as a )as tablenames open myCur FETCH NEXT FROM myCur INTO @temp WHILE @@FETCH_STATUS<>-1 BEGIN SET @sql=@sql+'SELECT ID FROM '+@temp SET @sql=@sql+' UNION ' FETCH NEXT FROM myCur INTO @temp END SET @sql=SUBSTRING(@sql,0,LEN(@sql)-5) CLOSE myCur DEALLOCATE myCur print(@sql)
ps 这个本来是查询一个的,结果有好几个,所以改了。
附上改之前的,作为备份:
string sqlText = @"SET @CODE=(SELECT TOP 1 CONTENT_CODE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE) SET @TITLE=(SELECT TOP 1 CONTENT_TITLE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE) SET @TABLE=(SELECT TOP 1 CONTENT_TYPE FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE) IF EXISTS (SELECT TOP 1 CONTENT_CODE_NAME FROM CT_LOCATOR_CONTENT WHERE LOCATOR_CODE=@LOCATOR_CODE) BEGIN EXEC('SELECT CT_LOCATOR_CONTENT.AUTO_ID AS AUTO_ID, CT_LOCATOR_CONTENT.CONTENT_SEQUENCE AS CONTENT_SEQUENCE,CT_LOCATOR_CONTENT.CONTENT_TYPE AS CONTENT_TYPE,'+@TABLE+'.'+@TITLE+' AS TITLE FROM CT_LOCATOR_CONTENT,'+@TABLE+' WHERE CT_LOCATOR_CONTENT.LOCATOR_CODE='''+@LOCATOR_CODE+''' AND CT_LOCATOR_CONTENT.CONTENT_CODE='+@TABLE+'.'+@CODE); END ELSE BEGIN SELECT '-1' AS AUTO_ID,'-1' AS CONTENT_SEQUENCE,LOCATOR_CATEGORY AS CONTENT_TYPE,'-1' AS TITLE FROM T_SM_LOCATOR WHERE LOCATOR_CODE=@LOCATOR_CODE END ";