چندین بار فراخوانی یک store procedure دریک procedure دیگر

[omid66]

سلام به دوستان
من قصد دارم یک procedure رو داخل یک procedure دیگه بطور مکرر صدا بزنم بطوریکه پاراکتر ورودی این procedure تشکیل شده یک سری مقدار که میتونه از داخل یک متغیر XML یا select یک جدول بدست بیاد

دقیقا می خوام همچنین کاری رو بکنم ولی متاسفانه نمیشه:



Declare @Tbl table (ProductID nvarchar(100), ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)
Declare @XML xml
set @XML = '<list><id>1</id><id>6></id></list>'


select Exec [dbo].[usp_GetRelatedProductsByID] select ParamValues.id.value('.','VARCHAR(50)') as id FROM @XML.nodes('/list/id') as ParamValues(id)

Select * from @Tbl
باتشکر

[Reza_Yarahmadi]

يك راه ميتونه بصورت زير باشه.
دستورات SP رو بصورت يك Function بنويسيد و به روش زير كاري كه ميخوايد رو انجام بديد

Declare @XML xml, @Str varchar(max)
Set @XML = '<list><id>1</id><id>6</id></list>'
Set @Str = ''

Select @Str = @Str + Case When @Str <> '' Then '
UNION ALL
' Else '' END +
'Select dbo.FunctionName(' + ParamValues.id.value('.','VARCHAR(50)') + ')'
FROM @XML.nodes('/list/id') as ParamValues(id)

EXEC (@Str)

[omid66]

البته این جوری هم میشه ولی یک مشکل پیش میاد
store Procedure یا function من یک جدول برمیگردونه که من هرکاری کردم نتونستم با تابع این جدول رو برگردونم
دلیلشم بخاطر وجود تعریف چندتا متغیر داخل تابع است که پیغام خطا میده
اینم کدش:


Declare @CatId bigint
Declare @TypeId bigint

select @TypeId=ProductTypeID from Products where ProductID=@ProductId
Select @CatId=ProductCategories.ProductCategoryID from ProductTypeCategories,ProductCategories
where ProductCategories.ProductCategoryID=ProductTypeCat egories.ProductCategoryID
and ProductTypeCategories.ProductTypeID = @TypeId

Declare @Tbl table (ProductID bigint, ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)

Insert into @Tbl
Select distinct Products.ProductID, Products.ProductTypeID, Products.Name,
Products.Price, Products.Count, Products.VisitCount, Products.Status,
(Select dbo.GetCountOfPropertiesSimilarity(@ProductId,Prod ucts.ProductID)) as TotalPurchase
from Products, ProductProperties
Where Products.ProductTypeID in (Select ProductTypeCategories.ProductTypeID from ProductTypeCategories
Where ProductTypeCategories.ProductCategoryID=@CatId)
and Products.ProductID<>@ProductId
and Products.Status=1

Select top(5)* from @Tbl Where TotalPurchase<>0 order by TotalPurchase desc, VisitCount desc

[mehdi.mousavi]

[SIZE=2]البته این جوری هم میشه ولی یک مشکل پیش میاد store Procedure یا function من یک جدول برمیگردونه که من هرکاری کردم نتونستم با تابع این جدول رو برگردونم دلیلشم بخاطر وجود تعریف چندتا متغیر داخل تابع است که پیغام خطا میده

سلام.
بدین شکل عمل کنید:


CREATE FUNCTION [dbo].[fnMyFunction]
(
-- Add the input parameters for the function here
@InputId INT
)
RETURNS @result TABLE (
Id INT
)
AS
BEGIN
DECLARE @OtherVariable INT = 0
INSERT INTO @result
SELECT ...

RETURN;
END

اونجاییکه نوشتم Add the input parameters... پارامترهای ورودی Function رو قرار بدید.
اونجاییکه DECLARE @OtherVariable نوشتم، متغیرهای مورد نیاز برای نوشتن تابع رو قرار بدید.
در نهایت هم مقادیر مورد نظرتون رو در جدول @result بریزید...
فقط دقت کنید که جدول @result رو در بخش RETURNS تعریف کنید (من الان فقط برای نشون دادن Structure تابع، یه Id از نوع INT تعریف کردم، که طبیعتا شما باید اینو بر اساس نیازتون تغییر بدید).

موفق باشید.

[omid66]

سلام.
بدین شکل عمل کنید:


CREATE FUNCTION [dbo].[fnMyFunction]
(
-- Add the input parameters for the function here
@InputId INT
)
RETURNS @result TABLE (
Id INT
)
AS
BEGIN
DECLARE @OtherVariable INT = 0
INSERT INTO @result
SELECT ...

RETURN;
ENDاونجاییکه نوشتم Add the input parameters... پارامترهای ورودی Function رو قرار بدید.
اونجاییکه DECLARE @OtherVariable نوشتم، متغیرهای مورد نیاز برای نوشتن تابع رو قرار بدید.
در نهایت هم مقادیر مورد نظرتون رو در جدول @result بریزید...
فقط دقت کنید که جدول @result رو در بخش RETURNS تعریف کنید (من الان فقط برای نشون دادن Structure تابع، یه Id از نوع INT تعریف کردم، که طبیعتا شما باید اینو بر اساس نیازتون تغییر بدید).

موفق باشید.

سلام جناب موسوی
باتشکر
من تست کردم بازهم با مشکل برخورد و این خطا رو اعلام میکنه:
Msg 178, Level 15, State 1, Procedure GetRelatedProductsByID, Line 36
A RETURN statement with a return value cannot be used in this context.

لطفا کد منو چک کنید آیا طور دیگه ای باید نوشت:


CREATE FUNCTION [dbo].[GetRelatedProductsByID]
(
@ProductId bigint
)
RETURNS @Tbl table (ProductID bigint, ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)
AS
Begin
Declare @CatId bigint
Declare @TypeId bigint
Declare @Temp table (ProductID bigint, ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)

select @TypeId=ProductTypeID from Products where ProductID=@ProductId
Select @CatId=ProductCategories.ProductCategoryID from ProductTypeCategories,ProductCategories
where ProductCategories.ProductCategoryID=ProductTypeCat egories.ProductCategoryID
and ProductTypeCategories.ProductTypeID = @TypeId

Insert into @Temp
Select distinct Products.ProductID, Products.ProductTypeID, Products.Name,
Products.Price, Products.Count, Products.VisitCount, Products.Status,
(Select dbo.GetCountOfPropertiesSimilarity(@ProductId,Prod ucts.ProductID)) as TotalPurchase
from Products, ProductProperties
Where Products.ProductTypeID in (Select ProductTypeCategories.ProductTypeID from ProductTypeCategories
Where ProductTypeCategories.ProductCategoryID=@CatId)
and Products.ProductID<>@ProductId
and Products.Status=1
Insert into @Tbl
Select top(5)* from @Temp Where TotalPurchase<>0 order by TotalPurchase desc, VisitCount desc

Return @Tbl
End

باتشکر

[Reza_Yarahmadi]

دستور Return رو بصورت زير تغيير بديد

Return;
End

[omid66]

دستور Return رو بصورت زير تغيير بديد

Return;
End

ممنون
چند دقیقه پیش اشکال رو حل کردم
ولی یک مشکل دیگه پیش اومده، نمی تونم از تابع استفاده کنم و روش آقای یاراحمدی هم خطا داد نمی دونم چرا.

[Reza_Yarahmadi]

چه خطايي ميده؟ چه دستوري نوشتيد؟ لطفا دستور و متن خطا رو بذاريد.

[omid66]

چه خطايي ميده؟ چه دستوري نوشتيد؟ لطفا دستور و متن خطا رو بذاريد.

خطا:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetRelatedProductsByID", or the name is ambiguous.

[Reza_Yarahmadi]

پس دستوري كه نوشتيد كجاست؟ اونو هم بذاريد تا ببينيم مشكل چيه ، چون اون نمونه اي كه نوشتم بدون مشكل اجرا ميشه.

[omid66]

این کد تابع من:


ALTER FUNCTION [dbo].[GetRelatedProductsByID]
(
@ProductId bigint
)
RETURNS @Tbl table (ProductID bigint, ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)
AS
Begin
Declare @CatId bigint
Declare @TypeId bigint
Declare @Temp table (ProductID bigint, ProductTypeID bigint, Name nvarchar(450)
,Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)

select @TypeId=ProductTypeID from Products where ProductID=@ProductId
Select @CatId=ProductCategories.ProductCategoryID from ProductTypeCategories,ProductCategories
where ProductCategories.ProductCategoryID=ProductTypeCat egories.ProductCategoryID
and ProductTypeCategories.ProductTypeID = @TypeId

Insert into @Temp
Select distinct Products.ProductID, Products.ProductTypeID, Products.Name,
Products.Price, Products.Count, Products.VisitCount, Products.Status,
(Select dbo.GetCountOfPropertiesSimilarity(@ProductId,Prod ucts.ProductID)) as TotalPurchase
from Products, ProductProperties
Where Products.ProductTypeID in (Select ProductTypeCategories.ProductTypeID from ProductTypeCategories
Where ProductTypeCategories.ProductCategoryID=@CatId)
and Products.ProductID<>@ProductId
and Products.Status=1
Insert into @Tbl
Select top(5)* from @Temp Where TotalPurchase<>0 order by TotalPurchase desc, VisitCount desc

Return
End


اینم کد شما که استفاده کردم:


Declare @XML xml, @Str varchar(max)
Set @XML = '<list><id>1</id><id>6</id></list>'
Set @Str = ''

Select @Str = @Str + Case When @Str <> '' Then '
UNION ALL
' Else '' END +
'Select dbo.GetRelatedProductsByID(' + ParamValues.id.value('.','VARCHAR(50)') + ')'
FROM @XML.nodes('/list/id') as ParamValues(id)

EXEC (@Str)

[omid66]

با تشکر از تمام دوستان بابت راهنمایی هاشون

بالاخره تونستم ازطریق Store procedure حلش کنم اینم کدش:
اما همچنان منتظر پاسخ جناب یاراحمدی هستم، چون این روش رو بلد نبودم
با تشکر



Declare @Tbl table (ProductID nvarchar(100), ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)
Declare @Tbl1 table (ProductID nvarchar(100), ProductTypeID bigint, Name nvarchar(450)
, Price bigint, Count bigint, VisitCount bigint, Status bit, TotalPurchase int)

Declare @XML xml
set @XML = '<list><id>1</id><id>2</id></list>'

declare @Proc nvarchar(50)
declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)

set @RowCnt = 1
set @Proc = 'usp_GetRelatedProductsByID'

-- These next two rows are specific to source table or query
declare @Products table(ProductID bigint)
insert into @Products (ProductID)
select ParamValues.id.value('.','VARCHAR(20)')
from @XML.nodes('/list/id') as ParamValues(id)

declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , ProductID nvarchar(10))
insert into @Import (ProductID) select ProductID from @Products

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows
begin
select @ExecSql = 'exec ' + @Proc + ' ''' + ProductID + '''' from @Import where rownum = @RowCnt
--print @ExecSql
insert into @Tbl
execute sp_executesql @ExecSql
Select @RowCnt = @RowCnt + 1
insert into @Tbl1
Select ProductID, ProductTypeID ,Name, Price, [COUNT] , VisitCount , [Status] , TotalPurchase from @Tbl
where ProductID not in (Select ProductID from @Tbl1)
end
Select * from @Tbl1