创建函数以返回SQL Server 2008中的表的结果
我有一种方法,它可以很好地独立工作,我希望变成一个函数或SP。我已经通过大量在线问题和答案进行了搜索,但没有人给我足够的信息来找出错误。创建函数以返回SQL Server 2008中的表的结果
表本身并不需要是一个变量,因为它总是相同的,但查找类型(@FindTypeID),数量(@qtyfld)和权重(@wtfld)字段可以不同。
CREATE FUNCTION ReturnFindsWithNotes
(@table nvarchar(50), @FindTypeID int, @qtyfld nvarchar(50), @wtfld nvarchar(50))
RETURNS @query TABLE
(
[SiteCodeID] int NULL,
[TrenchID] int NULL,
[Context] [nvarchar](20),
[FindsID] int NULL,
[BagNo] [nvarchar](20),
[FindTypeID] [int] NULL,
[FindQty] [int] NULL,
[FindWeight] [float] NULL,
[FindNotes] [nvarchar](500) NULL
)
AS
BEGIN
INSERT @query
SELECT
'SELECT * INTO #temp1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, ' + @FindTypeID + ' As FindTypeID, ' + @qtyfld + ' AS FindQty, ' + @wtfld + ' AS FindWeight, Notes, NotesToType
,LTRIM(RTRIM((b.splitdata))) AS FindNotes
FROM ' + @table +
' CROSS APPLY dbo.fnSplitString(FindsNWW.Notes,''^'') AS b
) AS k
SELECT * INTO #temp2
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, ' + @FindTypeID + ' As FindTypeID, ' + @qtyfld + ' AS FindQty, ' + @wtfld + ' AS FindWeight, Notes, NotesToType
,LTRIM(RTRIM((c.splitdata))) As FindTypeIDFromNotes
FROM ' + @table +
' CROSS APPLY dbo.fnSplitString(FindsNWW.NotesToType,''^'') AS c
) AS l
INSERT INTO @query ([SiteCodeID], [TrenchID], [Context], [FindsID], [BagNo], [FindTypeID], [FindQty], [FindWeight], [FindNotes])
SELECT #temp1.SiteCodeID, #temp1.TrenchID, #temp1.Context, #temp1.FindsID, #temp1.BagNo, #temp1.FindTypeID, #temp1.FindQty, #temp1.FindWeight, FindNotes
FROM #temp1 JOIN #temp2 on #temp1.ID = #temp2.ID WHERE FindTypeIDFromNotes = ' + @FindTypeID + '
DROP TABLE #temp1
DROP TABLE #temp2'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table
RETURN
END
以上是我得到了,但在执行创建函数时,我得到的错误:
Msg 213, Level 16, State 1, Procedure ReturnFindsWithNotes, Line 16
Column name or number of supplied values does not match table definition.
而且我将承担以下检索数据。
SELECT *
FROM ReturnFindsWithNotes ('FindsNWW', 12, 'PotWt', 'PotWt')
非常感谢@Spock。随着一些额外的东西我留在外面,它现在工作。以下是我的工作。
CREATE PROCEDURE ReturnFindsWithNotes
@table nvarchar(50),
@FindTypeID int,
@qtyfld nvarchar(50),
@wtfld nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
CREATE TABLE #temp1(
[SiteCodeID] int,
[TrenchID] int,
[Context] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FindsID] int,
[BagNo] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FindTypeID] [int] NULL,
[FindQty] [int] NULL,
[FindWeight] [float] NULL,
[FindNotes] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
select * into #temp2
from
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, 1 As FindTypeID, OtherQty, OtherWt, Notes, NotesToType
,ltrim(rtrim((b.splitdata))) AS Notes1
FROM FindsNWW
CROSS APPLY dbo.fnSplitString(FindsNWW.Notes,''^'') AS b
) AS k
select * into #temp3
from
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, 1 As FindTypeID, OtherQty, OtherWt, Notes, NotesToType
,ltrim(rtrim((c.splitdata))) As Notes2
FROM FindsNWW
CROSS APPLY dbo.fnSplitString(FindsNWW.NotesToType,''^'') AS c
) AS l
INSERT INTO [#temp1] ([SiteCodeID], [TrenchID], [Context], [FindsID], [BagNo], [FindTypeID], [FindQty], [FindWeight], [FindNotes])
select #temp2.SiteCodeID, #temp2.TrenchID, #temp2.Context, #temp2.FindsID, #temp2.BagNo, #temp2.FindTypeID,#temp2.OtherQty, #temp2.OtherWt, Notes1
from #temp2 join #temp3 on #temp2.ID = #temp3.ID Where Notes2 = 1
drop table #temp2
drop table #temp3
CREATE TABLE #temp4(
[SiteCodeID] int NULL,
[TrenchID] int NULL,
[Context] [nvarchar](20) NULL,
[FindsID] int NULL,
[BagNo] [nvarchar](20) NULL,
[FindTypeID] [int] NULL,
[FindQty] [int] NULL,
[FindWeight] [float] NULL,
[FindNotes] [nvarchar](500) NULL
) ON [PRIMARY]
INSERT INTO #temp4 ([SiteCodeID], [TrenchID], [Context], [FindsID], [BagNo], [FindTypeID], [FindQty], [FindWeight], [FindNotes])
SELECT [SiteCodeID], [TrenchID], [Context], [FindsID], [BagNo], [FindTypeID], [FindQty], [FindWeight],
CASE WHEN [FindNotes] = '''' THEN NULL ELSE [FindNotes] END
FROM [#temp1] ORDER BY SiteCodeID,[TrenchID], Context, [FindsID], BagNo, FindTypeID
DROP TABLE #temp1
SELECT * FROM #temp4 ORDER BY FindsID
DROP TABLE #temp4'
EXEC sp_executesql @SQL, N'@FindTypeID INT', @FindTypeID
END
您不能为此使用UDF。 您将需要动态SQL来解决问题。
UDF有一些限制。 其中,您不能在UDF中使用EXEC函数。 您必须要解决存储过程才能执行动态SQL。
有些事情,我不得不提到....或面临downvote ;-)
1)使用动态SQL 2时当心SQL注入)构建动态SQL 时,应使用QUOTENAME函数3)当您使用动态SQL
试试这个数据库管理员不喜欢它......
CREATE PROCEDURE ReturnFindsWithNotes
@table nvarchar(50),
@FindTypeID int,
@qtyfld nvarchar(50),
@wtfld nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
SELECT * INTO #temp1
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, @FindTypeID As FindTypeID, ' + @qtyfld + ' AS FindQty, ' + @wtfld + ' AS FindWeight, Notes, NotesToType
,LTRIM(RTRIM((b.splitdata))) AS FindNotes
FROM ' + @table +
' CROSS APPLY dbo.fnSplitString(FindsNWW.Notes,''^'') AS b
) AS k
SELECT * INTO #temp2
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, SiteCodeID, TrenchID, Context, findsID, BagNo, @FindTypeID As FindTypeID, ' + @qtyfld + ' AS FindQty, ' + @wtfld + ' AS FindWeight, Notes, NotesToType
,LTRIM(RTRIM((c.splitdata))) As FindTypeIDFromNotes
FROM ' + @table +
' CROSS APPLY dbo.fnSplitString(FindsNWW.NotesToType,''^'') AS c
) AS l
INSERT INTO @query ([SiteCodeID], [TrenchID], [Context], [FindsID], [BagNo], [FindTypeID], [FindQty], [FindWeight], [FindNotes])
SELECT #temp1.SiteCodeID, #temp1.TrenchID, #temp1.Context, #temp1.FindsID, #temp1.BagNo, #temp1.FindTypeID, #temp1.FindQty, #temp1.FindWeight, FindNotes
FROM #temp1 JOIN #temp2 on #temp1.ID = #temp2.ID WHERE FindTypeIDFromNotes = @FindTypeID
DROP TABLE #temp1
DROP TABLE #temp2'
EXEC sp_executesql @SQL, N'@FindTypeID INT', @FindTypeID
END
我欣赏这里的意见和建议。我正在努力克服缺乏SP和功能知识/经验。由于数据用于本地数据处理以创建将在稍后日期使用的数据表,幸运的是,SQL注入的问题不会出现,因为最终可见数据的用户将不能直接访问sql ,只需通过网络访问。关于上面的程序,我收到一个错误:'消息208,级别16,状态6,过程ReturnFindsWithNotes,行33' ''无效的对象名'ReturnFindsWithNotes'.' –
对不起,错误是我的错,我没有删除以前的过程。我将通过这个工作并尝试理解这些原则,谢谢。 –
非常感谢@Spock,我已经添加了上面的工作过程。 –
过多的失误是没有指出。首先你不能在函数内部创建'temp tables',所以把这个过程设为 –
这个错误是因为你试图插入你的表中,但是你的select语句中只有一列,那是一个很大的丑陋的动态sql字符串。这需要对你正在尝试做的事情有一个全面的反思。临时表在这里毫无意义,一个cte会更好。 –