UPDATE CURSOR - Declaring And Use.

Jul 20, 2005

I need to do something relatively simple�

I need to update a table using a cursor. (I may have to create a
stored procedure for doing this�)

I need to declare an update cursor, fetch the cursor and update the
data (and presumably close the cursor and de-allocate it�

The update query is as follows� Would anyone there know how to
declare the cursor for update and use it?

UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)

I need to know how to declare the cursor and fetch it.

Can anyone give me an example of the code I need for the SQL Server?

Thanks!

View 4 Replies

Declaring A Cursor On A Temporary Table

Jan 27, 2004

How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :

Select Name, Age Into #TempPerson From Person

View 7 Replies View Related

How To Call A Procedure While Declaring A Cursor

Apr 30, 2004

HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKS

View 14 Replies View Related

Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?

Sep 29, 2015

I cannot find the problem with this function.

ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;

[code]....

I am getting the following error on line 75:

Select statements included within a function cannot return data to a client.

This is happening when declaring TrackUpdateCursor

The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?

View 20 Replies View Related

Update Cursor

Dec 20, 2001

hi,

can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'.

i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it.

I have no experience with cursors.

Thanx in advance.

View 2 Replies View Related

Using Cursor For Update Statement?

May 9, 2012

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 ?

View 5 Replies View Related

Update Record In A Cursor

Apr 20, 2004

Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :

VB: Table("Fieldname") = Value

----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1

--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END

CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO

View 1 Replies View Related

How Do U Update A Row In A Table Using Cursor ?

Mar 11, 2008

How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?

View 1 Replies View Related

Error On Update, But Not When Using Cursor

Dec 7, 2006

I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).

When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).

The problem thus cannot be with the function since it works just fine if I do like this:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001

And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).

But when I do this it crashes:

UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)

For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.

Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.

The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).

Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).

Is there a certain tweak I can do to make things work out, or should I code things differently?

Thanks!

View 1 Replies View Related

Cursor Update Creating Nulls

Feb 1, 2008

So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.

Code:

use db7021
go

select * from products
go

declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription

open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end

close thecursor
deallocate thecursor

select * from products
go

Which produces the output:

Code:

id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>

(4 row(s) affected)

sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89

(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL

(4 row(s) affected)

I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.

Maybe I've missed something in the syntax for cursors?

View 2 Replies View Related

Variable As Field Name In CURSOR FOR UPDATE

Dec 10, 2005

I'm trying something like:

UPDATE tbl SET @varFieldName = @varValue

The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.

I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.

To get the field name, I :

SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'

Thanks,
Carl

View 2 Replies View Related

T-SQL (SS2K8) :: Cursor Table Update

May 6, 2015

DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers

[code]...

How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.

View 1 Replies View Related

One Statement Update - Join, No Cursor ?

Jul 20, 2005

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 Related

Transact SQL :: Update Statement With Cursor

Jun 16, 2015

When 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] ....

View 5 Replies View Related

Read The Csv In A Cursor Instead Of Bulk Update

Feb 13, 2008

Hello,
I am trying to read in from a csv file which works like this:

DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'

SET NOCOUNT ON

EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF

IF @doesExist = 1

BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')

What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?

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

Cursor Count Loop - Update Table

Jul 6, 2000

I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.

The table looks something like this.

inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT

I need the cursor to go through and number each line, something like this.

inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3

Any help is greatly appriciated.

Thanks

View 1 Replies View Related

SQL 2012 :: How To Update And Join Table With Cursor

Jul 15, 2014

I have table A

|account | Unmort |
| A |10.000.000 |

and a Table B

|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |

how to update to be like this??

|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |

for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...

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

Declaring A Variable

Feb 6, 2007

I have the below code and have two questions.
1) With the current delete control, the system breaks stating: System.Data.SqlClient.SqlException: Must declare the variable '@doc_area_id'.
2) Currently the Area Type column brings up all records set as '1'. It shows the value '1' for each column. I would like it to state the text 'Shared Area' under the Area Type Column where it is set as '1' in the database.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlManageSharedArea" CellPadding="4" ForeColor="#333333" BorderStyle="Solid" BorderColor="Black" BorderWidth="1px">
<Columns>
<asp:BoundField DataField="doc_area_name" HeaderText="Area Name" SortExpression="doc_area_name" />
<asp:BoundField DataField="doc_area_type" HeaderText="Area Type" SortExpression="doc_area_type" />
<asp:CheckBoxField DataField="doc_area_default" HeaderText="Default" SortExpression="doc_area_default" />
<asp:HyperLinkField HeaderText="Edit" NavigateUrl="~/Admin/EditDocumentArea.aspx" Text="Edit" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:HyperLinkField>
<asp:CommandField ShowDeleteButton="True" HeaderText="Delete" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:CommandField>
</Columns>

<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlManageSharedArea" runat="server" ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"
SelectCommand="SELECT [doc_area_id], [doc_area_name], [doc_area_type], [doc_area_default] FROM [document_area] WHERE [doc_area_type] = 1"
DeleteCommand="DELETE FROM document_area WHERE [doc_area_id] = @doc_area_id">
</asp:SqlDataSource>

Any help would be greatful!

View 1 Replies View Related

Declaring Variables In SQL CE

Dec 3, 2007

Hi, i am trying to run the following query in SQL Server CE:

SELECT @V1=CAST(SCOPE_IDENTITY() AS bigint)

SELECT @V1

INSERT INTO [ResourceItem] ([PackageId],[ResourceXml]) VALUES (@V1,@V6)

It gives me an error "near SELECT".

I replaced SCOPE_IDENTITY() with @@IDENTITY but it didnt help.

I think it is something related to storing values such as @foo = something.

How can i declare variables in SQL CE?

Pls help
Thank You

View 1 Replies View Related

Declaring A Variable

May 9, 2007

I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:

We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.

Do I need to declare a parameter, or in SQL is there such thing as a variable?

So if I have:

DECLARE @sql nvarchar(4000)

SELECT AdHocSQL from TheTable

SET @sql=AdHocSQL

Would this work? Is this syntatically correct? Or should I be doing this some other way?

The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.

Thanks for the information.

View 3 Replies View Related

T-SQL (SS2K8) :: Declaring Value To A Variable

Oct 22, 2014

I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.

See below.

Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1

[Code] .....

View 2 Replies View Related

Problem Declaring Cursors

Feb 26, 2008

I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure:
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27
A cursor with the name 'MyCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31
The cursor is already open.
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37
A cursor with the name 'vwCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38
The cursor is already open.

Here is the code causing the problem:

CREATE PROCEDURE [dbo].[BatchNonMons2] AS

BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */

/* Define every field you want to output */
DECLARE @Batch_Type int
DECLARE @Batch_Num int
DECLARE @Sys varchar (4)
DECLARE @Prin varchar (4)
DECLARE @Account_Num varchar(16)
DECLARE @Tran_Code varchar(3)
DECLARE @Clerk_Code varchar(3)
DECLARE @Memo_Text varchar(57)
DECLARE @SubTrans varchar(2)
DECLARE @SubTrans_2 varchar(1)
DECLARE @Terminal_Id varchar(3)
DECLARE @Op_Code varchar(2)
DECLARE @Last4 varchar(6)

DECLARE @vwSys varchar (4)
DECLARE @vwPrin varchar (4)

/* Define Cursor */
DECLARE MyCursor cursor
For SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number, Transaction_Code,
Clerk_Code, Memo_Text, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.tblNonMon_Daily_Trans

Open MyCursor; /*Works like an ARRAY*//*holds current record */
FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code

Declare vwCursor cursor
FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin

/* When @@Fetch_Status = 1 EOF has been reached */
WHILE @@Fetch_Status = 0
BEGIN
WHILE @vwSys = @Sys
BEGIN

Any input would be appreciated.
Thanx,

View 2 Replies View Related

Declaring USER_NAME() As SQL Variable

Jul 20, 2005

Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1

View 5 Replies View Related

Problem Declaring Cursors

May 2, 2008

I am entering the following:

declare @iAbsenceCategoryID int;

declare @dEntitlementRenewal datetime;

declare @iAbsenceUserID int;

declare @iMinutesUsed int;

declare @iMinutesNotUsed int;

declare @iEntitlement int;

declare cr cursor forward_only

for

select iabsencecategoryid,

dentitlementrenewal

from TblTAAbsenceCategories

open cr;

fetch next from cr into

@iAbsenceCategoryID, @dEntitlementRenewal

WHILE @@FETCH_STATUS = 0

BEGIN

fetch next from cr into

@iAbsenceCategoryID, @dEntitlementRenewal

if (DATEPART("Month",(GETDATE()))) = (DATEPART("Month", (@dEntitlementRenewal))) and (DATEPART("Day",(GETDATE()))) = (DATEPART("Day",(GETDATE())))

declare usercr cursor FORWARD_ONLY for

select iAbsenceUserID from TblTAAbsenceUsers

where iAbsenceCategoryID = @iAbsenceCategoryID

fetch next from usercr into

@iAbsenceUserID

WHILE @@FETCH_STATUS = 0

BEGIN

fetch next from usercr into

@iAbsenceUserID

select @iMinutesUsed = sum(iMinutesRequested)

from tblTAAbsenceHolidays

where iAbsenceUserID = 13 and iAbsenceActionID != 2

select @iEntitlement = iHolidayEntitlement from TblTAAbsenceUsers where iAbsenceUserID = 13;

set @iMinutesNotUsed = (@iEntitlement) - (@iMinutesUsed);

update TblTAAbsenceUsers

set iMinutesNotUsed = @iMinutesNotUsed

where iAbsenceUserID = @iAbsenceUserID;

END;

END;

close usercr;

close cr;

DEALLOCATE usercr;

DEALLOCATE cr;

and i get the following error message:

Msg 16916, Level 16, State 1, Line 26

A cursor with the name 'usercr' does not exist.

Msg 16916, Level 16, State 1, Line 57

A cursor with the name 'usercr' does not exist.

Msg 16916, Level 16, State 1, Line 60

A cursor with the name 'usercr' does not exist.

I cant see why its not allowing me to declare the usercr cursor, can anyone tell me where im going wrong? thanks!

View 13 Replies View Related

Declaring Public Variables

May 30, 2007

I am trying to use variables that are declared in a report that I am created in SSRS 2005.

For example:

Declare @Month as int

Set @Month = case when month(getdate()) = 1 then 9 else month(getdate()) - 3

When I do this, I get an error message, 'The declare cursor SQL construct or statement is not supported.'

So how do i properly declare a variable that can be used through all my dataset?

Thanks, Iris

View 10 Replies View Related

Declaring A Sql Query To A Variable...

Sep 10, 2007

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

View 5 Replies View Related

Declaring A Tablename In A Sproc As A Parameter

Oct 2, 2006

HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition

View 5 Replies View Related

Trouble Declaring Hierarchy Using FOR XML EXPLICIT

Jan 31, 2006

I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>

Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT

I'm receiving the foollowing error:
Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?

Thanks in advance!

View 1 Replies View Related

Trouble Declaring Hierarchy Using FOR XML EXPLICIT

Jan 31, 2006

I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>

Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_descas [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULLas [grouptype!1!scheme!element],
NULLas [grouptype!1!typevalue!element],
NULLas [description!1!short!element],
NULLas [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destinationas [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_codeas [description!2!short!element],
@p_term_descriptionas [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT

I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.

Is there anyway to have two different child notes off of the same parent node?

Thanks in advance!

View 1 Replies View Related

Declaring A Composite Attribute In Sql Server

Mar 28, 2008

hi guys i have a question ..... i would like to know how to delare a compound attribute in this case would be Birthinfo onto a sql server script. from this diagram . thanyou guyt in advance and any suggestion or help would be greatly appreciated .....

View 10 Replies View Related

Setting/declaring Variables Plus Concatenation

Mar 12, 2008

So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?

DECLARE@Prefix VARCHAR(10),
@First_Name VARCHAR(50),
@Middle_Name VARCHAR(50),
@Last_Name VARCHAR(50),
@Suffix VARCHAR (10),
@Title VARCHAR (50);

SET@Prefix = ind_prf_code;
SET@First_Name = ind_first_name;
SET@Middle_Name = ind_mid_name;
SET@Last_Name = ind_last_name;
SET@Suffix = ind_sfx_code;
SET@Title = cst_title_dn;

select coalesce((@Prefix + ' '),'') +
ltrim(rtrim(@First_Name)) +
case when ltrim(rtrim(@Middle_Name)) = '' then ' ' else (' ' +
ltrim(rtrim(@Middle_Name)) + ' ') end +
ltrim(rtrim(@Last_Name)) +
coalesce(' ' + @Suffix, '') +
char(13) +
ltrim(rtrim(@Title)) +
char(13) as label

from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key

Thanks!

View 1 Replies View Related