MS SQL Server :: How To Put Condition In Select Statement To Write A Cursor

'IF' Condition On Select Statement?

Dec 8, 1998

Can you implement the same type of feature in SQL as MS Access uses with it's "immediate if"
or iif() function? In other words can you look at a specific row and change the contents
of one field based on the contents of another field in the same row?

I am trying to create a view like the the following:

CREATE VIEW AS Test
SELECT
name,
city ,
IF city = 'NY' state ELSE country
FROM address_table

The view's 3rd field will be either [state] or [country] depending on the contents of [city]

Thanks in advance!

..Marisa

View 3 Replies View Related

If Condition In Select Statement...

Apr 3, 2007

Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.

SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROMtblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId

Thanks,
Rahul Jha

View 2 Replies View Related

WHERE Condition In SELECT Statement

Nov 14, 2007

Hi All,
I want get the result in a single SELECT statement

SELECT
SUM (PAID_LOSS),
SUM (PAID_EXP)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period GE 200701 and LE 200712
-************************************
SELECT
SUM (MEDICAL_RESERVE)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE Accounting_Period LE 200712

View 5 Replies View Related

Condition In Select Statement

Mar 31, 2008

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 100
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.
and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value

here col2 values are unique

take an example for col2=212
if col3=B
then result is 100
if col3=N
then result is 18022.54
for a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54)
but not the sum of (100+18022.54+111.07+0.0) .

Can any one reply for this..............

View 1 Replies View Related

Need Help On Select Statement Wit Like Condition

Nov 17, 2007

I tried following code and it return no data after query run successfully. I m testing it on msaccess .

SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity
FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID
WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%');

-------

I have test and test2 in Arthor column ... Thanks all !!

View 7 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Select Statement - Filter Condition

Nov 5, 2013

How to display in select statment starting from the word 'basic'

table name:version

Table values:

versionidVersiondescription
53666445SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
53666446SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
53666447SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
53666448SAG Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

Output should be:

versiondescription
Basic Guild Agreement 07/01/2014 - 12/31/2014 PT - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 HV - HV
Basic Guild Agreement 07/01/2014 - 12/31/2014 NWLN- SY
Basic Guild Agreement 07/01/2014 - 12/31/2014 BC - SY

View 3 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Trying To Write A Select Statement.

Jul 26, 2007

I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80?
Lets say for example the table looks like this:

Student_ID

Grade

11276

93

56879

81

11276

76

44327

92

11276

84

56879

97

56879

82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!

View 8 Replies View Related

Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?

Aug 1, 2007

I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column.

Here's the select statement:
"SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC"

My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control.
i.e.

Public Class LIST_CTRL_DATA

Public WksName As String

Public InstallStatus As String

Public InstallDate As String

Public PackageName As String

Public AppName As String

Public Version As String

Public Build As String

Public City As String

Public State As String

Public Percent As UInteger

Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults.

While sqlReader.Read()

currec += 1

Dim msg As THREAD_MESSAGE

msg.msgid = 101

Dim message As New LIST_CTRL_DATA

message.WksName = sqlReader("Workstation_Name").ToString

message.InstallStatus = sqlReader("Install_Status").ToString

message.InstallDate = sqlReader("Install_Date").ToString

message.AppName = sqlReader("Name").ToString

message.Version = sqlReader("Version").ToString

message.Build = sqlReader("Build").ToString

message.PackageName = sqlReader("Package_Name").ToString

message.City = sqlReader("City").ToString

message.State = sqlReader("State").ToString

message.Percent = (currec * 100) / maxrecs

msg.msgdata = message

m_ParentWindow.Invoke(m_NotifyMainWindow, msg)

End While

Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how?
I'm still somewhat new to SQL. Any assistance would be great.

Regards,
NBK

View 1 Replies View Related

How To Write The Select Statement?

Sep 26, 2007

I create a table that is named T1(C1,C2) and insert some data,as following:

C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
I want to write a select statement to obtain the following result:

C1 C2
1 1
4 5
7 9
12 12
14 16
how to write the select statement?

　

View 2 Replies View Related

Select Statement In Cursor

Mar 26, 2000

Hi...

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

View 1 Replies View Related

Using Select Statement Instead Of Cursor

May 20, 2003

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.

View 6 Replies View Related

How To Specify Which Database To Use For A Select Statement Within A Cursor?

Nov 9, 2007

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!

View 7 Replies View Related

Moving Average Using Select Statement Or Cursor Based?

Jul 30, 2007

ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000

I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?

View 20 Replies View Related

How To Write A SELECT Statement And Store The Result In A Session Variable

Nov 6, 2007

I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns

ContactID

View 1 Replies View Related

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable

Mar 28, 2000

Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list

I have difficulty combining a select statement with a where clause stored in a variable inside a cursor

The users select the mail content and frequency of delivery and i deliver the mail

I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.

Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work

For example

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

How do I combine the select statement with the where clause?

Help me...I need help urgently

View 1 Replies View Related

Order By Clause In DECLARE CURSOR Select Statement Won't Compile

May 7, 2008

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

View 3 Replies View Related

How To Write This Condition?

Apr 3, 2007

Hi,

I am trying to set up a conditional split. As usual, my books do not explain what I am trying to do :-)

flat file source --> conditional split (if Column0 contains a certain string send to error file)

In the Conditional Split Transformation Editor I have the following for "condition":

FINDSTRING( "my search string", [Column0] ,1 )

TITLE: Microsoft Visual Studio
------------------------------

Error at myPkg [Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" is not Boolean. The result type of the expression must be Boolean.

Error at myPkg[Conditional Split [8259]]: The expression "FINDSTRING( "my search string[Column0] ,1 )" on "output "Case 1" (8351)" is not valid.

Error at myPkg [Conditional Split [8259]]: Failed to set property "Expression" on "output "Case 1" (8351)".

As you can see I want it to search Column0. Can someone please point out my error.

Thanks

View 6 Replies View Related

How To Write Trigger Based On Certain Condition

Nov 8, 2005

One of my table called as 'customertable' contains following fields

customername, emailid, subscriptionendperiod

Example records are:

david, david@john.com, 12/20/2005(mm/dd/yyyy format).

My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'

How to write trigger for this.

Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.

Regards

View 3 Replies View Related

SQL Server 2008 :: How To Write A SELECT Statement To Get Data From A Linked Server

Feb 23, 2015

I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.

how do I write a SQL statement to reference the linked server ?

I tried the following:

Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned

Then I get the error message....

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.

View 2 Replies View Related

Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..

Sep 20, 2007

hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????

View 5 Replies View Related

How To Write Condition In The Conditional Split Control In SSIS

Aug 25, 2006

HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution?

" if Prod_ID Exist then UPDATE Records

if Prod_ID Not EXIST then INSERT Records "

how to write the above conditon in the Condional Split?

Thanks & Regards,

Jeyakumar.M

chennai

View 10 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

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

How To Write Set-based SQL Instead Of Cursor

Nov 9, 2005

Guys
Here's the scenario

create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-12-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '30-11-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-10-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('98765' , '31-12-2005' , 2)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 1)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 0)
select * from data1

I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
Any ideas

View 3 Replies View Related

How To Write Code Without Cursor

Aug 25, 2014

I wrote the foollowing cursor code but it takes too long.how i can write it without cursor?

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader]
AS
BEGIN
SET NOCOUNT ON;

[code]....

View 8 Replies View Related

Transact SQL :: How To Write A Cursor For Row Count

May 20, 2015

I have a table which table has :Identity Column (Identity), Schema name and Table_Name.

So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.

Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :

how can I write a cursor and Import Those results into to Another table.

View 3 Replies View Related

Does Cursor Convert Table To Read/write?

Apr 10, 2008

Hello,

Any help here much appreciated.

I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.

i suspect this is when i run a cursor through the table.

Is anyone able to confirm for me whether running a cursor changes a table's properties?

Many thanks.

Tim

Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Write Script Without Using Temp Table Or Cursor

Jun 4, 2015

I have a cte:
With cte as
(
Select distinct Incident_ID,
ACTUAL_SEVERITY ,
Policy
From
table)

There are 3 ACTUAL_SEVERITY value: 1-High, 2-Medium and 3-Low

I need the final result be like:
Policy High Medium/Low

How do I write the script with out using temp table or cursor?

View 6 Replies View Related

SQL Server 2008 :: Write A Cursor To Fetch Required Data From Table?

Oct 21, 2015

I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.

I have a temp table named getInvoice where I have five important columns

1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time

and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.

An example is below :

InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime

579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00

My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.

The query I wrote is below

declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0

[Code] .....

This query runs forever and doesn't stop.

View 6 Replies View Related

Hi, How Could Put A Condition In Statement.

Jan 14, 2008

Hi, i wanted to put a condition based on my codes below.. but where i had to put the condition? like where QTY=3
please help..

INSERT INTO magpatoc.dbo.RSOTransfer(RSONO, Customer, ItemCode, ItemDescription, Source, MOQ, QuantityRequired, Remarks, ZeroStock, NewProduct, ProjectForecast, WithMotherPO, Other, RequestedBy, RequestedDatetime, NotedBy, RecievedBy, RecievedDatetime, PreparedBy, PreparedDatetime, ApprovedBy, ApprovedDate, ReservationNoDate, PurchaseOrderNo)
SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','c:CopyOfRSODB.mdb';'admin';'',FinalCustItemRSO)

ed9teenMagnaza

View 3 Replies View Related