May 19, 2011 at 4:44 am
Hi All,
I Have aproblem in the Below Query.
i need to pass multiple values in the parameter,But the delete statement we have one condition 'FiscalYear < @FISCALYEAR ' this condition is not supported to pass multiple values please give me suggestions.
In my report i am selecting at a time maney years (more than one). how to change the delete logic for passing more than one year.
ALTER PROCEDURE [dbo].[SP_FIRT_TIME_GRANTEES]
(@FISCALYEAR VarChar(max) = NULL,
@FISCALMONTH VarChar(max) = NULL)
AS
-- EXEC SP_FIRT_TIME_GRANTEES '2011,2010', 04
BEGIN
DECLARE @GRANTEES TABLE (CostCenter Varchar(10), PANumber Varchar(15), GrantSummaryKey Int,
GranteeName Varchar(250), GranteeKey INT, NetAmount Float, TotalAmount Float, FisYear INT, FisMonth INT)
INSERT INTO @GRANTEES
select NULL, GSD.PANumber, GSD.GrantSummaryKey, GranteeLegalName, PrimaryGranteeKey,
GrantTargetAmount, NULL, FCD.FiscalYear, FCD.FiscalMonth
FROM GrantDetails_Fact GDF
Inner Join GrantSummary_Dim GSD ON GDF.GrantSummaryKey = GSD.GrantSummaryKey
Inner Join FiscalCalendar_Dim FCD ON GDF.FiscalYear = FCD.FiscalCalendarKey
Inner Join GranteeSummary_Dim GESD ON GDF.PrimaryGranteeKey = GESD.GranteeSummaryKey
Where FCD.FIscalMonth IS NOT NULL AND GDF.GrantTypeKey IN (1, 3) And
CHARINDEX(LTRIM(RTRIM(CAST(FCD.FiscalYear as VarChar(4)))), @FISCALYEAR)>0
and CHARINDEX(LTRIM(RTRIM(CAST(FCD.FiscalMonth as VarChar(4)))),@FISCALMONTH)>0
Order By GranteeLegalName
--SELECT * FROM @GRANTEES
Delete From @GRANTEES
Where GranteeKey IN (Select GDF.PrimaryGranteeKey From GrantDetails_Fact GDF
Inner Join FiscalCalendar_Dim FCD ON GDF.FiscalYear = FCD.FiscalCalendarKey
Where GDF.PrimaryGranteeKey = GranteeKey AND FCD.FiscalYear < @FISCALYEAR
AND GDF.GrantTypeKey IN (1, 3))
SELECT * FROM @GRANTEES order by GranteeKey
END
Thanks in Advance
May 19, 2011 at 7:47 am
This may help, but first you need to read about splitting delimited strings in the Tally table article referenced in my sig below.
CREATE PROCEDURE MyReport(@years VARCHAR(max)=null)
AS
DECLARE @t TABLE(FiscalYear int)
INSERT @t SELECT 2001 UNION SELECT 2002 UNION SELECT 2003 UNION SELECT 2004 UNION SELECT 2005
DELETE @t WHERE FiscalYear IN
(SELECT Item FROM dbo.fnSplitString(@years,',')) -- you'll need a string splitter function (see tally table article)
SELECT * FROM @t
GO
EXEC MyReport '2004,2001'
May 19, 2011 at 7:49 am
There are so many things wrong with this procedure.
First the name of the proc should not start with SP_. Sql Server interprets this as a system stored procedure and will look in the master database first for it, then look in the database you are connect to.
Second, the insert into the table is using CHARINDEX(LTRIM(RTRIM(CAST on a field that realy should be an smallint. These functions will not use any indexes and therefore perform a full table scan. If the table is large (and considering you have named it _Fact, I assume it is), then this will be really slow. Especially, the CharIndex.
Third, it looks like you have hijacked the parameters to met your needs. Why would Fiscal Month and Fiscal Year be varchar(max)? These should be smallint and tinyint. If you used an XML parameter, then you could shred it out in the proc and then perform the inserts and deletes from a join. Something like this:
<Criteria>
<FiscalYears>
<FiscalYear ID="2010"/>
<FiscalYear ID="2011"/>
</FiscalYears>
<FiscalMonths>
<FiscalMonth ID="04"/>
</FiscalMonths>
</Criteria>
Fourth, just to be complete, the last statement is Select *. This should never be used. What if the app calling this proc is expecting the 9 fields you declared in the table variable and then someone modifies this proc and adds a 10th field?
Sorry if this sounds harsh, I'm just trying to be honest and helpful
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply