SqlServer參數化查詢之where in和like完成詳解。本站提示廣大學習愛好者：（SqlServer參數化查詢之where in和like完成詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是SqlServer參數化查詢之where in和like完成詳解正文
身為一位小小的法式猿，在平常開辟中弗成以免的要和where in和like打交道，在年夜多半情形下我們傳的參數不多簡略做下單引號、敏感字符本義以後就直接拼進了SQL，履行查詢，弄定。如有一天你弗成防止的須要進步SQL的查詢機能，須要一次性where in 幾百、上千、乃至上萬條數據時，參數化查詢將是必定停止的選擇。但是若何完成where in和like的參數化查詢，是個讓很多人頭疼的成績。
where in 的參數化查詢完成
起首說一下我們經常使用的方法，直接拼SQL完成，普通情形下都能知足須要
string userIds = "1,2,3,4";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = string.Format("select * from Users(nolock) where UserID in({0})", userIds);
comm.ExecuteNonQuery();
}
須要參數化查詢時停止的測驗考試，很明顯以下如許履行SQL會報錯毛病
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserID in(@UserID)";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
很明顯如許會報毛病：在將 varchar 值 '1,2,3,4' 轉換成數據類型 int 時掉敗，由於參數類型為字符串，where in時會把@UserID當作一個字符串來處置，相當於現實履行了以下語句
select * from Users(nolock) where UserID in('1,2,3,4')
若履行的語句為字符串類型的，SQL履行不會報錯，固然也不會查詢出任何成果
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from Users(nolock) where UserName in(@UserName)";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, -1) { Value = "'john','dudu','rabbit'" });
comm.ExecuteNonQuery();
}
如許不會抱任何毛病，也查不出想要的成果，由於這個@UserName被當作一個字符串來處置，現實相當於履行以下語句
select * from Users(nolock) where UserName in('''john'',''dudu'',''rabbit''')
由此信任年夜家關於為什麼簡略的where in 傳參沒法獲得准確的成果曉得為何了吧，上面我們來看一看若何完成准確的參數化履行where in，為了真正完成參數化where in 傳參，許多淫才想到了各類替換計劃
計劃1，應用CHARINDEX或like 辦法完成參數化查詢，毫無疑問，這類辦法勝利了，並且勝利的復用了查詢籌劃，但同時也完全的讓查詢索引掉效(在此不商量索引話題)，形成的效果是全表掃描，假如內外數據量很年夜，百萬級、萬萬級乃至更多，如許的寫法將形成災害性效果；假如數據量比擬小、只想借助參數化完成避免SQL注入的話如許寫也無可厚非，照樣得看詳細需求。(不推舉)
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//應用CHARINDEX，完成參數化查詢，可以復用查詢籌劃，同時會使索引掉效
comm.CommandText = "select * from Users(nolock) where CHARINDEX(','+ltrim(str(UserID))+',',','+@UserID+',')>0";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//應用like，完成參數化查詢，可以復用查詢籌劃，同時會使索引掉效
comm.CommandText = "select * from Users(nolock) where ','+@UserID+',' like '%,'+ltrim(str(UserID))+',%' ";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
計劃2 應用exec靜態履行SQL，如許的寫法毫無疑問是很勝利的，並且代碼也比擬優雅，也起到了避免SQL注入的感化，看上去很完善，不外這類寫法和直接拼SQL履行沒啥本質性的差別，查詢籌劃沒有獲得復用，關於機能晉升沒任何贊助，很有種脫了褲子放屁的感到，但也不掉為一種處理計劃。(不推舉)
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//應用exec靜態履行SQL
//現實履行的查詢籌劃為(@UserID varchar(max))select * from Users(nolock) where UserID in (1,2,3,4)
//不是預期的(@UserID varchar(max))exec('select * from Users(nolock) where UserID in ('+@UserID+')')
comm.CommandText = "exec('select * from Users(nolock) where UserID in ('+@UserID+')')";
comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
計劃3 為where in的每個參數生成一個參數，寫法上比擬費事些，傳輸的參數個數無限制，最多2100個，可以依據須要應用此計劃(推舉)
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//為每條數據添加一個參數
comm.CommandText = "select * from Users(nolock) where UserID in (@UserID1,@UserId2,@UserID3,@UserID4)";
comm.Parameters.AddRange(
new SqlParameter[]{
new SqlParameter("@UserID1", SqlDbType.Int) { Value = 1},
new SqlParameter("@UserID2", SqlDbType.Int) { Value = 2},
new SqlParameter("@UserID3", SqlDbType.Int) { Value = 3},
new SqlParameter("@UserID4", SqlDbType.Int) { Value = 4}
});
comm.ExecuteNonQuery();
}
計劃4 應用暫時表完成(也能夠應用表變量機能上能夠會加倍好些)，寫法完成上比擬繁瑣些，可以依據須要寫個通用的where in暫時表查詢的辦法，以供不時之需，小我比擬推重這類寫法，可以或許使查詢籌劃獲得復用並且對索引也能有用的應用，不外因為須要創立暫時表，會帶來額定的IO開支，若查詢頻率很高，每次的數據不多時照樣建議應用計劃3，若查詢數據條數較多，特別是上千條乃至上萬條時，激烈建議應用此計劃，可以帶來偉大的機能晉升(激烈推舉)
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
string sql = @"
declare @Temp_Variable varchar(max)
create table #Temp_Table(Item varchar(max))
while(LEN(@Temp_Array) > 0)
begin
if(CHARINDEX(',',@Temp_Array) = 0)
begin
set @Temp_Variable = @Temp_Array
set @Temp_Array = ''
end
else
begin
set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)
set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)
end
insert into #Temp_Table(Item) values(@Temp_Variable)
end
select * from Users(nolock) where exists(select 1 from #Temp_Table(nolock) where #Temp_Table.Item=Users.UserID)
drop table #Temp_Table";
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@Temp_Array", SqlDbType.VarChar, -1) { Value = "1,2,3,4" });
comm.ExecuteNonQuery();
}
like參數化查詢
like查詢依據小我習氣將通配符寫到參數值中或在SQL拼接都可，兩種辦法履行後果一樣，在此不在胪陳
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//將 % 寫到參數值中
comm.CommandText = "select * from Users(nolock) where UserName like @UserName";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit%" });
comm.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
//SQL中拼接 %
comm.CommandText = "select * from Users(nolock) where UserName like @UserName+'%'";
comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar, 200) { Value = "rabbit%" });
comm.ExecuteNonQuery();
}
看到Tom.湯和蚊子額的評論 彌補了下xml傳參和tvp傳參，並對6種計劃做了個簡略總結
Sql Server參數化查詢之where in和like完成之xml和DataTable傳參
此文章屬懶散的肥兔原創
