关于SQL存储过程必须声明表变量的有关问题

关于SQL存储过程必须声明表变量的问题

SQL code

ALTER PROCEDURE [dbo].[corpListRealUser]    -- Add the parameters for the stored procedure here    @myCountrys varchar(500),    @myCategorys varchar(500),    @myTopic decimal,    @myPriceL int,    @myPriceH int,    @myOrderType int,    @myOrderFlag int,    @mySerKeyword varchar(500),    @CI_StartRecordIndex int,    @CI_EndRecordIndex intASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for procedure here    declare @str varchar(1000),@strOrder varchar(200)    set @str=''        declare @split varchar(1)    set @split=','    if @myTopic>0        begin        set @str=@str+'and topic_ID='+rtrim(@myTopic)        end    set @str=@str+'and CI_Charge BETWEEN '+rtrim(@myPriceL)+' AND '+rtrim(@myPriceH)    if @myCountrys<>'0'--把国家参数拆分存为@t_country:start        begin        declare @t_country table(m varchar(200))            while charindex(rtrim(@split),@myCountrys)<>0            begin            set @myCountrys=ltrim(rtrim(@myCountrys))            if(substring(@myCountrys,1,1)<>@split)              begin              insert into @t_country (m) values(substring(@myCountrys,1,charindex(@split,@myCountrys)-1))              set @myCountrys =stuff(@myCountrys,1,charindex(@split,@myCountrys),'')              end            else              begin              set @myCountrys=stuff(@myCountrys,1,1,'')              end            end            if rtrim(@myCountrys) <>''            insert into @t_country (m) values (@myCountrys) --把国家参数拆分存为@t_country:end                set @str=@str+'and exists(select * from @t_country where TE_CorpInfoReal.Country_ID=m)'        end        if @myCategorys<>'0'--把类别参数拆分存为@t_country:start        begin        declare @t_Category table(n varchar(200))            while charindex(rtrim(@split),@myCategorys)<>0            begin            set @myCategorys=ltrim(rtrim(@myCategorys))            if(substring(@myCategorys,1,1)<>@split)              begin              insert into @t_Category (n) values(substring(@myCategorys,1,charindex(@split,@myCategorys)-1))              set @myCategorys =stuff(@myCategorys,1,charindex(@split,@myCategorys),'')              end            else              begin              set @myCategorys=stuff(@myCategorys,1,1,'')              end            end            if rtrim(@myCategorys) <>''            insert into @t_Category (n) values (@myCategorys) --把类别参数拆分存为@t_country:end                set @str=@str+'and exists(        select * from @t_Category        where charindex('+@split+' + m + '+@split+', '+@split+' + TE_CorpInfoReal.C_ChildID) > 0)'        end        if @mySerKeyword<>''        begin        --set @str=@str+'and (CI_Keywords like ''%'+ @mySerKeyword +'%'' or CI_Profile like ''%'+ @mySerKeyword +'%'' or CI_CorpName like ''%'+ @mySerKeyword +'%'')'        set @str=@str+'and (CI_Keywords like ''%'+ @mySerKeyword +'%'')'        end    set @strOrder=''    declare @orderPX varchar(10)    if @myOrderFlag=1        set @orderPX=' asc'    else        set @orderPX=' desc'    if @myOrderType=1        begin                        set @strOrder=@strOrder+'order by CI_ModifyTime'+@orderPX            end    else if @myOrderType=2        begin                        set @strOrder=@strOrder+'order by CI_Charge'+@orderPX            end    else if @myOrderType=3        begin                        set @strOrder=@strOrder+'order by CI_ClickTimes'+@orderPX            end    else if @myOrderType=4        begin                        set @strOrder=@strOrder+'order by CI_BuyTimes'+@orderPX            end    else        begin                        set @strOrder=@strOrder+'order by CI_ModifyTime'+@orderPX            end        declare @sql nvarchar(500)    set @sql = 'select * from     (select top 600 row_number() over ('+rtrim(@strOrder)+')as Row,    CI_ID,CI_CorpName,CI_Profile,CI_StateFlag,CI_Charge,CI_ModifyTime,CI_ClickTimes,CI_BuyTimes,Country_ID from TE_CorpInfoReal where CI_StateFlag<>0 and CI_StateFlag<>4 and CI_StateFlag<>5'+@str+') t           WHERE 1=1 AND Row BETWEEN '+rtrim(@CI_StartRecordIndex)+' AND '+rtrim(@CI_EndRecordIndex)    exec(@sql)END 
 

消息 1087，级别 15，状态 2，第 1 行
必须声明表变量 "@t_country"。
消息 1087，级别 15，状态 2，第 2 行
必须声明表变量 "@t_Category"。

好像是作用域的关系，但是不是很懂SQL，我应该怎么改？...高手不吝告之...急急急。。。

[解决办法]
if @myCategorys<>'0'
--把类别参数拆分存为@t_country:start
begin
declare @t_Category table(n varchar(200)) 

if @myCountrys<>'0'
--把国家参数拆分存为@t_country:start
begin
declare @t_country table(m varchar(200)) 

你的变量是在IF块定义的，如果IF没执行的话，变量就没被定义了，你看看是不是这个原因
[解决办法]
建议拆分成or语句，这样相对速度也会快，你可以选择在传值时候就写好语句，或者到sql里面拆分，打个比方说，直接传入参数：countryid＝1 or country＝2 or...这样，相对你的方法快，而且，也没有定义域的问题了

查看更多 下一篇