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;
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!
);
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?
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
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 need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:
Server: Msg 16924, Level 16, State 1, Line 78 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?
select colnum, coldesc, colname into #ae_defs from ae_adefs select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') + ', ' + (select colname from #ae_defs where coldesc = 'PATIENT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT DATE') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT TYPE') + ', DocID from ae_dtl1'
DECLARE myCursor CURSOR FOR Select @SQL
OPEN myCursor print @@Cursor_rows FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
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.
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.
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.
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.
I have the following stored procedure, I would like to use IF statement or something of the sort in the where clause i.e. The last line in the SP is: AND (category.categoryID = @categoryID), I only want to check this, if @categoryID is not = 12. So can I do something like this:
IF @categoryID <> 12 AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients @categoryID int AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients FROM patient INNER JOIN patient_record ON patient.patientID = patient_record.patientID INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID INNER JOIN category ON category.categoryID = sub_category.categoryID WHERE risk = 6 AND (completed_date = '' OR completed_date IS NULL) AND (category.categoryID = @categoryID)
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?
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list
DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name> select * from x where x is a variable and the table name gets substituted.
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.
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'
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?
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
Hello everyone, I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches. So far i have tried: SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' " with no success. Any help is appreciated
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID FROM tblGRNItem INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp WHERE tblGRNItem.DateCreated BETWEEN (SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:
SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate
I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.
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
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.
I have to Select Order, Order Details and Order Status
Order Status is determined from Order Stage as follows:
If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.
For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)
How to combined order stage from table 2 and table 3 and then compute order status.
I have a two tables each having a uniqueidentifier column person_id
I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.
-- insert into wch_needed those who need checked
insert into #wch_needed (person_id, rendered_by ) select distinct e.person_id, e.rendered_by from #wch_who o, encounter e where o.person_id not in (select distinct person_id from #wch_have ) and o.person_id = e.person_id
the where conditional
where o.person_id not in (select distinct person_id from #wch_have )
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales 11 A 1000 23 A 1980 23 B 50 5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A 2)- if the store has type A associated with it then assign A 3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type from table1 where (TYPE]= (case when [TYPE] in ('A','B') then 'A' when [TYPE]='A' then 'A' else 'B'end)) GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
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.
