这条存储过程怎样修改啊??高手帮忙啊~~成功立即结贴
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= '+@sort+ ' and sh_area like " '+@area+ '% " order by '+@order+ ' desc '
exec(@sql)
GO

问题在@indextable

在.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= '+@sort+ ' and sh_area like " '+@area+ '% " order by '+@order+ ' 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= ' ' '+@sort+ ' ' ' and sh_area like ' ' '+@area+ '% ' ' order by '+@order+ ' 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= '+@sort+ ' and sh_area like ' ' '+@area+ '% ' ' order by '+@order+ ' desc '
exec(@sql)
GO

[解决办法]
表变量和临时表的使用要根据实际情况。

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

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