动态赋值 问题 。

[zell419]

ALTER  procedure [dbo].[test]
(
	@pageIndex int,--当前第几页
	@pageSize int,--页容量
	@tid int ,
	@o varchar(50)
)
as
declare @Count int
exec('
	select * from (
		select row_number() over (order by '+@o+' ) as RowIndex,
		*
		from JG_SubjectInfo 
		where 
			('+@tid+' = -1 or [tid]='+@tid+')
		) as temp
	where rowindex>('+@pageIndex+'-1) * '+@pageIndex+' and rowIndex<='+@pageIndex+'*'+@pageSize+'
	select  '+@Count+'  = select  count(*) from JG_SubjectInfo
		where  ('+@tid+' = -1 or [tid]='+@tid+')
	return '+@Count+'
')

因为要根据不同的字段排序 所以拼接 。
我现在这样改了后 ，执行的时候 提示 '=' 附近有语法错误 。
请各位看看 ，谢谢 。

[--小F--]

print 出来看看。

[geniuswjt]

错误很严重

[-晴天]

有变量的过程中,不能直接执行,要用

sp_executesql 来执行.

把 @count 作为参数传入.

[zell419]

谢谢各位SQL版的大牛 。
不知道前台要根据不同的东西排序的 ，所以就直接用了这分页 。
看来这个不怎么好改 。

[-晴天]

try:

ALTER  procedure [dbo].[test]
(
    @pageIndex int,--当前第几页
    @pageSize int,--页容量
    @tid int ,
    @o varchar(50),
@count int output   --最好把这个当作输出变量,而不用return
)
as
begin
--declare @Count int
declare @sql nvarchar(max)
set @sql='
    select * from (
        select row_number() over (order by '+@o+' ) as RowIndex,
        *
        from JG_SubjectInfo 
        where 
            ('+@tid+' = -1 or [tid]='+ltrim(@tid)+')
        ) as temp
    where rowindex>('+ltrim(@pageIndex)+'-1) * '+ltrim(@pageIndex)+' and rowIndex<='+ltrim(@pageIndex)+'*'+ltrim(@pageSize)+'
    select  @Count  = select  count(*) from JG_SubjectInfo
        where  ('+ltrim(@tid)+' = -1 or [tid]='+ltrim(@tid)
exec SP_EXECUTESQL @str,N'@count int output ',@count output 

end

[中国风]

@Count --沒有賦值

[-晴天]

我把@sql写成@str了!

ALTER  procedure [dbo].[test]
(
    @pageIndex int,--当前第几页
    @pageSize int,--页容量
    @tid int ,
    @o varchar(50),
@count int output   --最好把这个当作输出变量,而不用return
)
as
begin
--declare @Count int
declare @sql nvarchar(max)
set @sql='
    select * from (
        select row_number() over (order by '+@o+' ) as RowIndex,
        *
        from JG_SubjectInfo 
        where 
            ('+@tid+' = -1 or [tid]='+ltrim(@tid)+')
        ) as temp
    where rowindex>('+ltrim(@pageIndex)+'-1) * '+ltrim(@pageIndex)+' and rowIndex<='+ltrim(@pageIndex)+'*'+ltrim(@pageSize)+'
    select  @Count  = select  count(*) from JG_SubjectInfo
        where  ('+ltrim(@tid)+' = -1 or [tid]='+ltrim(@tid)
exec SP_EXECUTESQL @sql,N'@count int output ',@count output 

end

[gogodiy]

动态SQL中，INT型的变量要转换。

[中国风]

這樣測測


Create  procedure [dbo].[test]
(
	@pageIndex int,--当前第几页
	@pageSize int,--页容量
	@tid int ,
	@o varchar(50)
)
as
declare @Count INT
DECLARE @sql NVARCHAR(4000)


SET @sql='	select * from (
		select row_number() over (order by '+@o+' ) as RowIndex,
		*
		from JG_SubjectInfo 
		where 
			('+rtrim(@tid)+' = -1 or [tid]='+rtrim(@tid)+')
		) as temp
	where rowindex>'+RTRIM((@pageIndex-1)* +@pageIndex)+' and rowIndex<='+rtrim(@pageIndex*@pageSize)+'
	select  @Count = count(*) from JG_SubjectInfo
		where  ('+rtrim(@tid)+' = -1 or [tid]='+rtrim(@tid)+')'
		
	EXECUTE sp_executesql @sql,N'@Count int output',@Count OUTPUT
	return @Count

[zell419]

没测 ，但是谢谢大家 。

PS ：项目要上线 烦死 。

[pengxuan]

ALTER  procedure [dbo].[test]
(
    @pageIndex int,--当前第几页
    @pageSize int,--页容量
    @tid int ,
    @o varchar(50),
    @count int output
)
as
declare @sql nvarchar(1000)
set @sql = ('
    select * from (
        select row_number() over (order by '+@o+' ) as RowIndex,
        *
        from JG_SubjectInfo 
        where 
            ('+@tid+' = -1 or [tid]='+@tid+')
        ) as temp
    where rowindex>('+@pageIndex+'-1) * '+@pageIndex+' and rowIndex<='+@pageIndex+'*'+@pageSize+'
    select  @Count = select  count(*) from JG_SubjectInfo
        where  ('+@tid+' = -1 or [tid]='+@tid+')'
exec sp_executesql @sql,N'@Count int output',@count output
go
--执行
declare @count int
exec [test] 1,30,1,'o',@count output