这条存储过程怎样修改啊高手帮忙啊成功立即结贴解决思路

这条存储过程怎样修改啊??高手帮忙啊~~成功立即结贴
CREATE   PROCEDURE   [dbo].[storelist]

(
@sort   varchar(4),
@area   varchar(10),
@order   varchar(20)
)
as
declare   @indextable   table(sh_id   int   identity(1,1),nid   int)

set   @sql= 'insert   into   @indextable(nid)   select   sh_id   from   user_store   where   sh_sort= '[email protected]+ '   and   sh_area   like   " '[email protected]+ '% "   order   by   '[email protected]+ '   desc '
exec(@sql)
GO

[email protected]

在.net调用时提示
必须声明变量   '@indextable '。
必须声明变量   '@indextable '。  


------解决方案--------------------
CREATE PROCEDURE [dbo].[storelist]

(
@sort varchar(4),
@area varchar(10),
@order varchar(20)
)
as
create table indextable (sh_id int identity(1,1),nid int)

set @sql= 'insert into indextable(nid) select sh_id from user_store where sh_sort= '[email protected]+ ' and sh_area like " '[email protected]+ '% " order by '[email protected]+ ' desc '
exec(@sql)
GO
------解决方案--------------------
CREATE PROCEDURE [dbo].[storelist]
(
@sort varchar(4),
@area varchar(10),
@order varchar(20)
)
as
declare @sql varchar(2000)
set @sql= 'declare @indextable table(sh_id int identity(1,1),nid int)
insert into @indextable(nid)
select sh_id from user_store where sh_sort= ' ' '[email protected]+ ' ' ' and sh_area like ' ' '[email protected]+ '% ' ' order by '[email protected]+ ' desc
select * from @indextable '
exec(@sql)
GO

------解决方案--------------------
表变量应该在动态SQL 内部定义，这是变量作用域的问题。
------解决方案--------------------
CREATE PROCEDURE [dbo].[storelist] @sort varchar(4),@area varchar(10),@order varchar(20) as
declare @sql varchar(2000)
set @sql= 'declare @indextable table(sh_id int identity(1,1),nid int) insert into @indextable(nid) select sh_id from user_store where sh_sort= '[email protected]+ ' and sh_area like ' ' '[email protected]+ '% ' ' order by '[email protected]+ ' desc '
exec(@sql)
GO

------解决方案--------------------
表变量和临时表的使用要根据实际情况。

一般少量的数据用表变量较快，数据量大时用临时表。

但是一般情况下，硬件完全可以弥补这两者的差异。