Cursor, Query, View And Recordset

Jul 20, 2005

Could someone help me by answering the questions below?

What's a cursor?
What's difference between Query and View?
Is a RecordSet just part of a table? Can it be part of a query of view?
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()"?

Thanks for your help!

View 4 Replies


ADVERTISEMENT

SQLClient Cursor/recordset

May 15, 2008

hi, I want to work with a recordset/cursor with sqlserver sqlclient, I have a sqlconnection and sqlcommand, I need to scroll forward and back.

View 1 Replies View Related

MS SQL Server View Linked Into MSAccess Is Not Updateable Recordset

Jul 29, 2004

Hi All,
How can I link a view into MS Access from MS Sql Server, that I can update as a tabble.
Thanks
Laszlo

View 3 Replies View Related

How Can I Get Vb6 To Update Offline Recordset When View Has Defined An Attribute As AdFldUnknownUpdatable?

Aug 21, 2006

hopefully, someone has already solved this problem

I have been assigned an application that maps data from access databases into a view in Sql Server 2005. A vb6 application does a select on the view. The recordset is then modified by the application, and eventually, the original tables will be updated with the new data. Some of the fields in the view are the result of calculations ( i.e. adding multiple varchar fields together to produce one complex varchar result ). Any of the fields that are calculated in the manner previously described have the adFldUnknownUpdatable bit set in the attributes of the recordset, and consequently, you get a -2147217887 error ( multiple-step operation .... ) when you try to change the value of the field in the recordset. Does anyone know how to get around this problem?

View 1 Replies View Related

How To Do This Without Using Cursor/SP/View

Sep 28, 2005

Hi, everyone. I'm a beginner so my question might be silly.

If I have a table as below:

Letter No.
A 1
B 2
C 2

How to write a query without using Cursor/SP/View to return:

Letter No.
A 1
B 2
B 3
C 4
C 5


Thanks in advance

View 8 Replies View Related

Create View From Cursor

May 12, 2006

I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend

View 2 Replies View Related

Updating A Partitioned View In A Cursor

Jan 13, 2004

I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?

Thanks

View 2 Replies View Related

Query With Recordset

Jun 22, 2004

Hello,

I am connecting to the database as following:
set con = server.createobject("adodb.connection")
con.open "connectionstring"
set rs = con.execute("select * from tablename")

I am able to display the records but if I want to give adopenstatic to the above connection, how can I do so?

Thanks in advance,
Uday.

View 1 Replies View Related

Use A Whole Recordset For A 'LIKE' Query (was Quickie)

Jan 16, 2007

trying to search a DB for all records within a range.
I have a recordset containing about 10 postcodes and I need to find all the records containing any one of those postcodes
currently I have:
sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & left(rsPostcodeResult, 4) & "%'"
but I am getting 'type mismatch' error.

Thanks for looking
Mark

View 11 Replies View Related

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare

Oct 17, 2006

This one has me stumped.

I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.

There error generated is:

Server: Msg 16957, Level 16, State 4, Line 3

FOR UPDATE cannot be specified on a READ ONLY cursor



Here is the cursor declaration:



declare some_cursor CURSOR

for

select *

from part_view

FOR UPDATE



Any ideas, guys? Thanks in advance for knocking your head against this one.

PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.

View 2 Replies View Related

Difficult Query: Return Recordset From Concatenated Strings?

Jul 20, 2005

Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray

View 7 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Data Access :: Recordset Returned By Query Have A Status Of Closed?

Oct 8, 2015

I'm running the following SQL query from LabVIEW, a graphical programming language, using the built in capabilities it has for database connectivity:

    DECLARE @currentID int
    SET @currentID = (SELECT MIN(ExperimentID) FROM Jobs_t WHERE JobStatus = 'ToRun');
    UPDATE [dbo].[Jobs_t]
    SET [JobStatus] = 'Pending'
    WHERE ExperimentID = @currentID;
    SELECT @currentID AS result
<main.img>

This is the analogous code to main() is a C-like language. The first block, which has the "Connection Information" wire going into it, opens a .udl file and creates an ADO.NET _Connection reference, which is later used to invoke methods for the query.

<execute query.img>

This is the inside of the second block, the one with "EXE" and the pink wire going into it. The boxes with the gray border operate much like "switch" statements. The wire going into the "?" terminal on these boxes determines which case gets executed. The yellow boxes with white rectangels dropping down are invoke nodes and property nodes; they accept a reference to an object and allow you to invoke methods and read/write properties of that object. You can see the _Recordset object here as well. <fetch recordset.img>

Here's the next block to be executed, the one whose icon reads "FETCH ALL". We see that the first thing to execute on the far left grabs some properties of the recordset, and returns them in a "struct" (the pink wire that goes into the box that reads "state"). This is where the code fails. The recordset opened in the previous VI (virtual instrument) has a status of "closed", and the purple variant (seen under "Read all the data available") comes back empty.

The rest of the code is fairly irrelevant, as it's just converting the received variant into usable data, and freeing the recordset reference opened previously. My question is, why would the status from the query of the recordset be "closed"? I realize that recordsets are "closed" when the query returns no rows, but executing that query in SSMS returns good data. Also, executing the LabVIEW code does the UPDATE in the query, so I know that's not broken either.

View 3 Replies View Related

Query From A Cursor

Apr 13, 1999

hi, I created a cursor, can I use "select * from cursorname in the query window?

thanks
Ali

View 1 Replies View Related

Query Without Using Cursor

May 2, 2006

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakri

View 8 Replies View Related

How To Write SQL Query And Not Cursor ?

Oct 10, 2004

Please help me to get the required result:
For each IDS in table1
- change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3)
- in table2, find the maxVal for each number
- disply the table1..ids, number, table2..maxVal & table1..idsDesc,
order by table1..ids, table2..maxVal & IntValue

I have 2 tables, over milin records each. The Simplified versions of the tables looks like that:

create table table1 (ids varchar(100), idsDesc varchar(100))
go
insert table1 select '1,2,3', 'Description 1'
union all select '2,3,4', 'Description 2'
union all select '1,7', 'Description 3'
union all select '16,3,8', 'Description 4'
union all select '2,5,6,1', 'Description 5'
go

create table table2 (ids int, maxVal int)
go
insert table2 select 1, 10
union all select 2, 6
union all select 3, 12
union all select 4, 11
union all select 5, 66
union all select 6, 4
union all select 7, 3
-- union all select 8, 5 -- no value for 8
union all select 9, 6
union all select 16, 12
go

I have also function that returns table variable of numbers delivered from given string:
create function dbo.fn_StrToIntValues ( @str varchar(1000) )
returns @numsTbl table (IntValue int not null)

The command
select * from dbo.fn_StrToIntValues('1,2,33')

Returns --> intValue
1
2
33

Can I use SQL query and not cursor to get the following result ?

Required Output :
ids IntValue maxVal idsDesc
--------- -------- ------- ---------------
'2,5,6,1' 5 66 'Description 5'
'16,3,8' 16 12 'Description 4'
'2,3,4,9' 3 12 'Description 2'
'1,2,3' 3 12 'Description 1'
'1,7' 1 10 'Description 3'

View 2 Replies View Related

Complex Query With Cursor

Sep 12, 2014

SELECT debit.ACCOUNT_NO, debit.Serviced_Amt,credit.Tran_Amt,credit.Serviced_Flag
FROM tbl_Interest_Debit as debit
inner join
tbl_Credit as credit
on debit.ACCOUNT_NO=credit.Account_No
order by credit.TRANSACTION_VALUE_DATE

[code]....

I want that service_amount should be subtracted from tran_amt until service_amount become zeroOnce service_amount becomes zero service_flag should be changed to 1.using with cursor.

View 2 Replies View Related

Cursor - Query In Procedure To Get Maxdate

Feb 29, 2008

i m new in sql...and i have this procedure..which have cursor inside..

1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table

but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...


create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON

DECLARE @MaxDate datetime
DECLARE @Date datetime


select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general

where name = @name AND dated = @MaxDate


DECLARE CUR1 CURSOR FOR

SELECT @DATE FROM #tempt

OPEN CUR1

FETCH NEXT FROM CUR1 INTO @Date

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1


--DROP TABLE #Date1

FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end

View 3 Replies View Related

Error: Cursor Not Returned From Query

May 26, 2006

I'm a really beginner about sql2000.During my test I have created the following query. It's works ok until Ido't add the code included in section A, when I add it the i obtain theerror: Cursor not returned from queryAnyone can help me?Thanks Carlo M.set nocount onIF OBJECT_ID('storico_big') IS NULL --- section A begincreate table storico_big( data datetime,bcarrier varchar(20),bda CHAR(30),bzone char(50),bdur int) ;insert into storico_big --- section Aendselect top 10000adetdate,bcarrier,bda,bzone,bdurfrom pp_cdr (nolock)whereadetdate < :data_fin and adetdate > :data_in order by adetdateset nocount off------ end of query

View 3 Replies View Related

Is It Possible To Use This One Query To Update The QUAL_SCORE Field Without Using Cursor

Dec 7, 2007

UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25)  +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;     
is it possible to use this one query to update  the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
 
Thanks

View 2 Replies View Related

Query Results Couble Spaced When Using Cursor

Feb 8, 2001

Just upgraded from 6.5 to 7 and in query analyzer I get double spaced results on queries that previously single-spaced. Installed 2000 QA and still get same results. Is there a setting that we are missing or a workaround to this?

Note ==> The cursor query returns single spaced results when ran alone.

Test code below:

DECLARE @pol char(10)
DECLARE testcursor CURSOR FOR
SELECT policy_no FROM policy_history
WHERE policy_chg_end_dt is null and policy_deleted_sw = 0

OPEN testcursor
FETCH NEXT FROM testcursor INTO @pol

WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @pol
FETCH NEXT FROM testcursor INTO @pol
END

CLOSE testcursor
DEALLOCATE testcursor

View 1 Replies View Related

Declare Cursor Based On Dynamic Query

Sep 18, 2006

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working



Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test



This code is not working.



please suggest



Nitin

View 12 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

T-SQL (SS2K8) :: Convert Cursor To Recursive CTE Or A Normal Query?

Sep 25, 2015

I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor

If I convert it to a Function I get the below error message

Invalid use of a side-effecting operator 'EXECUTE STRING' within a function

converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.

CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)

[code]....

View 5 Replies View Related

SQL Server 2012 :: How To Do OPENROWSET Query Within Cursor Using Variables

Oct 22, 2015

I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.

DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0

PRINT @Found

View 2 Replies View Related

Cursor Works In Query Analyzer But Not In Stored Procedure

Mar 7, 2008



Hi i have a script works in sql query analyzer;


declare @id decimal


declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;

Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;

select @id
this script turns me a value.

i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO

my c# code using stored procedure is below;






Code Snippet
try
{

OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{

MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{

MessageBox.Show(ex.Message);
}


The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?

View 4 Replies View Related

Support For OLEDB Scrollable Updateable Cursor Via Query

Jul 5, 2007

Hi,

is there any update on the subject?

Jet has it for ages.



I want to use Scrollable/Updateable Cursors & OLEDB Rowsets opened via Select query.

Last time I have heard that MSFT is planning to add this in the future.



Is there a beta version that I can try?



Thanks,

Konstantin

View 8 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 :: Cursor Stop Query At Specific Time?

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2

[code]....

View 1 Replies View Related

Dynamic Query, Local Cursor Variable And Global Cursors

Oct 3, 2006

Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View 3 Replies View Related

Query View Vs Query Tables Directly

May 19, 2008

Greetings,

I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.

I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?

Thanks in advance for your replies.

View 3 Replies View Related

SQL Server Admin 2014 :: Cursor Stop Query At Specific Time

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;

[Code] ....

View 8 Replies View Related







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