تبدیل این query به FUNCTION

[fakhravari]

با سلام

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]

سلام.
من روی کوئری زیر مراحل کار رو توضیح میدم. شما طبق جدول و کوئری خودتون اصلاحات لازم رو انجام بدید.
کوئری :

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]

ممنون
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