怎样用sql server自定义函数，实现动态的sql，返回一个table类型

你的动态SQL是要通过传入的参数生成查询语句，然后由sp_executesql来执行吗?

如果是这样的话，那么自定义函数是无法实现的。因为，SQL

Server的自定义函数中不可以调用sp_executesql存储过程的。

1.create procedure deleteSeed(@seedTable nvarchar(255),@url nvarchar(255))

2.

3.as

4.declare @deleteSql nvarchar(255)

5.declare @updateSql nvarchar(255)

6.declare @selectSql nvarchar(255)

7.declare @length int

8.

9.begin

10.set @deleteSql = 'delete from ' + @seedTable + ' where url=''' + @url + ''''

11.set @updateSql = 'update ' + @seedTable + ' set length=length-1 where url=''' + @url + ''''

12. //-- set @selectSql = 'select ' + @length + '=length from ' + @seedTable + ' where url=''' + @url + ''''

13.set @selectSql = N'select @needLength = length from'+@seedTable +N' where url=''' + @url + ''''

14.

15.//--exec(@selectSql)

16.

17. exec sp_executeSql @selectSql,N'@needLength int output',@length = needLenght OUTPUT

18.

19.if @length=1 begin

20.exec(@deleteSql)

21.end

22.else begin

23.exec(@updateSql)

24.end

25.end

create procedure deleteSeed(@seedTable nvarchar(255),@url nvarchar(255))

as

declare @deleteSql nvarchar(255)

declare @updateSql nvarchar(255)

declare @selectSql nvarchar(255)

declare @length int

begin

set @deleteSql = 'delete from ' + @seedTable + ' where url=''' + @url + ''''

set @updateSql = 'update ' + @seedTable + ' set length=length-1 where url=''' + @url + ''''

//-- set @selectSql = 'select ' + @length + '=length from ' + @seedTable + ' where url=''' + @url + ''''

set @selectSql = N'select @needLength = length from'+@seedTable +N' where url=''' + @url + ''''

//--exec(@selectSql)

exec sp_executeSql @selectSql,N'@needLength int output',@length = needLenght OUTPUT

if @length=1 begin

exec(@deleteSql)

end

else begin

exec(@updateSql)

end

end

你试试，主要是execute 和sp_executeSql 你到msdn去查查了解一些不同点。

---