?
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
上存储过程在执行后没有查到任何东西，[email protected]，而不是字符串拼接，所以如果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'
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
上存储过程在执行后没有查到任何东西，[email protected]，而不是字符串拼接，所以如果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 [[email protected]} [=默认值] [output], [email protected]} [=默认值] [output], .... ]as SQL_statements
?
?? 2、 创建不带参数存储过程
--创建存储过程if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_studentgocreate proc proc_get_studentas select * from student;--调用、执行存储过程exec proc_get_student;
?? 3、 修改存储过程
--修改存储过程alter proc proc_get_studentasselect * from student;
?? 4、 带参存储过程
--带参存储过程if (object_id('proc_find_stu', 'P') is not null) drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;
?? 5、 带通配符参数存储过程
--带通配符参数存储过程if (object_id('proc_findStudentByName', 'P') is not null) drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';
?? 6、 带输出参数存储过程
if (object_id('proc_getStudentRecord', 'P') is not null) drop proc proc_getStudentRecordgocreate 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_tempgocreate proc proc_tempwith recompileas select * from student;goexec proc_temp;
?? 8、 加密存储过程
--加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null) drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas select * from student;goexec proc_temp_encryption;exec sp_helptext 'proc_temp';exec sp_helptext 'proc_temp_encryption';>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>蕃薯耀 2013年11月14日 15:32:03 星期四
?
?
?
?
?
?