如何构建一个函数，根据制定的表名、字段名、条件查询数据，并且返回这个指定的字段数据

cnming 2018-09-01 09:41:43

应用场景是这样的，数据库中有外键，外键仅存了ID，没有存名称，例如船舶ID，船舶名称，在集装箱的业务表中仅存有船舶ID，想做一个计算列，动态获取船舶名称，在Select *的时候一起查询出来。
计算列不允许执行存储过程，不允许执行Select，但是允许执行函数，因此想建立一个行数，通过指定的条件返回数据。
当然我也接受别的方法实现这个计算列。
谢谢！

目前写的函数如下，但是报错了



Create FUNCTION GetNameByForeignKey

(	

	@ForeignKey					Varchar(64),				--外键键值

	@ForeignKeyColumnName		Varchar(64),				--外键字段名

	@ForeignNameColumnName		Varchar(64),				--外键名称字段名

	@ForeignTableName			Varchar(64),				--外键涉及的表名

	@OrderByColumnNames			Varchar(128) = ''			--排序，多个字段排列用逗号分隔，组成 Order By 的规范

)

RETURNS Varchar(128) 

AS

Begin

	Declare @ResultValue		Varchar(128) = ''

	Declare @SQL				nvarchar(3250) = ''



	Set @SQL = 'Select Top(1) @ResultValue = ' + @ForeignNameColumnName + ' From ' + @ForeignTableName + ' Where ' + @ForeignKeyColumnName + ' = ''' + @ForeignKey + ''''

	if ((Not (@OrderByColumnNames is Null)) And (Len(@OrderByColumnNames) > 0))

	Begin

		Set @SQL = @SQL + ' Order By ' + @OrderByColumnNames

	End



	--Exec(@SQL)

	--创建函数的时候报错了：在函数内对带副作用的运算符 'EXECUTE STRING' 的使用无效



	--EXEC sp_executesql @SQL

	--创建函数能通过，执行时报错了：只有函数和某些扩展存储过程才能从函数内部执行



	EXEC SP_EXECUTESQL @SQL,N'@ResultValue Varchar(128)', @ResultValue OUT;

	--创建函数能通过，执行时报错了：只有函数和某些扩展存储过程才能从函数内部执行。



	RETURN @ResultValue

End

GO

...全文

1187 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

吉普赛的歌 2018-09-11

打赏
举报

不要什么 ETL , 就是用 SQL 代理，定时生成需要的数据而已。

cnming 2018-09-11

打赏
举报

引用 14 楼 yenange 的回复:

如果数据稳定比较稳定，变化不多的话，那就定时生成数据到结果表（先清空）， PowerBI 到结果表去取。

使用ETL工具？

吉普赛的歌 2018-09-11

打赏
举报

如果数据稳定比较稳定，变化不多的话，那就定时生成数据到结果表（先清空）， PowerBI 到结果表去取。

cnming 2018-09-11

打赏
举报

从数据的便捷性角度来说，如果允许获取外键指定的某个字段Select回来多好啊

ダ雨夹雪リ 2018-09-03

打赏
举报

引用 11 楼 yenange 的回复:

[quote=引用 10 楼 mxbing1984 的回复:]
其实CLR函数,还是能实现的

想法非常好。
但楼主希望的是做成计算列，你按楼主的需求试下是否可行？[/quote]

唉,没看清楚,计算列是不行的(此列不具有确定性)

吉普赛的歌 2018-09-03

打赏
举报

引用 10 楼 mxbing1984 的回复:

其实CLR函数,还是能实现的

想法非常好。
但楼主希望的是做成计算列，你按楼主的需求试下是否可行？

ダ雨夹雪リ 2018-09-03

打赏
举报

其实CLR函数,还是能实现的



using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;



public partial class UserDefinedFunctions

{

    [SqlFunction(DataAccess = DataAccessKind.Read)]

    public static SqlString GetNameByForeignKey(string ForeignKey, string ForeignKeyColumnName, string ForeignNameColumnName, string ForeignTableName, string OrderByColumnNames)

    {

        string sql = string.Format(@"Select Top(1) {0} from {1} where {2} = '{3}'", ForeignNameColumnName, ForeignTableName, ForeignKeyColumnName, ForeignKey);





        if (!string.IsNullOrEmpty(OrderByColumnNames))

        {

            sql += " Order By " + OrderByColumnNames;

        }



        using (SqlConnection cn = new SqlConnection("context connection=true"))

        {

            cn.Open();

           

            try

            {

                SqlCommand cmd = new SqlCommand(sql, cn);

                object obj= cmd.ExecuteScalar();



                return new SqlString(obj.ToString());





            }

            catch (Exception ex)

            {

                return null;

            }

        }





         

    }

}

编译以上代码,加载程序集,然后创建函数



--创建函数

CREATE FUNCTION GetNameByForeignKey(@ForeignKey NVARCHAR(50),@ForeignKeyColumnName NVARCHAR(50),@ForeignNameColumnName NVARCHAR(50)

,@ForeignTableName NVARCHAR(50),@OrderByColumnNames NVARCHAR(50))

RETURNS NVARCHAR(100)

EXTERNAL NAME database1.UserDefinedFunctions.GetNameByForeignKey



--执行查询

SELECT *,dbo.GetNameByForeignKey(id,'id','qqnum','Group704','') FROM  [dbo].[Group704]