Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

I am new to cursors--need help retrieving the rows. Here is my sy


When I execute the below statement I get 1 character returned for each column
and no column headings.  Please help.

DECLARE CONTRACT_CURS CURSOR SCROLL for

SELECT CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END
FROM CONTRACTS
GROUP BY CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END

DECLARE
       @CONTRACT_NAME VARCHAR
       @PACKAGE_NAME NVARCHAR,
       @PAY_START NVARCHAR,
       @PAY_END NVARCHAR

OPEN CONTRACTS_CURS

FETCH NEXT FROM CONTRACTS_CURS
        INTO @CONTRACT_NAME,
                @PACKAGE_NAME,
                        @PAY_START,
                        @PAY_END

WHILE @@FETCH_STATUS = 0
BEGIN
        select @CONTRACT_NAME,
                 @PACKAGE_NAME,
                         @PAY_START,
                         @PAY_END,
 END
 CLOSE CONTRACTS_CURS

DEALLOCATE CONTRACTS_CURS

On Jun 4, 2:59 pm, OriginalStealth

<OriginalStea@discussions.microsoft.com> wrote:
> When I execute the below statement I get 1 character returned for each column
> and no column headings.  Please help.

The best way for me to help you is to tell you DON'T USE CURSORS.
They are very seldom the answer to a SQL Server problem.

The nature of your question further informs me that you are just
beginning this, so I'll tell you the other thing you need to know.
When declaring a VARCHAR variable, you have to give it a length:

DECLARE
       @CONTRACT_NAME VARCHAR(30)
       @PACKAGE_NAME NVARCHAR(45),
       @PAY_START NVARCHAR(12),
       @PAY_END NVARCHAR(12)

Otherwise it takes the default length of one character.

But please. Unless this is a homework problem where you have to
demonstrate that you can write a cursor, DON'T USE CURSORS.

-----------------------------------------------Reply-----------------------------------------------

First of all, you don't need a cursor.  Second, you declared variables that
are 1-char in width, since you did not specify a width.  Third, you did a
SELECT on variables, which does not give you column names; you need to
provide them yourself:

select @CONTRACT_NAME as CONTRACT_NAME,
         @PACKAGE_NAME as PACKAGE_NAME,
                         @PAY_START as PAY_START,
                         @PAY_END as PAY_END

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"OriginalStealth" <OriginalStea@discussions.microsoft.com> wrote in
message news:402B1849-7371-47F0-805D-9A3FCB820B8E@microsoft.com...
When I execute the below statement I get 1 character returned for each
column
and no column headings.  Please help.

DECLARE CONTRACT_CURS CURSOR SCROLL for

SELECT CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END
FROM CONTRACTS
GROUP BY CONTRACT_NAME, PACKAGE_NAME, PAY_START, PAY_END

DECLARE
       @CONTRACT_NAME VARCHAR
       @PACKAGE_NAME NVARCHAR,
       @PAY_START NVARCHAR,
       @PAY_END NVARCHAR

OPEN CONTRACTS_CURS

FETCH NEXT FROM CONTRACTS_CURS
INTO @CONTRACT_NAME,
             @PACKAGE_NAME,
                        @PAY_START,
                        @PAY_END

WHILE @@FETCH_STATUS = 0
BEGIN
select @CONTRACT_NAME,
         @PACKAGE_NAME,
                         @PAY_START,
                         @PAY_END,
 END
 CLOSE CONTRACTS_CURS

DEALLOCATE CONTRACTS_CURS

-----------------------------------------------Reply-----------------------------------------------

"OriginalStealth" <OriginalStea@discussions.microsoft.com> wrote in
message news:402B1849-7371-47F0-805D-9A3FCB820B8E@microsoft.com...

> When I execute the below statement I get 1 character returned for each
> column
> and no column headings.  Please help.

I expect that everyone will tell you to avoid using a cursor. Unfortunately
you haven't given enough information for anyone to show you an alternative.
Please post DDL, sample data and show your required end result.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc