关于SQL存储过程必须声明表变量的问题SQL codeALTER PROCEDURE [dbo].[corpListRealUser]-- Add the param
关于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...这样，相对你的方法快，而且，也没有定义域的问题了