今天一下午我就用來寫這個存儲過程了。遇到了好幾個問題,現在解決了,就曬一曬,看看各位大牛有沒有啥更好的辦法,算是拋磚引玉吧。。。
這個存儲過程是我用來搜索擁有某種技能的用戶的
存儲過程 create PROCEDURE [dbo].[selectuserbypageandsearch] @categoryid int, @sex int, @skillid int, @ishaved int, @pageindex int, @pagesize int AS DECLARE @strSQL varchar(5000) declare @whereSQL varchar(1000) set @whereSQL='where 1=1 and dbo.Users.isskillopened=1' if @ishaved>0 set @whereSQL=@whereSQL+'and dbo.Myskills.ishaved='+ltrim(STR(@ishaved))+'' if @sex>=0 set @whereSQL =@whereSQL +' and dbo.Users.sex='+STR(@sex)+'' if @skillid>0 set @whereSQL =@whereSQL +' and dbo.Myskills.sid='+STR(@skillid)+'' if @skillid<=0 and @categoryid>=0 set @whereSQL =@whereSQL +' and dbo.Skills.categoryid='+STR(@categoryid)+'' IF @pageindex = 1 BEGIN SET @strSQL ='SELECT DISTINCT TOP '+ STR(@pagesize) + ' dbo.Users.id, dbo.Users.name, dbo.Users.nickname,dbo.Users.xuehao FROM dbo.Myskills INNER JOIN dbo.Skills ON dbo.Myskills.sid = dbo.Skills.id INNER JOIN dbo.Users ON dbo.Myskills.uid = dbo.Users.id '+ STR(@whereSQL) + '' end ELSE BEGIN SET @strSQL ='SELECT DISTINCT TOP '+ STR(@pagesize) + ' dbo.Users.id, dbo.Users.name, dbo.Users.nickname,dbo.Users.xuehao FROM dbo.Myskills INNER JOIN dbo.Skills ON dbo.Myskills.sid = dbo.Skills.id INNER JOIN dbo.Users ON dbo.Myskills.uid = dbo.Users.id '+ STR(@whereSQL) + ' and dbo.Users.id >(SELECT ISNULL(MAX([id]),0) FROM (SELECT TOP '+STR((@pageindex-1)*@pagesize)+' id FROM [User] '+ STR(@whereSQL) + ' ORDER BY id) as A) ORDER by dbo.Users.id desc ' end select @strSQL EXEC(@strSQL) GO
寫好之後,把它執行一下。。。。
我使用的是下面的代碼
exec selectuserbypageandsearch 1,-1,0,0,1,10
果然有問題。。。。水平太菜。。。沒辦法。。錯誤顯示的是
消息 8114,級別 16,狀態 5,過程 selectuserbypageandsearch,第 27 行
從數據類型 varchar 轉換為 float 時出錯。
好吧,我見過這個錯誤,那就修改它。。。(雖然我不知道為什麼要這麼改。。。)
把
alter PROCEDURE [dbo].[selectuserbypageandsearch] @categoryid int, @sex int, @skillid int, @ishaved int, @pageindex int, @pagesize int AS DECLARE @strSQL varchar(5000) declare @whereSQL varchar(1000) set @whereSQL='where dbo.Users.isskillopened=1' if @ishaved>0 set @whereSQL=@whereSQL+'and dbo.Myskills.ishaved='+ltrim(STR(@ishaved))+'' if @sex>=0 set @whereSQL =@whereSQL +' and dbo.Users.sex='+STR(@sex)+'' if @skillid>0 set @whereSQL =@whereSQL +' and dbo.Myskills.sid='+STR(@skillid)+'' if @skillid<=0 and @categoryid>=0 set @whereSQL =@whereSQL +' and dbo.Skills.categoryid='+STR(@categoryid)+'' IF @pageindex = 1 BEGIN SET @strSQL ='SELECT DISTINCT TOP '+ STR(@pagesize) + ' dbo.Users.id, dbo.Users.name, dbo.Users.nickname,dbo.Users.xuehao FROM dbo.Myskills INNER JOIN dbo.Skills ON dbo.Myskills.sid = dbo.Skills.id INNER JOIN dbo.Users ON dbo.Myskills.uid = dbo.Users.id '+ Convert(varchar,@whereSQL) + ''--有@wheresql的都修改一下 end ELSE BEGIN SET @strSQL ='SELECT DISTINCT TOP '+ STR(@pagesize) + ' dbo.Users.id, dbo.Users.name, dbo.Users.nickname,dbo.Users.xuehao FROM dbo.Myskills INNER JOIN dbo.Skills ON dbo.Myskills.sid = dbo.Skills.id INNER JOIN dbo.Users ON dbo.Myskills.uid = dbo.Users.id '+ Convert(varchar,@whereSQL) + ' and dbo.Users.id >(SELECT ISNULL(MAX([id]),0) FROM (SELECT TOP '+STR((@pageindex-1)*@pagesize)+' id FROM [User] '+ Convert(varchar,@whereSQL) + ' ORDER BY id) as A) ORDER by dbo.Users.id desc ' end select @strSQL EXEC(@strSQL) GO