27,579
社区成员
发帖
与我相关
我的任务
分享
if exists(select * from sysobjects where name='fn_TaskList')
drop function fn_TaskList
go
create function fn_TaskList(
@taskSubject nvarchar(50)----任务主题
)
returns table
as
return (
SELECT [TaskInfo].[TaskID],[TaskSubject],[TaskContent],[RespEmpID] FROM [TaskInfo] where [TaskSubject] like '%'+@taskSubject+'%' or @taskSubject is null
)
go
select * from fn_TaskList( 'w' )
-----------------
----------------
if exists(select * from sysobjects where name='stro_GetTaskList')
drop procedure stro_GetTaskList
go
create procedure stro_GetTaskList
@taskSubject nvarchar(50)
as
declare @sql varchar(3000)
set @sql='select * from fn_TaskList('+@taskSubject +')'
exec(@sql)
---select * from fn_TaskList( 'w' )
go
execute stro_GetTaskList 'w'
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME ='stro_GetTaskList_temp')
DROP PROCEDURE stro_GetTaskList_temp
GO
CREATE PROCEDURE stro_GetTaskList_temp
@taskSubject NVARCHAR(50)
AS
DECLARE @sql NVARCHAR(3000);
SET @sql = N'SELECT * FROM fn_TaskList(@taskSubject)';
EXEC sp_executesql @sql,N'@taskSubject nvarchar(50)',@taskSubject = @taskSubject;
GO
EXECUTE stro_GetTaskList_temp 'w'')select * from sys.tables;--'')';
EXECUTE stro_GetTaskList 'w'')select * from sys.tables;--'')';
create table #(code varchar(20),value int)
declare @sql varchar(200)
set @sql='insert into # select ''AAA'',10'
-- AAA之前的一对单引号与之后的一对单引号分别代表一个单引号
print @sql --查看这个字符串的实际内容
/*
insert into # select 'AAA',10
*/
exec(@sql)
select * from #
/*
code value
-------------------- -----------
AAA 10
*/
--如果需要insert一个值为'A''AA的字符串,如下:
set @sql='insert into # select ''A''''AA'',10'
--两层嵌套的字符串内部,一个单引号需要经过两次转义,于是变成了4个单引号
print @sql --查看这个字符串的实际内容
/*
insert into # select 'A''AA',10
*/
exec(@sql)
select * from #
/*
code value
-------------------- -----------
AAA 10
A'AA 10
*/
drop table #
if exists(select * from sysobjects where name='stro_GetTaskList')
drop procedure stro_GetTaskList
go
create procedure stro_GetTaskList
@taskSubject nvarchar(50)
as
declare @sql varchar(3000)
set @sql='select * from fn_TaskList('''+@taskSubject +''')'
exec(@sql)
---select * from fn_TaskList( 'w' )
go
的确是串内少了引号.if exists(select * from sysobjects where name='stro_GetTaskList')
drop procedure stro_GetTaskList
go
create procedure stro_GetTaskList
@taskSubject nvarchar(50)
as
declare @sql varchar(3000)
set @sql='select * from dbo.fn_TaskList('+@taskSubject +')'
exec(@sql)
---select * from fn_TaskList( 'w' )
go
if exists(select * from sysobjects where name='fn_TaskList')
drop function fn_TaskList
go
create function fn_TaskList(
@taskSubject nvarchar(50)----任务主题
)
returns table
as
return (
SELECT [TaskInfo].[TaskID],[TaskSubject],[TaskContent],[RespEmpID] FROM [TaskInfo] where [TaskSubject] like '%'+@taskSubject+'%' or @taskSubject is null
)
go
select * from fn_TaskList( 'w' )
-----------------
----------------
if exists(select * from sysobjects where name='stro_GetTaskList')
drop procedure stro_GetTaskList
go
create procedure stro_GetTaskList
@taskSubject nvarchar(50)
as
declare @sql varchar(3000)
set @sql='select * from fn_TaskList('''+@taskSubject +''')'
exec(@sql)
---select * from fn_TaskList( 'w' )
go
execute stro_GetTaskList 'w'