I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'
Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
but I can't seem to come up with the right statement.
Any help greatly appreciated.
Thanks,
Judith
The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance
R.
The code:
Code Snippet
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author: -----
-- Create date:
-- Description: inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
-- Add the parameters for the stored procedure here
@importedReportID int,
@authCode varchar(12)
AS
BEGIN
DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--IF (@authCode <> 'TX-TEC')
--BEGIN
-- SET @errmsg = 'Unsupported reporting format:' + @authCode
-- RAISERROR(@errmsg, 11, 1);
--END
DECLARE srcRecsCursor CURSOR LOCAL
FOR (SELECT
ImportedRecordID
,ImportedReportID
,AuthorityCode
,[ID]
,[Field1] AS RecordType
,[Field2] AS FormType
,[Field3] AS ItemID
,[Field4] AS EntityCode
,[Field5] AS LastName
,[Field6] AS FirstMiddleNames
,[Field7] AS Title
,[Field8] AS Suffix
,[Field9] AS AddressLine1
,[Field10] AS AddressLine2
,[Field11] AS City
,[Field12] AS [State]
,[Field13] AS ZipFull
,[Field14] AS OutOfStatePAC
,[Field15] AS FecID
,[Field16] AS Date
,[Field17] AS Amount
,[Field18] AS [Description]
,[Field19] AS Employer
,[Field20] AS Occupation
,[Field21] AS AttorneyJob
,[Field22] AS SpouseEmployer
,[Field23] As ChildParentEmployer1
,[Field24] AS ChildParentEmployer2
,[Field25] AS InKindTravel
,[Field26] AS TravellerLastName
,[Field27] AS TravellerFirstMiddleNames
,[Field28] AS TravellerTitle
,[Field29] AS TravellerSuffix
,[Field30] AS TravelMode
,[Field31] As DptCity
,[Field32] AS DptDate
,[Field33] AS ArvCity
,[Field34] AS ArvDate
,[Field35] AS TravelPurpose
,[Field36] AS TravelRecordBackReference
FROM ImportedNativeRecords
WHERE ImportedReportID IS NOT NULL
AND ReportType IN ('RCPT','PLDG')
ORDER BY ImportedRecordID -- this should work but gives syntax error!
);
END
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.
I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?
I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.
First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?
Second problem: What do I set the value of the parameter to?
The code looks like:
CallableStatement cstmt = myConnection.prepareCall(sQuery);
cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?
cstmt.setNull(1, Types.OTHER); // What is the right type?
if (cstmt.execute()) {
ResultSet rs = cstmt.getResultSet();
}
Execution results in a NullPointerException from the driver.
What am I doing wrong?
Thanks for your assistance.
Jon Weaver
Is there any way to create a cursor, based on a dynamically created select_statement? Something like:
DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect
where @strSelect is previously declared as let's say varchar.
I don't want to create a stored procedure for this.
Thanks!
I keep getting the message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'declare'.
What am I doing wrong?
declare @dbname varchar(8000),
declare @countyname varchar (200) ,
declare @sql varchar(8000)
declare county_name cursor for
select distinct county from Zipcodes
open county_name
fetch next from county_name
into @countyname
declare dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open dbname_name
fetch next from dbname_name
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end
set @sql = ''
fetch next from dbname_name into @dbname
fetch next from county_name into @countyname
CLOSE county_name
DEALLOCATE county_name
CLOSE dbname_name
DEALLOCATE dbname_name
Hello..
Can you declare a cursor with dynamic SQL?
I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.
these attempts did not work:
DECLARE crsCursor CURSOR FOR @vchrSQL
DECLARE crsCursor CURSOR FOR (@vchrSQL)
Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?
Is Declare Cursor same as Create Cursor and if not what is the major difference?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.
CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View 5 Replies View RelatedHello,
I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable).
Something like this:
DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable
i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)
Not sure if i am doing this right all together.
any help would be greatly appreciate.
The below cursor is giving an error
DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)
DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Why does the delete statement give an error ?
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
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.
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.
So! Here's the statement that works:
======================
DECLARE @fixeddate datetime
DECLARE @CVG_ELECT char(1)
DECLARE @Effdt datetime
DECLARE EFFDTS CURSOR FOR
SELECT Z.EFFDT, COVERAGE_ELECT
FROM PS_LIFE_ADD_BEN Z
WHERE Z.EMPLID = '1000'
AND Z.EFFDT <=
GETDATE()
AND Z.PLAN_TYPE = '20'
ORDER BY Z.EFFDT DESC
OPEN EFFDTS
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
WHILE @@FETCH_STATUS = 0
BEGIN
if @CVG_ELECT <> 'E'
break
ELSE
SET @fixeddate = @Effdt
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
END
CLOSE EFFDTS
DEALLOCATE EFFDTS
PRINT @fixeddate
======================
If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).
Is it possible to encapsulate this inside a select statement?
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
I've created a function that converts the rows of a column into a delimited string using a passed cursor and delimiter character. In the past I did this in Oracle and called it as shown in the following example:
SELECT Table1.ID, Table1.FirstName, Table1.LastName, fnDelimitRows(CURSOR(SELECT Table2.CourseName FROM Table2 WHERE Table2.StudentID = Table1.ID), ',') AS AssignedCourses FROM Table1
23 John Smith CS101,MT200,BIO100
43 Julio Johnson CS200,ENG100,MT300
How would I pass a cursor into a function in SS like I did above in Oracle?
Thanks!
Hi everyone I am trying to pass a parameter to a USE command within the body of a cursor. I keep getting an error. Here is my code.
Code Snippet
DECLARE @SQLText AS NVARCHAR(4000)
Declare @SQLText2 as NVARCHAR (2000)
DECLARE Cursor_Trial CURSOR FOR SELECT name FROM sys.sysdatabases order by name
Declare @name as varchar(255)
Open Cursor_Trial
FETCH NEXT FROM Cursor_Trial INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Use @name --------This is where I'm getting the error
Set @SQLText2 = @name
select @SQLText2, * from sys.database_principals
where type_desc='DATABASE_ROLE'
and name = 'xxxxx'
FETCH NEXT FROM Cursor_Trial INTO @name
END
CLOSE Cursor_Trial
DEALLOCATE Cursor_Trial
As you can see I am trying to pass the next database name into the body of the cursor so that the query will pull the role information only for the current database in the loop. Any thoughts or tips? Is there a better way to accomplish what I'm trying to do here?
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View 4 Replies View RelatedHi...
I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria
I am using SQL mail.
I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery
When i have the following code to run my cursor
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.
What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.
Please help!!!
Thanks much indeed
Ramesh
Hi All, Can anyone please help?
TableA has data as below:
ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s
I have to convert the rel_code into a specific manner so the data will look as below in TableB:
ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31
Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.
I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.
Thanks in advance.
Jannat.
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?
Thanks !
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code:
DECLARE @avg varchar(50)
DECLARE @cur_avg CURSOR
SET @cur_avg = CURSOR FOR
select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id
this is the update statement:
Code:
OPEN @cur_avg
FETCH @cur_avg INTO @avg
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE tbl_app_monitoring_archive
SET average = @avg
FETCH @cur_avg INTO @avg
END
is it also possible to do this without the cursor ?
I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).
Here is my code:
Declare @databaseName varchar(50)
Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases
OPEN c_getDatabaseName
FETCH NEXT from c_getDatabaseName into @databaseName
While @@FETCH_STATUS = 0
[Code] ....
How to get USE to take the variable value ?
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View 4 Replies View RelatedHi everyone,
The following snippet of code returns something like that: string;string1;string2
Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task)
Thanks a lot,
declare @anex as varchar(500)
declare @anex2 as varchar(700)
set @anex2 = ''
DECLARE anexos CURSOR FOR
SELECT [Ruta] + [Fichero] as ANEXO
FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO
WHERE ENVIADO = 0
OPEN anexos;
FETCH NEXT FROM anexos INTO @anex
WHILE @@FETCH_STATUS = 0
BEGIN
IF @anex2 = ''
begin
set @anex2 = @anex
end
else
begin
set @anex2 = @anex2 + ';' + @anex
end
FETCH NEXT FROM anexos INTO @anex
END
CLOSE anexos
DEALLOCATE anexos
Hello,
I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.
I have a temp table populated with the below headers and their associated data.
The headers are as follows:
ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.
The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.
One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.
The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.
The current script I have written is below. If you can offer any help I would greatly appreciate it.
Code SnippetDeclare @ItemID VarChar(30),
@QtyOnHand Decimal (16,8),
@WhseID VarChar (6),
@SumRcvd Int,
@TranID VarChar(30),
@TranDate DateTime,
@QtyRcvd Decimal (16,8),
@UnitCost Decimal (16,8),
@ItemKey Int,
@WhseKey Int,
@ShortDesc VarChar (40),
@StdCost Decimal (16,8)
DECLARE Temp_cursor CURSOR FOR
SELECT TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost
FROM #Temp1 tem
OPEN Temp_cursor
FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost
WHILE @@FETCH_STATUS = 0
BEGIN -- 0
Insert Into #Temp3
(TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost)
Values
(@TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost)
FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost
adding a if statement within my cursor.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
[code]....
The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View 6 Replies View RelatedWhen I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
Hi everyone,
I have been trying to perform the following task:
Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:
Database name
Table name
Column name from that table with a clustered index
I have already created the following script which displays all the databases for a given server:
declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name
open testCursorForDB
fetch next from testCursorForDB
into @DBname
while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end
close testCursorForDB
deallocate testCursorForDB
I also have created the following query which will display all the table and column names which have a clustered index for a given database:
select object_name(i.id) as TableName,
i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'
However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.
Any help is greatly appreciated
Thanks!
I have a SQL statement stored in a SQL varriable (after a lot of conditions)
Code:
declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'
Now, I want to create a cursor to recalculate some values
I've tried:
Code:
declare cur_T cursor for exec(@sql) open cur_T
but it doesn't work.
Can I have another way to do that???
col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.
Can any one reply for this..............
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.
I am not using / importing any dlls.
I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.
I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.
First time when I bind paramaters using SQLBindParameter it works perfect.
When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.
If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.
I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.
Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?
Thanks