这个函数有问题,帮忙修改一下

[clkun]

测试不通过,代码如下:

表:cls_type
id name
1 中国
2 美国
....

SELECT dbo.getnamebyid(cls_type, 1) AS Expr1


CREATE FUNCTION getnamebyid(@tablename varchar(20) , @id int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @name as varchar(50)
declare @sql as varchar(100)

set @sql = 'select @name= name from ' + @tableName + ' where id = ' + @id
exec sp_executesql @sql,@name output
RETURN(@name)
END


提示列名 cls_type 无效

如果我加一个 判断表名是否存在,这句
if exists (select * from dbo.sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
应该如何加在上面??

谢谢!

[pt1314917]

鸟儿大哥说的有道理，函数里面用不了exec...
所以换到存储过程里去吧。

create proc wsp
@tablename varchar(20),
@id int  
AS   
BEGIN 
	declare @count int
   	declare @sql  nvarchar(1000)   
	set @sql='select @num=count(1) from dbo.sysobjects where id = object_id('''+ @tablename +''')' 
	exec sp_executesql @sql,N'@num int output',@count output
	if(@count<1)
	begin
		select '您输入的表名不存在' as name
	end
	else
	begin
		print 'a'
		set @sql ='select name [name]  from  ' + @tablename  +   ' where id =  ' + cast(@id as varchar)  
  		exec(@sql) 
	end
END 

调用：exec wsp 'asdfdsa',3

[chuifengde]

函数内不能有普通存储过程

[clkun]

什么意思?能不能帮我修改一下?

[fa_ge]

CREATE FUNCTION getnamebyid(@tablename varchar(20) , @id int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @name as varchar(50)
declare @sql as varchar(100)
exec ('select * from sysobjects where xtype=''U'' and name='''+ @tablename +'''')

if @@rowcount>0
begin
set @sql = 'select @name= name from ' + @tableName + ' where id = ' + @id
exec sp_executesql @sql,@name output
RETURN(@name)
end
END

[pt1314917]

函数应该改成这样
CREATE FUNCTION getnamebyid(@tablename varchar(20) , @id int)   
RETURNS varchar(50)   
AS   
BEGIN   
   DECLARE @name1  nvarchar(50)   
   declare @sql  nvarchar(100)   
   set @sql =    'select @name=  name  from    ' + @tableName  +   ' where id =  ' + @id  
   exec sp_executesql @sql,N'@name varchar(50) output',@name1 output  
   RETURN(@name1)   
END  

[chuifengde]

create function内不能有exec,存储过程等改变数据库全局状态的语句

[clkun]

函数应该改成这样
CREATE FUNCTION getnamebyid(@tablename varchar(20) , @id int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @name1 nvarchar(50)
declare @sql nvarchar(100)
set @sql = 'select @name= name from ' + @tableName + ' where id = ' + @id
exec sp_executesql @sql,N'@name varchar(50) output',@name1 output
RETURN(@name1)
END


相同的错误信息. 列名 cls_type 无效