SQL游标应用

 

自己整了半天才弄好，写成博客纪念下：

这个是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
                                ";