Есть хранимая процедура состоящая из двух запросов. Если я создаю процедуру так :
ALTER PROCEDURE [dbo].[report_List_For_Category_6]
-- Add the parameters for the stored procedure here
@id INT
AS
BEGIN
т.е. с входным параметром id, а потом вызываю её
exec [report_List_For_Category_6] @id = 101
То процедура зависает намертво (не дождался окончания выполнения)
Если же создаю без входного параметра и задаю id в теле процедуры
ALTER PROCEDURE [dbo].[report_List_For_Category_6]
AS
BEGIN
declare
@id INT
SET NOCOUNT ON;
set @id = 101
то время выполнения секунд 5.
Подскажите в чем проблема (MS SQL Server 2008 R2).
Все что нашел в интернете касалось переменных/параметров против констант (переменные и входные параметры рассматривались как один класс).
CREATE PROCEDURE [dbo].[report_List_For_Category_6]
AS
declare
@id INT
BEGIN
SET NOCOUNT ON;
set @id = 101
DECLARE @Date SMALLDATETIME
SELECT @Date = [Date]
FROM SSCLists
WHERE Id = (
SELECT TOP 1 CLId
FROM SSLists
WHERE Id = @id
);
WITH latestMarriageDocs
AS (
SELECT Id
,ClientId
,StatementId
,RelationId
,TypeDocMarriageId
,NUM_DOC
,DATE_BRAK
,ORG_NAME
,ROW_NUMBER() OVER (
PARTITION BY StatementId ORDER BY DATE_BRAK DESC
) AS RowNum
FROM MDocs
WHERE StatementId IS NOT NULL
)
,addresses
AS (
SELECT ID
,StatementId
,PLACE_KLADR_ID
,ROW_NUMBER() OVER (
PARTITION BY StatementId ORDER BY id DESC
) AS AddrNum
FROM [Address] a
WHERE AddressTypeId = 2
AND StatementId IS NOT NULL
AND a.StatementId IN (
SELECT ssli.ClaimId
FROM SSLItems ssli
WHERE ssli.ListId = @id
)
)
,prices
AS (
SELECT mp.[Id]
,[DecreeId]
,[KladrCode]
,[Date]
,[Price]
,ROW_NUMBER() OVER (
PARTITION BY DecreeId
,KladrCode
,a.StatementId ORDER BY [Date] DESC
) AS PriceNum
,a.StatementId
FROM [MPrices] mp
INNER JOIN addresses a ON a.PLACE_KLADR_ID = mp.KladrCode
AND a.AddrNum = 1
WHERE mp.DecreeId = 3
AND mp.[Date] < @Date
)
SELECT *
,OwedSquare * tab1.Price AS Cost
,Convert(DECIMAL(10, 2), (OwedSquare * tab1.Price) / (tab1.Relcount - 1)) AS partCost
FROM (
SELECT dense_rank() OVER (
ORDER BY RegDate
,Id
) AS Num
,*
,(
CASE
WHEN relCount = 2
THEN 42
WHEN relCount = 3
THEN 54
WHEN relCount > 3
THEN relCount * 18
END
) OwedSquare
FROM (
SELECT TOP 10000 s.Id
,s.AdministrationsId
,DATE_ORG AS RegDate
,o.NAME AS omsuName
,c.id AS clientid
,dbo.count_Family_relations(s.Id) + 1 AS Relcount --1+ROW_NUMBER() OVER (PARTITION BY S.Id ORDER BY c.id DESC) AS Relcount
,ISNULL(c.FAMILY, '') + ' ' + ISNULL(c.NAME, '') + ' ' + ISNULL(c.LASTNAME, '') AS ClaimerFIO
,c.DATE_BORN AS ClaimerBirthday
,d.SER_DOC + ' ' + d.NUM_DOC AS ClaimerDoc
,d.SER_DOC AS Series
,d.NUM_DOC AS Number
,d.ORG_NAME AS Organ
,d.DOC_DATE AS DOCDATE
,r.TypeRelationshipId
,ISNULL(c2.FAMILY, '') + ' ' + ISNULL(c2.NAME, '') + ' ' + ISNULL(c2.LASTNAME, '') AS rFIO
,d2.SER_DOC + ' ' + d2.NUM_DOC AS rDoc
,d2.SER_DOC AS rSeries
,d2.NUM_DOC AS rNumber
,d2.ORG_NAME AS rOrgan
,d2.DOC_DATE AS rDocDATE
,c2.DATE_BORN rBirthday
,m.NUM_DOC
,m.ORG_NAME
,m.DATE_BRAK
,prices.Price
FROM SSLItems i
LEFT JOIN [Statement] s ON i.ClaimId = s.Id
LEFT JOIN Client c ON s.clientid = c.Id
LEFT JOIN Document d ON c.id = d.clientid
LEFT JOIN Program p ON s.id = p.statementid
LEFT JOIN Dic_OMSU o ON s.OMSUId = o.Id
LEFT JOIN Relation r ON r.statementid = s.id
AND r.TypeRelationshipId IN (
3
,4
,9
,10
,23
,24
)
AND RelationClientId IS NOT NULL
LEFT JOIN Client c2 ON c2.id = r.relationclientid
LEFT JOIN Document d2 ON c2.id = d2.clientid
AND (
d2.typedocumentid = 1
OR d2.typedocumentid = 3
)
LEFT JOIN prices ON prices.StatementId = s.Id
AND prices.PriceNum = 1
LEFT JOIN latestMarriageDocs m ON s.id = m.statementid
AND m.RowNum = 1
WHERE i.ListId = @id
AND i.STATE = 0
ORDER BY Id
) AS tab
) AS tab1
ORDER BY isnull(RegDate, 65535)
,Id ASC
,relcount DESC
END