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

SQL 數據庫中的存儲過程的參數問題

編輯:更多數據庫知識

   1、SQL 數據庫中的存儲過程的參數問題

  怎麼將SQL數據庫中的存儲過程中的參數既作為輸出變量又作為輸出變量?

  [sql] view plaincopy --drop proc proc_test

  --go

  create proc dbo.proc_test

  @in int,

  @out int out,

  @in_out int output

  as

  select @out = @in + @in_out, --1 + 2 = 3

  @in_out = @out + 1 --3 + 1 = 4

  go

  declare @in_p int

  declare @out_p int

  declare @in_out_p int

  set @in_p = 1;

  set @in_out_p = 2

  exec dbo.proc_test @in_p,

  @out_p out,

  @in_out_p output

  select @in_p, --輸入參數

  @out_p, --輸出參數

  @in_out_p --輸入,輸出參數

  /*

  (無列名) (無列名) (無列名)

  1 3 4

  */

  2、在存儲過程中的參數問題。

  下面是問題:

  [sql] view plaincopy create table #tableTest(id int identity , name varchar(20),age int,)

  go

  insert into #tableTest

  select '小明',23 union all

  select '小紅',28 union all

  select '小軍',27

  go

  select *from #tableTest

  go

  create proc procTest

  @name varchar(20),

  @age int,

  @IDs varchar(30)

  as

  begin

  select *from #tableTest where 1=1

  end

  --當我傳入@name參數等於 小明,23歲,還有ID在(1,3)的時候

  --我怎麼可以弄成可選的參數

  --比如,name不為空時候

  select *from #tableTest where 1=1 and name like '小明'

  --如果name參數為空的時候,IDs參數不為空的時候

  select *from #tableTest where 1=1 and id in(1,3)

  --請問一下,就有參數不為空的時候存儲過程中的SQL追加條件,為空的時候就不追加,這樣帶可選參數的存儲過程怎麼寫,以及怎麼調用,請幫小弟寫一個實例

  這種問題,本質上就是根據傳入的參數不同,進行不同的查詢,也就是where 後面的查詢條件是動態的。

  一般有2中處理方式,一種就是寫動態語句,但動態語句由於是動態拼接字符串,所以比較難維護,而且如果存儲過程需要執行多次,那麼每次都需要重新編譯,但每次生成的執行計劃,應該是比較優化的。但如果拼接字符串部分,只是少量的話,還是可以用動態語句的,下面我的解法就是用動態語句來實現的,結構清晰,易於維護。

  另一種,就是通過在where語句後面寫case when來進行判斷,這種方法的好處是不用動態拼接語句,但不易於理解,也不易於修改,因為別人不一定能理解你這麼寫的意思。另一個問題就是性能的問題,因為在原來的公司就用過這種方法,一段時間後,查詢非常慢,本來幾秒就能出結果,後來幾分鐘都出不了結果。說實在的,這種方法要求較高的技巧性,也容易出錯,不建議使用。

  下面是我的解法,用了動態語句來實現,但考慮了維護、測試方面的要求:

  [sql] view plaincopy --drop table #tableTest

  create table #tableTest(id int identity , name varchar(20),age int,)

  go

  insert into #tableTest

  select '小明',23 union all

  select '小紅',28 union all

  select '小軍',27

  go

  select *from #tableTest

  go

  create proc procTest

  @name varchar(20)=null,@age int = null,@IDs varchar(30) = null

  as

  declare @sql nvarchar(max);

  set @sql = '';

  set @sql = 'select * from #tableTest where 1 = 1';

  set @sql = @sql +

  case when @name is not null

  then ' and name like ' + QUOTENAME(@name +'%','''')

  when @age is not null

  then ' and age = ' + cast(@age AS varchar)

  when @ids Is not null

  then ' and id in (' + @ids +')'

  else ' '

  end

  --打印出語句

  select @sql as '語句'

  --執行語句

  --exec(@sql)

  go

  exec procTest

  /*

  語句

  select * from #tableTest where 1 = 1

  */

  exec procTest '小明',23

  /*

  語句

  select * from #tableTest where 1 = 1 and name like '小明%'

  */

  exec procTest @ids = '2,3'

  /*

  語句

  select * from #tableTest where 1 = 1 and id in (2,3)

  */

  備注:如遇到需多個and參數連接查詢,SQL語句可寫如下

  SET @sql= @sql +

  CASE WHEN @SellerNick <> ''

  THEN ' and SellerNick = '+@SellerNick+' '

  ELSE ' '

  END

  SET @sql= @sql +

  CASE WHEN @LogisticsId <> ''

  THEN ' and LogisticsId = '+@LogisticsId+''

  ELSE ' '

  END

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