USE [IncubatorIMS_TEST]
GO
/****** 对象: StoredProcedure [dbo].[DynamicSearchResult] 脚本日期: 03/21/2011 21:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DynamicSearchResult]
@GuideTeacherType nvarchar(300),
@GuideTeacherName nvarchar(500),
@GuideTeacherCompany nvarchar(1000),
@GuideTeacherJob nvarchar(1000)
as
declare @sql nvarchar(2000)
set @sql = 'select * from BIGuideTeacher where 1=1'
if(@GuideTeacherType <> ' ')
set @sql = @sql + ' and GuideTeacherType like ' + '%' + @GuideTeacherType + '%'
if(@GuideTeacherName <> ' ')
set @sql = @sql + ' and GuideTeacherName like ' + '%' + @GuideTeacherName + '%'
if(@GuideTeacherCompany <> ' ')
set @sql = @sql + ' and GuideTeacherCompany like ' + '%' + @GuideTeacherCompany + '%'
if(@GuideTeacherJob <> ' ')
set @sql = @sql + ' and GuideTeacherJob like ' + '%' + @GuideTeacherJob + '%'
exec(@sql)
貌似没啥效果,顺便问下怎么样在SQL2005中调试存储过程啊,就是怎么样可以单步执行和打断点这种,网上说能打断点但是没找到DEBUG的按钮求帮忙~3Q~
GO
/****** 对象: StoredProcedure [dbo].[DynamicSearchResult] 脚本日期: 03/21/2011 21:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DynamicSearchResult]
@GuideTeacherType nvarchar(300),
@GuideTeacherName nvarchar(500),
@GuideTeacherCompany nvarchar(1000),
@GuideTeacherJob nvarchar(1000)
as
declare @sql nvarchar(2000)
set @sql = 'select * from BIGuideTeacher where 1=1'
if(@GuideTeacherType <> ' ')
set @sql = @sql + ' and GuideTeacherType like ' + '%' + @GuideTeacherType + '%'
if(@GuideTeacherName <> ' ')
set @sql = @sql + ' and GuideTeacherName like ' + '%' + @GuideTeacherName + '%'
if(@GuideTeacherCompany <> ' ')
set @sql = @sql + ' and GuideTeacherCompany like ' + '%' + @GuideTeacherCompany + '%'
if(@GuideTeacherJob <> ' ')
set @sql = @sql + ' and GuideTeacherJob like ' + '%' + @GuideTeacherJob + '%'
exec(@sql)
貌似没啥效果,顺便问下怎么样在SQL2005中调试存储过程啊,就是怎么样可以单步执行和打断点这种,网上说能打断点但是没找到DEBUG的按钮求帮忙~3Q~
GO
/****** 对象: StoredProcedure [dbo].[DynamicSearchResult] 脚本日期: 03/21/2011 21:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[DynamicSearchResult]
@GuideTeacherType nvarchar(300),
@GuideTeacherName nvarchar(500),
@GuideTeacherCompany nvarchar(1000),
@GuideTeacherJob nvarchar(1000)
as
declare @sql nvarchar(2000)
set @sql = 'select * from BIGuideTeacher where 1=1'
if(@GuideTeacherType <> ' ')
set @sql = @sql + ' and GuideTeacherType like ' + '%' + @GuideTeacherType + '%'
if(@GuideTeacherName <> ' ')
set @sql = @sql + ' and GuideTeacherName like ' + '%' + @GuideTeacherName + '%'
if(@GuideTeacherCompany <> ' ')
set @sql = @sql + ' and GuideTeacherCompany like ' + '%' + @GuideTeacherCompany + '%'
if(@GuideTeacherJob <> ' ')
set @sql = @sql + ' and GuideTeacherJob like ' + '%' + @GuideTeacherJob + '%'
print(@sql)--这里
--这个不用拼字符串,写那么复杂
create proc [dbo].[DynamicSearchResult]
@GuideTeacherType nvarchar(300)='',
@GuideTeacherName nvarchar(500)='',
@GuideTeacherCompany nvarchar(1000)='',
@GuideTeacherJob nvarchar(1000)=''
as
begin
select * from BIGuideTeacher
where (@GuideTeacherType='' or GuideTeacherType like '%'+@GuideTeacherType+'%')
and (@GuideTeacherName='' or GuideTeacherName like '%'+@GuideTeacherName+'%')
and (@GuideTeacherCompany='' or GuideTeacherCompany like '%'+@GuideTeacherCompany+'%')
and (@GuideTeacherJob='' or GuideTeacherJob like '%'+@GuideTeacherJob+'%')
end