CREATE function fGetProjectID(@classType varchar(100)) returns varchar(2000) as BEGIN Declare @strsql varchar(100) set @strsql='select projectNo from projectMaster where class in ('+@classType+') ' return @strsql --exec(@strsql) end go
那这个函数该怎么写?这个@classType变量的字符串有可能是‘a’,'b','c'
函数不能使用exec 少 begin .. end
谢谢libin_ftsafe 我想得到一个table怎么写,returns table
Alter function fGetProjectID(@classType varchar(100)) returns varchar(2000) as BEGIN return(select projectNo from projectMaster where class in (@classType) end
--返回table CREATE function fGetProjectID(@classType varchar(100)) returns @t table(col varchar(100)) as begin insert @t(col) values('1111111111') return end
7楼的方法正确但用不了,@classType的字符串有可能是"'a','b','c'"而不是'a' 拼出来的字符串应该是select * from xx where class in('a','b','c')
没有必要用函数,一个SQL语句就可以了:select projectNo from projectMaster where charindex(','+rtrim(class)+',' , ','+@classType+',')>0
有两个错误: 一是变量定义后整个函数体要用BEGIN...END包括起来, 二是在函数里面不能用EXEC来执行SQL语句,按你的意思正确的写法如下 CREATE FUNCTION fGetProjectID(@classType varchar(100)) returns varchar(2000) as BEGIN return(select projectNo from projectMaster where class in (@classType) end
CREATE function fGetProjectID(@classType varchar(100)) returns table as return(select projectNo from projectMaster where class in (@classType)) select * from dbo.fGetProjectID(@classtype)
另外as 后面缺少begin..
returns varchar(2000)
as
BEGIN
Declare @strsql varchar(100) set @strsql='select projectNo from projectMaster where class in ('+@classType+') '
return @strsql
--exec(@strsql)
end
go
少
begin
..
end
我想得到一个table怎么写,returns table
returns varchar(2000)
as
BEGIN
return(select projectNo from projectMaster where class in (@classType)
end
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition >[ WITH < function_option > [ [,] ...n ] ] [ AS ]BEGIN
function_body
RETURN
END< function_option > ::=
{ ENCRYPTION | SCHEMABINDING }< table_type_definition > ::=
( { column_definition | table_constraint } [ ,...n ] )
CREATE function fGetProjectID(@classType varchar(100))
returns @t table(col varchar(100))
as
begin
insert @t(col) values('1111111111')
return
end
拼出来的字符串应该是select * from xx where class in('a','b','c')
一是变量定义后整个函数体要用BEGIN...END包括起来,
二是在函数里面不能用EXEC来执行SQL语句,按你的意思正确的写法如下
CREATE FUNCTION fGetProjectID(@classType varchar(100))
returns varchar(2000)
as
BEGIN
return(select projectNo from projectMaster where class in (@classType)
end
returns table
as
return(select projectNo from projectMaster where class in (@classType)) select * from dbo.fGetProjectID(@classtype)