create
procedure
[
dbo
]
.
[
Get_Twzzl
]
(
@startIndex
int
,
@endIndex
int
,
@docount
bit
,
@w_jgid
char
(
50
),
@lbbh
char
(
50
),
@w_wzbm
char
(
50
),
@w_wzmc
char
(
50
),
@w_ggxh
char
(
50
))
as
set
nocount
on
set
@lbbh
=
ltrim
(
rtrim
(
@lbbh
))
set
@w_wzbm
=
ltrim
(
rtrim
(
@w_wzbm
))
set
@w_wzmc
=
ltrim
(
rtrim
(
@w_wzmc
))
set
@w_ggxh
=
ltrim
(
rtrim
(
@w_ggxh
))
declare
@p
as
varchar
(
500
)
set
@p
=
''
set
@p
=
@p
+
'
w_jgid=
'''
+
@w_jgid
+
'''
'
if
(
@lbbh
<>
''
)
begin
set
@p
=
@p
+
'
and left(w_wzbm,len(
'''
+
@lbbh
+
'''
))=
'''
+
@lbbh
+
'''
'
end
if
(
@w_wzbm
<>
''
)
begin
set
@p
=
@p
+
'
and w_wzbm=
'''
+
@w_wzbm
+
'''
'
end
if
(
@w_wzmc
<>
''
)
begin
set
@p
=
@p
+
'
and w_wzmc=
'''
+
@w_wzmc
+
'''
'
end
if
(
@w_ggxh
<>
''
)
begin
set
@p
=
@p
+
'
and w_ggxh=
'''
+
@w_ggxh
+
'''
'
end
declare
@sqlstr
as
varchar
(
8000
)
declare
@sqlstr1
as
varchar
(
8000
)
declare
@sqlstr2
as
varchar
(
8000
)
set
@sqlstr
=
'
select count(*) AS Counts from Twzzl where
'
+
@p
+
''
if
(
@docount
=
1
)
exec
(
@sqlstr
)
else
begin
create
table
#indextable (w_autoid
int
identity
(
1
,
1
),nid
int
)
set
rowcount
#indextable
set
sqlstr1
=
'
insert into #endIndex(nid) select w_autoid from Twzzl where
'
+
@p
+
'
order by w_autoid desc;
'
set
sqlstr2
=
'
select O.w_autoid,O.w_wzbm,O.w_wzmc,O.w_ggxh,O.w_jldw,O.w_disable,O.w_jgid,O.w_xhbh,rtrim(ltrim(str(O.w_jhprice,20,2))) as w_jhprice from Twzzl O,#indextable t where
'
+
@p
+
'
and O.w_autoid=t.nid and t.w_autoid between
'
+
ltrim
(
@startIndex
)
+
'
and
'
+
ltrim
(
@endIndex
)
+
'
order by t.w_autoid desc
'
exec
(
@sqlstr1
+
@sqlstr2
)
end
set
nocount
off
go
转载于:https://www.cnblogs.com/Mr0909/archive/2011/07/06/2099639.html
