What happens when top in a stored procedure is followed by a variable?
Copy Code code as follows:
Create proc GetWorkPlan2
(@intCounter int
, @lngUserID int)
As
Select Top 5 lngworkid,strworkname,strexechumanname,strbegindate
From worklist where lngexechumanid= @lngUserID
ORDER BY Lngworkid Desc
Now I want to change the top 5 here to variable · Top @intCounter
As follows
Copy Code code as follows:
ALTER proc GetWorkPlan2
(@intCounter int
, @lngUserID int)
As
)
EXEC sp_executesql (' Select top ' +convert (varchar), @intCounter) + ' Lngworkid,strworkname,strexechumanname, Strbegindate from worklist where lngexechumanid= '
+convert (varchar, @lngUserID) + ' ORDER by lngworkid Desc '
Always prompt for syntax errors near the keyword ' convert '.
Ok!
So instead
Copy Code code as follows:
ALTER proc GetWorkPlan2
(@intCounter int
, @lngUserID int)
As
DECLARE @strCounter varchar (10)
Set @strCounter =convert (varchar), @intCounter)
DECLARE @strUserID varchar (10)
Set @strUserID =convert (varchar), @lngUserID)
EXEC sp_executesql (' select top ' + @strCounter + ' lngworkid,strworkname,strexechumanname,strbegindate from worklist where Lngexechumanid= '
+ @strUserID + ' ORDER by lngworkid Desc '
)
Later, after Saucer (Shi) eldest brother reminded, found that you can use the following statement to achieve (sql2005/2008):
Copy Code code as follows:
Alter proc GetWorkPlan2
(
@intCounter int
, @lngUserID int
)
As
SET ROWCOUNT @intCounter
Select Lngworkid,strworkname,strexechumanname,strbegindate
From worklist where lngexechumanid= @lngUserID
ORDER BY Lngworkid Desc
Invite the Month Note: This article copyright by invite month and the blog Garden Common All, reprint please indicate the source.
