Avoiding Cursor

Sep 7, 2007

This query uses a cursor to fetch a parameter and pass it to another Stored proc. Is there a straightforward way to do this without using a cursor?

declare @deleteunassigned int
declare cur_unassigned cursor for select distinct a.cust_cont_pk
from cust_cont a, cont_fold_ass b (NOLOCK)
where a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
fetch next from cur_unassigned into @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
fetch next from cur_unassigned into @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO


declare @deleteunassigned int
declare cur_unassigned
cursor for
SELECT DISTINCT a.cust_cont_pk
FROM cust_cont a,
cont_fold_ass b (NOLOCK)
WHERE a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO



Future guru in the making.

View 2 Replies


ADVERTISEMENT

Help In Avoiding The Use Of A Cursor

Apr 8, 2008

Here is a simplified example of a problem I am facing.

I have 2 tables: Tasks and Employees.

Tasks:
(Task_ID, Task_Name, Task_Type, Task_Requirement, Employee_ID)
Employees:
Emp_ID, Emp_Name, Emp_Specialty, Emp_Task_Cnt, Max_Task_Cnt

Requirements: Write a MS SQLServer 2000 Storeed Procedure to:
1. Update the Tasks table by assigning the task to an Employee.
2. Incrememnt the employee's Emp_Task_Cnt for each Task assigned.
3. Match the Employee to the Task by matching the Task_Requirement to the Emp_Specialty.
4. Do not exceed the employee's Max_Task_Cnt.

I have a working solution to the requirements, but it involves using cursor logic. For all the obvious reasons, I wanted to avoid using a cursor (or cursor-like looping structure) but could not figure out any other way to avoid processing the Task table one record at a time because of the: "4. Do not allow an Employee's Task_Cnt to exeed the Max_Task_Cnt."

Q: Is there a way to do this without using a cursor and still meet all of the requirements?

View 2 Replies View Related

Avoiding Cursor - Want Set Based Solution

Feb 13, 2002

Hi there!

Here is my situation:

table 'ReceiptHeader'

IDCustomerIDDateCreated
1225102/06/2002
1332102/09/2002
1444002/15/2002


table 'ReceiptDiscount'

IDDiscountIDReceiptHeaderIDAmount
111210.00
241250.00
311325.00

**a receipt can have multiple discounts**


Table 'ReceiptDetail'

ReceiptHeaderID LineItemIDTotal
121155.33
131145.33
141241.66

**for this example there is only one line item per receipt**



Without using a cursor, i would like to return a result set
like this one below using a set based solution...


ReceiptIDCustomerIDDiscountTotal
1225160.00155.33
1332125.00145.33
144400.00241.66

Thanks,
SF

View 1 Replies View Related

Avoiding The Usage Of DTC

Apr 17, 2007

Hello

I am running an script and the following sentence throws and error because the DTC service is not running in the Remote Server:

insert into MyLocalTable
execute synonym_MyRemoteProcedure @SomeParameter

Since a transaction is not declared within the script, why is the DTC required?
How can I avoid the usage of the DTC? Is there a way to say "this code is not within a distributed transaction"?

Thanks a lot.

View 1 Replies View Related

Avoiding Caching

May 9, 2007

I'm trying to performance tune a procedure and am sort of being thwarted by caching.

When I first run the procedure, it takes a few seconds which is too long in this case. Subsequent executions in Management Studio are nearly instantaneous, though, which I imagine is due to caching and does not reflect the behavior of the procedure in production.

Is there a way to disable caching so that each execution of the procedure in Management Studio will be consistent and reflect the "first run" performance?

View 3 Replies View Related

Avoiding Compilation

Jul 20, 2005

Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny

View 3 Replies View Related

Avoiding Deadlock

May 4, 2006

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)


IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@ClientId, @Hostname, @Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@Hostname, Etc=@Etc
WHERE ClientId=@ClientId
END
But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.
So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.
Instead I'm now getting deadlock errors.
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?
Thanks.
Nate Hekman

View 9 Replies View Related

Avoiding ASPNETDB SQL Database?

Sep 7, 2007

Hello.
I have been developing a small site that has two backend SQL Server databases.  One for my application data and one for the ASPNETDB database that is created by the ASP .NET Configuration utility.
Is it possible to configure the ASP .NET Configuration tool to use my custom database instead of creating a second database called ASPNETDB?
Thanks in advance.
Kev

View 2 Replies View Related

Avoiding SQL Injection With Dynamic SQL

Aug 5, 2004

I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure?

Jason Pacheco

View 2 Replies View Related

Avoiding Query In Loop

Jun 9, 2008

Hello all,

I currently have an asp script that is generating a 12 month rolling report. From asp I'm running a for loop with 12 iterations, each one sending the following query:

select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = " & Mo & " and year(aReportDate) = " & Yr

where the Mo and Yr variables are incremented accordingly.

I actually have 4 sets of data being pulled back to populate a graph, so this results in 48 queries with each page load! Obviously not ideal. So I'm hoping to reduce this to 4 queries. I was playing with the following in enterprise manager:

DECLARE @DT DATETIME
DECLARE @CNT INT
SET @DT = '10/31/07'
SET @CNT = 1
WHILE(@CNT < 12)
BEGIN
select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = month(@DT) and year(aReportDate) = year(@DT)

SET @CNT = @CNT + 1
END

I haven't yet added any logic to increment the date, but my concern is that it looks like it is returning 12 separate results. Is there any way to combine this all into one resultset that will be passed back to my asp script? Hopefully this makes sense?

Suggestions on a completely different approach would also be welcome.

Thanks!

View 2 Replies View Related

Avoiding Subselect Query

Mar 2, 2007

Hi,

Hope someone could help me in revising a long running query. Here is the query

select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)

In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.

Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?

Hope someone could help me with this. Thanks in advance.

-Ruel

View 9 Replies View Related

Avoiding Entries To Transaction Log

Oct 8, 2007

MS SQL Server 2005

I have a table in our system that hold temporary data for doing calculations. It will process several million records in it. each time they forecast our products.....

Is there any way to have the SQL server NOT add these transactions to the transaction log, since I'm going to wipe the data anyway? I'd like to be able to pick and choose the tables that are 'backed up' into the transaction log...

Please advice. Thanks

View 8 Replies View Related

Help With Where Not Exists / Avoiding Loops

Mar 18, 2008

I am trying to figure out an efficient way of comparing two tables of identical structure and primary keys only I want to do a join where one of the tables reveals values for records which have been modified and/or updated.

To illustrate, I have two tables in the generic form:

id-dt-val

For which the 'val' in table 2 could be different from the 'val' in table 1 - for a given id-dt coupling that are identical in both tables.

Does anyone know of an efficient way I could return all id-dt couplings in table 2 which have values that are different from those with the same id-dt couplings in table 1?

NOTE: I am asking this because I am trying to avoid explicit comparisons between the 'val' columns. The tables I am working with in actuality have roughly 900 or so columns, so I don't want this kind of a monster query to do (otherwise, I would simply do something like where a.id = b.id and a.dt = b.dt and a.val <> b.val) - but this won't do in this case.

As a sample query, I have the following script below. When I attempt the where not exists, as you might expect, I only get the one record in which the id-dt coupling is different from those in table 1, but I'm not sure how to return the other records where the id-dt coupling is the same in table 1 but for where modified values exist:


create table #tab1
(
id varchar(3),
dt datetime,
val float
)
go

create table #tab2
(
id varchar(3),
dt datetime,
val float
)
go


insert into #tab1
values
('ABC','01/31/1990',5.436)
go
insert into #tab1
values
('DEF','01/31/1990',4.427)
go
insert into #tab1
values
('GHI','01/31/1990',7.724)
go


insert into #tab2
values
('XYZ','01/31/1990',3.333)
go
insert into #tab2
values
('DEF','01/31/1990',11.111)
go
insert into #tab2
values
('GHI','01/31/1990',12.112)
go


select a.* from #tab2 a --Trouble is, this only returns the XYZ record
where not exists
(select b.* from #tab1 b where a.id = b.id and a.dt = b.dt)
go

drop table #tab1
drop table #tab2
go

I really dont' want to have to code up a loop to do the value by value comparison for inequality, so if anyone knows of an efficient set-based way of doing this, I would really appreciate it.

Any advice appreciated!

-KS

View 7 Replies View Related

Avoiding Time-outs

Jul 20, 2005

The C++ application calls the database to look up property data. Onetroublesome query is a function that returns a table, finding data whichis assembled from four or five tables through a view that has a join,and then updating the resulting @table from some other tables. Thereare several queries inside the function, which are selected accordingto which parameters are supplied (house #, street, zip, or perhaps parcelnumber, or house #, street, town, city,...etc.). If a lot of parametersare provided, and the property is not in the database, then several queriesmay be attempted -- it keeps going until it runs out of queries or findssomething. Usually it takes ~1-2 sec for a hit, but maybe a minute insome failure cases, depending on the distribution of data. (~100 milproperties in the DB) Some queires operate on the assumption the input datais slightly faulty, and take relatively a long time, e.g., if WHEREZIP=@Zip fails, we try WHERE ZIP LIKE substring(@Zip,1,3)+'%'. Whileall this is going on the application may decide the DB is never going toreturn, and time out; it also seems more likely to throw an exception thelonger it has to wait. Is there a way to cause the DB function to fail ifit takes more than a certain amount of time? I could also recast it asa procedure, and check the time consumed after every query, and abandonthe search if a certain amount of time has elapsed.Thanks in advance,Jim Geissman

View 3 Replies View Related

Avoiding Divide By Zero In Report

Jun 8, 2007

What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?



IIF(divide by zero, display nothing, else display computed result)...??

View 6 Replies View Related

Avoiding Nested Cursors

Dec 4, 2007

I have a Master/Detail table setup - let's call the master "Account" and the detail "Amount". I also have a "black box" stored procedure (BlackBox_sp) which carries out a lot of complex processing.

What I need to do is, for each Account, I need to iterate thtough it's Amount records and call the black box each time. Once I've finished going through all the Amount records, I need to call the black box again once for the Account. This must be done with the Account & Amount rows in a specific order.

So I have something along the lines of





Code Block

DECLARE Total int

DECLARE Account_cur
OPEN Account_cur
FETCH NEXT FROM Account_cur
WHILE FETCH_STATUS = 0
BEGIN

SET Total = 0


DECLARE Amount_cur
OPEN Amount_cur
FETCH NEXT FROM Amount_cur
WHILE FETCH_STATUS = 0
BEGIN

SET Total = Total + Amount

EXEC BlackBox_sp (Amount)
END
CLOSE Amount_cur

EXEC BlackBox_sp (Total)

END
CLOSE Account_cur

Any tips on another approach would be appreciated given the contraints I have.

Greg.

View 1 Replies View Related

Best Way To Insert New Records Avoiding Concurrency

Mar 27, 2008

I have web site when people orders through website at same time, a problem can be arrive when allocating next primary key value to new record, using maximum number of records +1
how to avoid this problem and insert to sql server
please give me your ideas

View 16 Replies View Related

Avoiding Index While Fetching Data

Mar 7, 2001

Hi there,
I'm using a query to fetch data from a table where one of the criteria is IN(...) clause for the key column of the table.Now the data being retrieved is ordered by the key column of the table even though I haven't specified any order by clause.
I want to know if there a way in which the data being fetched is in the order of my IN(...) clause.


Thanx
Aby

View 3 Replies View Related

Avoiding &#39;System Creation Indexes&#39; ?

Jun 26, 2002

Hi,

Can anybody help me how to Stop this 'System Creation Indexes' (Index Name like 'WA%')?.

Is there any method is available to delete the Existing 'System Indexes'?.

thanks,
Srini

View 1 Replies View Related

Temporarily Avoiding Writing Into Trans Log

Mar 9, 2007

Guys,

Is there a way to temporarily disable logging into the transaction log.

In our system, we perform purging of our database every night, where the purging consists of 2 steps:

1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.

2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)

During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.

I really don't care about being able to recover this data.

I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.

However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].

So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?


Thanks a lot

View 13 Replies View Related

Turning Off Logging / Avoiding Huge Log File

Nov 15, 1999

Help,

Have a user who needs many data changes to a huge table with over 2 million rows.
He hopes to do this by running a series of queries.

Last time our log grew gargantuan. I know 7.0 is wonderful & dynamic, but after
an hour of rambling through books online I'm more confused than ever.

Is there a way we can code into his query at intervals any hard coded checkpoints
or log backups? Do we need to dbcc shrinkfile the logfile?

Thanks,

Mark Blackburn
mark@mbari.org

View 1 Replies View Related

Selecting From Multiple Tables Avoiding Duplicates

Sep 6, 2006

Hi

I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.

I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?

Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!

View 7 Replies View Related

Avoiding Locks While Inserting / Deleting From A Table?

Oct 31, 2007



Hello -- I have a Huge Table Tab1 (160 Mill Rows). I have a script that Inserts and Deletes from this Tab1 (This Script runs for a good 4/5 Hrs).

When the script is being executed, No other session can even do a "SELECT TOP 10 * FROM Tab1"

How do i avoid this? Any "NOLOCK" Keywords i should be specifying in my Script?

Thanks in advance

View 3 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

SQL Server 2012 :: Avoiding If Conditional To Reduce CPU Usage

Jun 23, 2015

I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.

CREATE FUNCTION prici.[fn_pricipalamt]
(
-- Add the parameters for the function here
@Tcode char(10),
@SecTypeCode1 char(10),
@SecTypeCode2 char(10),
@TradeAmount float,

[Code] .....

View 2 Replies View Related

Avoiding Clear Text Passwords And Editing Of Packages

Oct 26, 2006

Hi!

I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.

In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.

The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.

Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

View 5 Replies View Related

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View 1 Replies View Related

Cursor Inside A Cursor

Oct 5, 2004

I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View 1 Replies View Related

Client Side Cursor Vs Sever Side Cursor?

Jul 20, 2005

I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved