关于SQL存储过程必须声明表变量的问题
SQL codeALTER 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 @[email protected]+'and topic_ID='+rtrim(@myTopic) end set @[email protected]+'and CI_Charge BETWEEN '+rtrim(@myPriceL)+' AND '+rtrim(@myPriceH) if @myCountrys<>'0'[email protected]_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) [email protected]_country:end set @[email protected]+'and exists(select * from @t_country where TE_CorpInfoReal.Country_ID=m)' end if @myCategorys<>'0'[email protected]_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) [email protected]_country:end set @[email protected]+'and exists( select * from @t_Category where charindex([email protected]+' + m + [email protected]+', [email protected]+' + TE_CorpInfoReal.C_ChildID) > 0)' end if @mySerKeyword<>'' begin --set @[email protected]+'and (CI_Keywords like ''%'+ @mySerKeyword +'%'' or CI_Profile like ''%'+ @mySerKeyword +'%'' or CI_CorpName like ''%'+ @mySerKeyword +'%'')' set @[email protected]+'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 @[email protected]+'order by [email protected] end else if @myOrderType=2 begin set @[email protected]+'order by [email protected] end else if @myOrderType=3 begin set @[email protected]+'order by [email protected] end else if @myOrderType=4 begin set @[email protected]+'order by [email protected] end else begin set @[email protected]+'order by [email protected] 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<>[email protected]+') t WHERE 1=1 AND Row BETWEEN '+rtrim(@CI_StartRecordIndex)+' AND '+rtrim(@CI_EndRecordIndex) exec(@sql)END