sql存儲過程帶in條件的解決方法
問題:
if (object_id('procedure_table', 'P') is not null)
drop proc procedure_table
go
create proc procedure_table(@isdelete int, @id varchar(20))
as
select * from tableName where isdelete = @isdelete and id in (@id);
go
上存儲過程在執行後沒有查到任何東西,原因是存儲過程将@id當作一個參數,而不是字元串拼接,是以如果id有幾個的話,那麼後面的語句就是id in ('1,2,3') ,故查不到資料。
解決方法:(拼接Sql語句)
if (object_id('procedure_table', 'P') is not null)
drop proc procedure_table
go
create proc procedure_table(@isdelete int, @id varchar(20))
as
exec ('select * from tableName where isdelete = ' + @isdelete + ' and id in ( '+ @id + ' )')
go
exec procedure_table 0,'1,2,3'
--------------------------更多存儲過程知識---------------------------------
Ø 使用者自定義存儲過程
1、 建立文法
create proc | procedure pro_name
[{@參數資料類型} [=預設值] [output],
{@參數資料類型} [=預設值] [output],
....
]
as
SQL_statements
2、 建立不帶參數存儲過程
--建立存儲過程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--調用、執行存儲過程
exec proc_get_student;
3、 修改存儲過程
--修改存儲過程
alter proc proc_get_student
as
select * from student;
4、 帶參存儲過程
--帶參存儲過程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
5、 帶通配符參數存儲過程
--帶通配符參數存儲過程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
6、 帶輸出參數存儲過程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --預設輸入參數
@name varchar(20) out, --輸出參數
@age varchar(20) output--輸入輸出參數
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
7、 不緩存存儲過程
--WITH RECOMPILE 不緩存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;
8、 加密存儲過程
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
蕃薯耀 2013年11月14日 15:32:03 星期四