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

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



...全文
1120 16 打赏 收藏 举报
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
吉普赛的歌 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]


  • 打赏
  • 举报
回复
cnming 2018-09-02
引用 8 楼 zjcxc 的回复:
如果你不想用 VIEW,既然你都想用计算列了,那么直接加个真正的列,然后用触发器在数据变化的时候去自动更新这个列也成啊


View创建了,根据建模用程序创建了,但是坑的是PowerBI莫名奇妙无法添加外键,导出的外键都自动有了,所以放弃了View的做法。

添加列的话工作量巨大啊,所以想用个计算列。
  • 打赏
  • 举报
回复
吉普赛的歌 2018-09-01
这根本就实现不了, 不要强求。想现实一点的办法。

必须动态, 就表示功能复杂了, 直接用存储过程返回数据吧。


  • 打赏
  • 举报
回复
zjcxc 2018-09-01
如果你不想用 VIEW,既然你都想用计算列了,那么直接加个真正的列,然后用触发器在数据变化的时候去自动更新这个列也成啊
  • 打赏
  • 举报
回复
zjcxc 2018-09-01
建个 VIEW,把数据关联起来,查询的时候直接查询 VIEW 不就行了么?
  • 打赏
  • 举报
回复
卖水果的net 2018-09-01
引用 5 楼 cnming 的回复:
[quote=引用 2 楼 wmxcn2000 的回复:] 确实是实现不了楼主还是想想别的办法吧 比如:存储过程、语句块。
应用程序中,我都是用存储过程返回数据的,都不存在问题,但是PowerBI有点麻烦了。这个程序已经写了十年了,现在去改也好麻烦 [/quote] 可以在BI展示数据之前,执行一个存储过程,把这些数据先写到一个临时表中,再用函数直接去查询。
  • 打赏
  • 举报
回复
cnming 2018-09-01
引用 2 楼 wmxcn2000 的回复:
确实是实现不了楼主还是想想别的办法吧

比如:存储过程、语句块。


应用程序中,我都是用存储过程返回数据的,都不存在问题,但是PowerBI有点麻烦了。这个程序已经写了十年了,现在去改也好麻烦
  • 打赏
  • 举报
回复
cnming 2018-09-01
引用 1 楼 yenange 的回复:
这根本就实现不了, 不要强求。想现实一点的办法。

必须动态, 就表示功能复杂了, 直接用存储过程返回数据吧。


数据表引到PowerBI中,很多外键,没有实际数据,都引入外键的表也好麻烦,有好的处理办法没?
  • 打赏
  • 举报
回复
xbmu3 2018-09-01
这根本就实现不了, 不要强求。
  • 打赏
  • 举报
回复
卖水果的net 2018-09-01
确实是实现不了楼主还是想想别的办法吧 比如:存储过程、语句块。
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
申请成为版主
帖子事件
创建了帖子
2018-09-01 09:41
社区公告
暂无公告