PDA

View Full Version : تبدیل این query به FUNCTION



fakhravari
دوشنبه 21 اسفند 1391, 13:32 عصر
با سلام
with T
as
(
SELECT SyncChangesTable.TableName, SyncChangesTable.RecordValue, SyncListTable.Position
FROM SyncChangesTable INNER JOIN
SyncListTable ON SyncChangesTable.TableName = SyncListTable.TableName
GROUP BY SyncChangesTable.TableName, SyncChangesTable.RecordValue, SyncListTable.Position
)
select
TableName,
(SELECT top(1)RecordID FROM SyncChangesTable WHERE (RecordValue = T.RecordValue) and (TableName = T.TableName)) AS RecordID,
RecordValue,
(SELECT COUNT(Operations)FROM SyncChangesTable WHERE (RecordValue = T.RecordValue) AND (Operations = 'I')) AS I,
(SELECT COUNT(Operations)FROM SyncChangesTable WHERE (RecordValue = T.RecordValue) AND (Operations = 'U')) AS U,
(SELECT COUNT(Operations)FROM SyncChangesTable WHERE (RecordValue = T.RecordValue) AND (Operations = 'D')) AS D,
T.Position
from T
where
(SELECT COUNT(Operations)FROM SyncChangesTable WHERE (RecordValue = T.RecordValue) AND (Operations = 'D'))>'0'
order by T.Position
چطوری RETURNS TABLE کنم؟

veniz2008
دوشنبه 21 اسفند 1391, 19:30 عصر
سلام.
من روی کوئری زیر مراحل کار رو توضیح میدم. شما طبق جدول و کوئری خودتون اصلاحات لازم رو انجام بدید.
کوئری :

SELECT DISTINCT RecordsID,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'D')) AS D,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable AS SyncChangesTable_3
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'I')) AS I,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable AS SyncChangesTable_2
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'U')) AS U,
(SELECT TOP (1) sys1.TableName
FROM SyncChangesTable AS SyncChangesTable_1 inner join SyncListTable as sys2
on SyncChangesTable_1.TableName = sys2.TableName
WHERE (RecordsID = sys1.RecordsID)) AS TableName,
(SELECT TOP (1) sys2.Position
FROM SyncChangesTable AS SyncChangesTable_1 inner join SyncListTable as sys2
on SyncChangesTable_1.TableName = sys2.TableName
WHERE (RecordsID = sys1.RecordsID)) AS Position
FROM SyncChangesTable AS sys1 order by Position
خوب مراحل کار رو کامل توضیح میدم تا مشکلی پیش نیاد.
از قسمت Table valued Function یک function جدید ایجاد کنید و کدهای زیر رو داخلش بنویسید(توجه داشته باشید که در این مثال تابع پارامتر ورودی نداره. در غیر اینصورت بایستی پارامترها هم درون function و هم درون stored procedure قید شوند).

create function MyTestFunction()
-- در اینجا یک جدول ایجاد می کنیم که ستون های آن برابر با تعداد ستون های موجود در خروجی کوئری می باشد
returns @mytable Table
(
RecordsID int,
D int,
I int,
U int,
TableName nvarchar(50),
Position int
)
as
begin
-- رکوردهایی که در جدول درج می شوند و باید برگشت داده شوند، در واقع همان خروجی عبارت سلکت می باشد
insert into @mytable(RecordsID,D,I,U,TableName,Position)
SELECT DISTINCT RecordsID,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'D')) AS D,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable AS SyncChangesTable_3
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'I')) AS I,
(SELECT COUNT(Operations) AS Expr1
FROM SyncChangesTable AS SyncChangesTable_2
WHERE (RecordsID = sys1.RecordsID) AND (Operations = 'U')) AS U,
(SELECT TOP (1) sys1.TableName
FROM SyncChangesTable AS SyncChangesTable_1 inner join SyncListTable as sys2
on SyncChangesTable_1.TableName = sys2.TableName
WHERE (RecordsID = sys1.RecordsID)) AS TableName,
(SELECT TOP (1) sys2.Position
FROM SyncChangesTable AS SyncChangesTable_1 inner join SyncListTable as sys2
on SyncChangesTable_1.TableName = sys2.TableName
WHERE (RecordsID = sys1.RecordsID)) AS Position
FROM SyncChangesTable AS sys1 order by Position
-- در نهایت برگشت نتیجه را خواهیم داشت
return
end
حالا یه stored procedure باز کنید و کد کوتاه زیر رو داخلش بنویسید :

create proc SPTestFunction
as
select * from dbo.MyTestFunction()
go
حالا میتویند براحتی از این sp در برنامتون استفاده کنید.
توجه : هنگام استفاده از function ها در stored procedure حتما کلمه dbo بایستی قید بشه.
موفق باشید.

fakhravari
دوشنبه 21 اسفند 1391, 23:32 عصر
ممنون
query شما درست
ولی query مد نظر من توی پست اولم هست.
به کلمه with گیر میده .
USE [LogBOOKHOME]
GO
/****** Object: Table [dbo].[SyncChangesTable] Script Date: 03/12/2013 12:38:10 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SyncChangesTable](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](30) NOT NULL,
[RecordID] [nvarchar](30) NOT NULL,
[RecordValue] [nvarchar](20) NOT NULL,
[Operations] [nchar](1) NOT NULL,
[Flag] [tinyint] NULL
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[SyncListTable] Script Date: 03/12/2013 12:38:10 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SyncListTable](
[TableName] [nvarchar](30) NOT NULL,
[Position] [tinyint] NOT NULL,
CONSTRAINT [PK_SyncListTable] PRIMARY KEY CLUSTERED
(
[TableName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[SyncChangesTable] ON

GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(1 AS Numeric(18, 0)), N'B_Categories', N'CategoryID', N'7', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(2 AS Numeric(18, 0)), N'B_Categories', N'CategoryID', N'2', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(3 AS Numeric(18, 0)), N'B_Categories', N'CategoryID', N'2', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(4 AS Numeric(18, 0)), N'B_Forums', N'ForumID', N'34', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(5 AS Numeric(18, 0)), N'B_Forums', N'ForumID', N'35', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(6 AS Numeric(18, 0)), N'B_Categories', N'CategoryID', N'7', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(7 AS Numeric(18, 0)), N'B_Topics', N'TopicID', N'6', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(8 AS Numeric(18, 0)), N'B_Topics', N'TopicID', N'6', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(9 AS Numeric(18, 0)), N'B_Topics', N'TopicID', N'29', N'I', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(10 AS Numeric(18, 0)), N'B_Topics', N'TopicID', N'29', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(11 AS Numeric(18, 0)), N'B_Topics', N'TopicID', N'29', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(12 AS Numeric(18, 0)), N'Polls_Votes', N'VoteID', N'24', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(13 AS Numeric(18, 0)), N'Polls_Votes', N'VoteID', N'25', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(14 AS Numeric(18, 0)), N'Polls_Votes', N'VoteID', N'26', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(15 AS Numeric(18, 0)), N'Polls_Votes', N'VoteID', N'27', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(16 AS Numeric(18, 0)), N'Polls_Options', N'OptionID', N'5', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(17 AS Numeric(18, 0)), N'Polls_Questions', N'QuestionID', N'1', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(18 AS Numeric(18, 0)), N'Polls_Questions', N'QuestionID', N'3', N'D', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(19 AS Numeric(18, 0)), N'Polls_Questions', N'QuestionID', N'4', N'I', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(20 AS Numeric(18, 0)), N'Polls_Options', N'OptionID', N'3', N'U', 0)
GO
INSERT [dbo].[SyncChangesTable] ([ID], [TableName], [RecordID], [RecordValue], [Operations], [Flag]) VALUES (CAST(21 AS Numeric(18, 0)), N'Polls_Options', N'OptionID', N'4', N'U', 0)
GO
SET IDENTITY_INSERT [dbo].[SyncChangesTable] OFF
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'B_Categories', 1)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'B_Forums', 2)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'B_Topics', 3)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'Link', 7)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'Polls_Options', 5)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'Polls_Questions', 4)
GO
INSERT [dbo].[SyncListTable] ([TableName], [Position]) VALUES (N'Polls_Votes', 6)
GO