Better Way To Rewrite Stored Proc And Not Use Cursor At All
May 26, 2015
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
Hello. I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want). Not I need that ID in another stored procedure and I can't get it out from the stored procedure.
exec @blabla = MyProc -- works well if I use return exec MyProc @blabla -- works using OUTPUT keyword
But neither of these examples works with a CURSOR as the @blabla. Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:
DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FOR exec MyProc
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc @parentId integer, @outputCursor CURSOR VARYING OUTPUT AS BEGIN TRAN T1 DECLARE parent_cursor CURSOR STATIC FOR SELECT parentTable.childId, parentTable. parentValue FROM parentTable WHERE parentTable.parentId = @parentId OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int DECLARE @parentValue varchar(50) FETCH NEXT FROM parent_cursor INTO @childId, @parentValue WHILE @@FETCH_STATUS = 0 BEGIN SELECT childTable.childValue FROM childTable WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue END
CLOSE parent_cursor DEALLOCATE parent_cursor COMMIT TRAN T1 GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter() da.SelectCommand = New SqlCommand("myStoredProc", conn) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int) paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor") paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
I have a stored Procedure that is looping through multiple cursors.
It is never finding any records in any curosr that is using a local variable in the where clause...Help
Alter Procedure ProjectedIncome As SET ROWCOUNT 0 Declare -- Date types @startdate smalldatetime ,@enddate smalldatetime ,@ProjectedDate smalldatetime ,@termination smalldatetime ,@effectivedate smalldatetime -- Integer ,@Nums int ,@nums2 int ,@ClientId int ,@AssetId int ,@ProductID int ,@Policies int ,@product int ,@Per int ,@Projected int
-- String ,@debugtext varchar(150) ,@productid2 varchar(15) -- float ,@rate float ,@Cap float --bit ,@Override bit --Money ,@AnnualPremium Money ,@Value Money ,@Premium Money ,@PaymentAmount Money --Doubles ,@PremCalc int ,@HoldPrem int ,@HoldCom int ,@CumBal int ,@CumPrem int ,@MonthlyPrem int ,@XBal int ,@CapPrev int ,@PremTier int ,@Incriment int
--Declare cursor for System Variables DECLARE SystemVar_cur cursor for SELECT ProjectionStartDate,ProjectionEndDate from SystemVariables
--Declare the Cursor for Asset Definitions declare AssetDef_cur cursor for SELECT termination,effectivedate,ClientID,AnnualPremium,A ssetID,ProductID,Policies from AssetDefinitions
--Declare cursor for CommisionDefinitions declare CommisionDef_cur cursor for Select a.product,a.per,a.cap,a.rate,a.value from CommisionDefinitions a where a.product = @ProductId2;
--Declare cursor for projections declare projections_cur cursor for Select a.override,a.premium,a.paymentamount from projections a where a.date = @ProjectedDate and assetid = @AssetId;
-- Select from the SystemVariables Table OPEN SystemVar_cur FETCH SystemVar_cur INTO @startdate,@enddate CLOSE SystemVar_cur DEALLOCATE SystemVar_cur -- Open the AssetDefinition File and loop through -- INSERT INTO debug_table VALUES('Open the Asset Cursor')
Open AssetDef_cur
Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies
While @@fetch_status = 0 Begin-- begin AssetDefinitions Loop
--If Asset is not Terminated
If @termination IS NULL BEGIN-- begin @termination IS NULL SET @MonthlyPrem = (@AnnualPremium/12) SET @debugtext = 'MonthlyPrem = AnnualPrem' + CAST(@AnnualPremium as Char) + '/12' INSERT INTO debug_table VALUES(@debugtext)
If @effectivedate > @startdate SET @ProjectedDate = @effectivedate Else SET @ProjectedDate = @startdate -- end if SET @PremCalc = 0 SET @CumBal = 0 SET @XBal = 0 SET @HoldCom = 0
-- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount If @@fetch_status = 0 BEGIN IF @override = 1 BEGIN-- begin @override = 1
SET @CumPrem = @premium SET @CumBal = @paymentamount SET @HoldPrem = @CumPrem SET @HoldCom = @CumBal END-- end @override = 1 Else SET @HoldPrem = @MonthlyPrem END CLOSE projections_cur While @ProjectedDate <= @enddate BEGIN-- begin While @ProjectedDate <= @enddate SET @CapPrev = 0 --reset cap balance SET @XBal = 0 SET @debugtext = 'Begin Get Commision Record For Product' + CAST(@productID as CHAR) INSERT INTO debug_table VALUES(@debugtext) SET @productid2 = @productid SET @PremTier = @HoldPrem
---NOW Open the CommisionDef table OPEN CommisionDef_cur
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value IF @@fetch_status <> 0 BEGIN SET @debugtext = 'ERROR? ' + CAST(@@error as Char) INSERT INTO debug_table VALUES(@debugtext) END WHILE @@fetch_status = 0 BEGIN-- begin While CommisionDef Fetch = 0 SET @debugtext = 'Found Commision Record' + CAST(@product as Char) INSERT INTO debug_table VALUES(@debugtext)
If @Per = 0 BEGIN-- begin If @Per = 0
SET @Incriment = @Cap - @CapPrev If @PremTier > @Incriment SET @XBal = @XBal + (@Incriment * @Rate) Else BEGIN-- begin @PremTier > @Incriment If @PremTier >= 0 SET @XBal = @XBal + (@PremTier * @Rate) END-- end @PremTier > @Incriment SET @debugtext = 'XBal ' + CAST(@XBal as CHAR(10)) INSERT INTO debug_table VALUES(@debugtext)
SET @CapPrev = @Cap SET @PremTier = @PremTier - @Incriment END-- end If @Per = 0
Else BEGIN-- begin If @Per <> 0
SET @XBal = @value * @Policies / 12 SET @HoldCom = 0 SET @PremCalc = 0 SET @CumBal = @XBal SET @debugtext = 'CumBal' + CAST(@CumBal as Char) INSERT INTO debug_table VALUES(@debugtext)
SET @HoldPrem = @Policies END-- end If @Per <> 0
FETCH CommisionDef_cur INTO @product,@per,@cap,@rate,@value END-- end While CommisionDef Fetch = 0
CLOSE commisionDef_cur -- Fetch the Projection Record open projections_cur
fetch projections_cur INTO @override,@premium,@paymentamount IF @@fetch_status = 0 BEGIN -- begin Projection Fetch = 0 IF @override = 1 SET @HoldCom = @CumBal ELSE -- If not overridden, set the fields to Update the projection File BEGIN-- begin @override <> 1 SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc UPDATE projections SET projected = @projected, premium = @Premium where assetid=@AssetID and date = @ProjectedDate SET @HoldCom = @XBal END-- end @override <> 1
END-- end Projection Fetch = 0 ELSE BEGIN -- Begin Projection Fetch else IF @@fetch_status = -1 BEGIN-- begin Projection Fetch = -1
SET @Projected = ((@XBal - @HoldCom) * 100 + 0.5) / 100 SET @Premium = @HoldPrem - @PremCalc SET @debugtext = '((xbal - holdcom)*100 + 0.5)/100 ' + CAST(@Xbal as char) + ' , ' + CAST(@holdcom as CHAR) INSERT INTO debug_table VALUES(@debugtext)
SET @debugtext = 'Projection Record Not Found so Write it' INSERT INTO debug_table VALUES(@debugtext) --Projection record was not found so write it SET @override = 0 INSERT INTO Projections (AssetId,Date,Premium,Projected,Override,Payment,P aymentAmount) VALUES(@AssetId,@ProjectedDate,@Premium,@Projected ,@override,0,0) SET @HoldCom = @XBal END-- end Projection Fetch = -1 END -- end Projection Fetch else
CLOSE projections_cur
SET @ProjectedDate = DateAdd("m", 1, @ProjectedDate) SET @PremCalc = @HoldPrem -- Fetch the Projection Record OPEN projections_cur
FETCH projections_cur INTO @override,@premium,@paymentamount IF @override = 1 BEGIN-- begin @override = 1
SET @CumBal = @paymentamount SET @HoldPrem = @HoldPrem + @CumPrem END -- end @override = 1
ELSE SET @HoldPrem = @HoldPrem + @MonthlyPrem CLOSE projections_cur
END-- End the While ProjectedDate <=@enddate END --End the If Termination is NULL Fetch AssetDef_cur INTO @termination ,@effectivedate ,@clientId ,@AnnualPremium ,@assetId ,@ProductId ,@Policies END CLOSE AssetDef_cur DEALLOCATE AssetDef_cur DEALLOCATE projections_cur DEALLOCATE CommisionDef_cur
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc ( @IDINT, @NameVARCHAR (200), @SessionIDINT ) AS BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
---Master query (Assuming this will display 20 rows) we are dealing with one single table that we need to pivot. select id,fname,lname,sponsor from masterfile where id='TARZAN'
---from those 20 rows there is id that sponsored some one else ---explain: assuming ID=SHAGGY FNAME=Shaggy LNAME=Scooby (was sponsored by Tarzan) ---but Shaggy has sponsored 2 others select id,fname,lname,sponsor from masterfile where id='SHAGGY'
---will display 3 rows and if from one of those 3 others that belongs to shaggy ---I also want to get their information ID,fname,lname ---This can go up to 10 per saying is like building a Tree with branches and leaves under those branches
---Explain: ---Let's assume that we have an OAK Tree that has 4 main branches ---and out of those 4 main branches 2 of them have other branches with leaves under it
--I would like to do this process in a cursor (Store Proc) is possible --the way I have it now taking way too long --because in within so many (do while loop)
TIA Please pardon me, I could not find better layout to explain this.
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
Existing Stored Procedure, has been running well on SQL since 7.0.(but needed some tweaking to migrate to 2000).Now all of a sudden after installing SP4 of SQL 2000,this process slows down, and SQL Spotlight shows the number of locksjust climbing throughout the processing run.According to the MS Knowledge Base Articles on KeyLocks .. this was aproblem that was *fixed* in the service pack ... where as for me it isnow broken.Article ID: 260652PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"May Hold Locks Longer http://support.microsoft.com/kb/260652/Article ID: 828096FIX: Key Locks Are Held Until the End of the Statement for Rows ThatDo Not Pass Filter Criteria http://support.microsoft.com/kb/828096/Anybody else have this issue, or have any "eazy" solutions?The proc cursors thru a list and runs a proc on each item in the "worklist".This is an existing systemwith no plans to turn the process into a set oriented one,as is going away shortly.
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
SELECT Loan.loan_No AS Loan_No, Loan.customer_No AS Customer_No, Customer.first_name AS First_name, Customer.second_name AS Second_name, Customer.surname AS Surname, Customer.initials AS Initials, Bank.Bank_name AS Bank_name, Branch.Branch_name AS Branch_name, Branch.branch_code AS Branch_code , Bank_detail.bank_acc_type AS Bank_acc_type, Transaction_Record.transaction_Amount AS Transaction_Amount, Transaction_Record.transaction_Date AS Transaction_Date, Loan.product AS Product, Product.product_Type AS Product_Type, Product_Type.loan_Type AS Loan_Type
FROM Transaction_Record INNER JOIN Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN Product ON Loan.product = Product.product INNER JOIN Customer ON Loan.customer_No = Customer.customer_no INNER JOIN Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN Bank ON Branch.Bank = Bank.Bank INNER JOIN Product_Type ON Product.product_Type = Product_Type.product_Type
Here's the code I'd like to update, and below it a set of sample data:
Declare @StartDate DateTime Declare @EndDate DateTime Set @StartDate = '20-mar-2008' Set @EndDate = '25-mar-2008'
SELECT
COUNT (iqs.childid) as Cnt ,CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END AS CategoryGroup ,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate
FROM InventoryQueryStatus iqs JOIN InventoryStatus [is] ON [is].StatusID = iqs.StatusID JOIN Inventory i ON i.InventoryID = iqs.InventoryID JOIN InventoryCategory ic ON ic.CategoryID = i.CategoryID WHERE iqs.StatusID = 31000 and Category NOT LIKE 'Force%' --AND iqs.StatusDate >=GETDATE()-1 AND iqs.StatusDate BETWEEN @StartDate AND @EndDate GROUP BY CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END ,CONVERT(VARCHAR(10), iqs.StatusDate, 103)
I have a WHERE clause that could be an "=" or a "LIKE" depending uponif the passed variable is populated or not. I would like to know thebest way to write the WHERE clause to make it dynamically switchbetween the 2 and make best use of the indexes.CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))CREATE INDEX IDX_myTable_County ON myTable(COUNTY)DECLARE @COUNTY VARCHAR(50)SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULLSELECT ID FROM myTableWHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'END)This does not seem like best practice to me because I am forced to use"LIKE" even when @COUNTY is populated with data. Ultimately I'd like:WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE'%' END)but that is incorrect syntax on "=".Also, I do not want to use a dynamically built statement. Is there away around this?Thanks,Josh
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
SELECT CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range, COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries] FROM Country GROUP BY CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END
We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?
Our current index script template is:
Create NonClustered Index [IdxABCDE] On dbo.blahblah(blahID) With FillFactor = 90, Statistics_NoRecompute On [Index2] Go
and I need to rewrite it as:
ALTER INDEX [IdxABCDE] ON [dbo].[blahblah] REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)
I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..
Thanks..
Dinakar Nethi ************************ Life is short. Enjoy it. ************************ http://weblogs.sqlteam.com/dinakar/
I have a complex stored procedure that utilises inner joins, and in the WHERE clause there is defined a subquery. This subquery has now cause a performance hit to the ponit where the server is returning a timeout. Need to find an alternate fast solution.....
SELECT BE.BlogEntryID
FROM vw_BlogEntry BE
INNER JOIN @BlogView BC ON BC.CommonID = BE.BlogCommonID
INNER JOIN vw_Blog B ON B.BlogID = BC.BlogID
WHERE (
... ) AND (
.... )
AND
(
-- GET ENTRIES WHERE COMMENT COUNT IS AT LEAST @CommentCount (..or @CommentCount = 0)
@CommentCount <= 0
OR BE.CommonID IN (SELECT bc.EntryCommonID FROM vw_BlogComment_Current bc
INNER JOIN tblVersionDetails vd ON bc.VersionID = vd.VersionID
WHERE
IsNull(@CommentStatus,'') = ''
OR vd.Status IN (SELECT * FROM fn_CsvToString(@CommentStatus))
This works, but it's highly unefficient and generates a lot of IO. Is there another way to do it without the use of temp tables?
Code Block select eh.* from Equipment_History eh where Equipment_History_ID in (select top 2 Equipment_History_ID from Equipment_History eh1 where Equipment_ID in (select Equipment_ID from Equipment_History eh2 where Equipment_History_ID in (select min(Equipment_History_ID) from Equipment_History eh3 where eh2.Equipment_ID = eh3.Equipment_ID and eh2.Equipment_Status_Type in (1,2,3))) and eh1.Equipment_ID = eh.Equipment_ID order by Equipment_History_ID)
Equipment_History_ID is PK
Let me know what other information would be useful, I can barely understand my logic from looking at the code but it does in fact work.
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId, CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99 ELSE
(someCol*0.4+someOtherCol*0.3) END FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol, CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol FROM
SomeTable ) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).
I have altered the sp_help_revlogin (from Microsoft article # 246133 )
PLEASE TEST/FIX before you use this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin_2005]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @logintype char(1)
DECLARE @logindisabled int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name <> 'sa' and type in ('S','U','G')
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
I have a activeX script like below in DTS and I am trying to rewrite it in SSIS. What is the best way to do this? Can I do this using a look up table? or other transformers in SSIS '********************************************************************** ' Visual Basic Transformation Script '************************************************************************
' Copy each source column to the destination column Function Main() if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination("RACE_NATIVE_AM_IND") = "Y" else if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then DTSDestination("RACE_NATIVE_AM_IND") = "N" end if end if
if DTSSource("RACE_ASIAN_YN") = "1" then DTSDestination("RACE_ASIAN_IND") = "Y" else if DTSSource("RACE_ASIAN_YN") = "2" then DTSDestination("RACE_ASIAN_IND") = "N" end if end if
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination("RACE_BLACK_IND") = "Y" else if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then DTSDestination("RACE_BLACK_IND") = "N" end if end if
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination("RACE_HAWAIIAN_IND") = "Y" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then DTSDestination("RACE_HAWAIIAN_IND") = "N" end if end if
if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_WHITE_IND") = "Y" else if DTSSource("RACE_CAUCASIAN_YN") = "2" then DTSDestination("RACE_WHITE_IND") = "N" end if end if
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination ("RACE_CD") = 40 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_ASIAN_YN") = "1" then DTSDestination ("RACE_CD") = 16 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination ("RACE_CD") = 32 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination ("RACE_CD") = 51 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_CD") = 31 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" end if end if end if end if end if else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then DTSDestination("RACE_CD") = 99 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then DTSDestination("RACE_CD") = 52 DTSDestination("RACE_MULT_IND") = "Y" DTSDestination("RACE_OTH_IND") = "N" end if end if end if
