程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 關於存儲過程的一個小問題

關於存儲過程的一個小問題

編輯:關於SqlServer

今天一下午我就用來寫這個存儲過程了。遇到了好幾個問題,現在解決了,就曬一曬,看看各位大牛有沒有啥更好的辦法,算是拋磚引玉吧。。。

這個存儲過程是我用來搜索擁有某種技能的用戶的

存儲過程
    
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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved