none
print select statement

    Question

  • hi

    i use cursor for output report but don't know how to set up it for Print

    problematic part is 49-54 line, when select is displayed in Grid and not as text and every line has own head. i need to how can i do it. thx

    SET NOCOUNT ON;
    
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    
    
    PRINT '-------------------------Report Uverov-----------------------';
    
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    
    OPEN hlavicka_cursor
    
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT ' '
        select @message = 'Uvery za den: ' + @DEN
        PRINT @message 
        PRINT '--------------------------'
    
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
    
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
    
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
    
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
    
        WHILE @@FETCH_STATUS = 0
        BEGIN
    
            SELECT @message = 'select QUOTENAME(EOD),QUOTENAME(datum) from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            PRINT @message 
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
    
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;

    
    
    
    
    
    
    
    
    
    
    
    
    Tuesday, August 06, 2013 1:16 PM

Answers

  • next try:
    SET NOCOUNT ON;
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    CREATE TABLE #message (msg nvarchar(200));
    PRINT '-------------------------Report Uverov-----------------------';
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    OPEN hlavicka_cursor
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --PRINT ' '
        select @message = 'Uvery za den: ' + @DEN
    	INSERT #message VALUES (@message)
        --PRINT @message 
        --PRINT '--------------------------'
    	INSERT #message VALUES ('--------------------------')
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @message = 'INSERT #message select EOD + ''  ''+ datum from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;
    SELECT * FROM #message


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by Peter2285 Wednesday, August 07, 2013 11:42 AM
    Wednesday, August 07, 2013 11:38 AM

All replies

  • problematic part is 49-54 line, when select is displayed in Grid and not as text and every line has own head. i need to how can i do it. 

    Hello Peter,

    I am not sure, what you mean? You execute several statement and each result of the single executions are shown in an own grid. And the "Print" result you can find in the tab "Messages", not in "Result".


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 06, 2013 1:30 PM
  • you can create a temporary table, insert your @message result in it and print it (SELECT * FROM temptable) at the end, outside your cursor.

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, August 06, 2013 1:34 PM
  • hi,

    i try it with tmp table but was not succesfull. could you pls show me how can i do it pls. thx

    Tuesday, August 06, 2013 4:08 PM
  • SET NOCOUNT ON;
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    CREATE TABLE #message (msg nvarchar(200));
    PRINT '-------------------------Report Uverov-----------------------';
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    OPEN hlavicka_cursor
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT ' '
        select @message = 'Uvery za den: ' + @DEN
        PRINT @message 
        PRINT '--------------------------'
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @message = 'select QUOTENAME(EOD),QUOTENAME(datum) from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            INSERT #message VALUES (@message)
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;
    SELECT * FROM #message


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, August 07, 2013 8:45 AM
  • i try it, but in output still missing requested rows. i need to PRINT table #message so as rows will be visible under their head.
    -------------------------Report Uverov-----------------------
     
    Uvery za den: 2013-07-31
    --------------------------
     
    Uvery za den: 2013-08-01
    --------------------------
     
    Uvery za den: 2013-08-02
    --------------------------
     
    Uvery za den: 2013-08-03
    --------------------------

    Wednesday, August 07, 2013 8:52 AM
  • try this; I didn't notice there were 2 PRINT statements of the @message string

    SET NOCOUNT ON;
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    CREATE TABLE #message (msg nvarchar(200));
    PRINT '-------------------------Report Uverov-----------------------';
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    OPEN hlavicka_cursor
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --PRINT ' '
        --select @message = 'Uvery za den: ' + @DEN
        --PRINT @message 
        --PRINT '--------------------------'
    	INSERT #message VALUES (@message)
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @message = 'select QUOTENAME(EOD),QUOTENAME(datum) from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            INSERT #message VALUES (@message)
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;
    SELECT * FROM #message


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by Peter2285 Wednesday, August 07, 2013 9:37 AM
    • Unmarked as answer by Peter2285 Wednesday, August 07, 2013 9:37 AM
    Wednesday, August 07, 2013 9:03 AM
  • unfortunately not. now it display only first row with name of report. i need it do have it in format below, but problem is  how to insert there rows with info for that day
    -------------------------Report Uverov-----------------------
     
    Uvery za den: 2013-07-31
    --------------------------
    20130731   31.7.2013
    20130731   31.7.2013
    20130731   31.7.2013
     
    Uvery za den: 2013-08-01
    --------------------------
    20130801   01.08.2013 
    20130801   01.08.2013 
    20130801   01.08.2013 
    

    Wednesday, August 07, 2013 9:22 AM
  • now I think I got it
    SET NOCOUNT ON;
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    CREATE TABLE #message (msg nvarchar(200));
    PRINT '-------------------------Report Uverov-----------------------';
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    OPEN hlavicka_cursor
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --PRINT ' '
        --select @message = 'Uvery za den: ' + @DEN
        --PRINT @message 
        --PRINT '--------------------------'
    	INSERT #message VALUES (@message)
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @message = 'INSERT #message select QUOTENAME(EOD) + ''  ''+ QUOTENAME(datum) from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;
    SELECT * FROM #message


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by Peter2285 Wednesday, August 07, 2013 9:34 AM
    • Unmarked as answer by Peter2285 Wednesday, August 07, 2013 9:37 AM
    Wednesday, August 07, 2013 9:29 AM
  • in screen results it display requested rows but still missing in screen Message. i don't know how to use Print statement for selected rows to display it in Report
    -------------------------Report Uverov-----------------------
     
    Uvery za den: 2013-07-31
    --------------------------
     
    Uvery za den: 2013-08-01
    --------------------------

    Wednesday, August 07, 2013 9:39 AM
  • I'm not sure I quite understand what you are aiming at.

    can you provide me the structure of the table (dbo.tab_loans_cz) and a few line in the form of insert statements and also the requested result?


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, August 07, 2013 11:09 AM
  • this is requested information. when all selected info will be in window MESSAGES a can create a file from this output and then send by email automatically

    ---create table---
    Create table dbo.tab_loans_cz
    (dbkey int identity(1,1)
    ,EOD nvarchar(6)
    ,DATUM nvarchar(10)
    ,OJ nvarchar(5)
    ,NAZOV nvarchar(25)
    ,POKLADNA nvarchar(8)
    ,SUMA nvarchar(8)
    )
    
    ----fill in table
    insert into dbo.tab_loans_cz (EOD,DATUM,OJ,NAZOV,POKLADNA,SUMA ) values
    (130731,2013-07-31,11036,'Brno Kralovo Pole',102,7199),
    (130731,2013-07-31,11040,'Plzen C4',92,8549),
    (130731,2013-07-31,11080,'Mlada Boleslav',101,6749),
    (130731,2013-07-31,11009,'Ostrava Hrabova',91,4275),
    (130801,2013-08-01,11037,'Hradec Kralove C4',91,3330),
    (130801,2013-08-01,11025,'Kolin',101,4950),
    (130801,2013-08-01,11040,'Plzen C4',102,3150),
    (130801,2013-08-01,11042,'Zlin C4',102,3419)
    
    --- thats how the output should look in result window MESSAGE---
    
    -------------------------Report Uverov-----------------------
     
    Uvery za den: 2013-07-31
    --------------------------
    130731     2013-07-31   11036  Brno Kralovo Pole      102 7199
    130731     2013-07-31   11040  Plzen C4               92  8549
    130731     2013-07-31   11080  Mlada Boleslav         101 6749
    130731     2013-07-31   11009  Ostrava Hrabova        91  4275
     
    Uvery za den: 2013-08-01
    --------------------------
    130801    2013-08-01    11037  Hradec Kralove C4      91  3330
    130801    2013-08-01    11025  Kolin                  101 4950
    130801    2013-08-01    11040  Plzen C4               102 3150
    .....

    Wednesday, August 07, 2013 11:28 AM
  • next try:
    SET NOCOUNT ON;
    DECLARE 
    @DEN as varchar(15),
    @message as nvarchar(200),
    @dbkey as varchar (5),
    @eod as varchar (20),
    @datum as varchar (20),
    @nazov as varchar (20),
    @pokladna as varchar (20),
    @suma as varchar (20)
    CREATE TABLE #message (msg nvarchar(200));
    PRINT '-------------------------Report Uverov-----------------------';
    DECLARE hlavicka_cursor CURSOR FOR 
    select distinct DATUM 
    from dbo.tab_loans_cz;
    OPEN hlavicka_cursor
    FETCH NEXT FROM hlavicka_cursor 
    INTO @DEN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --PRINT ' '
        select @message = 'Uvery za den: ' + @DEN
    	INSERT #message VALUES (@message)
        --PRINT @message 
        --PRINT '--------------------------'
    	INSERT #message VALUES ('--------------------------')
        -- Declare an inner cursor based   
        -- on @DEN from the outer cursor.
        DECLARE telicko_cursor CURSOR FOR 
        SELECT dbkey,eod,datum,nazov,pokladna,suma
        FROM dbo.tab_loans_cz
        WHERE DATUM=@DEN  -- Variable value from the outer cursor
        OPEN telicko_cursor
        FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
        IF @@FETCH_STATUS <> 0 
            PRINT '         <<None>>'     
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @message = 'INSERT #message select EOD + ''  ''+ datum from 
            dbo.tab_loans_cz a where dbkey='+@dbkey
            exec (@message)
            FETCH NEXT FROM telicko_cursor INTO @dbkey,@eod,@datum,@nazov,@pokladna,@suma
            END
        CLOSE telicko_cursor
        DEALLOCATE telicko_cursor
            -- Get the next vendor.
        FETCH NEXT FROM hlavicka_cursor 
        INTO @DEN
    END 
    CLOSE hlavicka_cursor;
    DEALLOCATE hlavicka_cursor;
    SELECT * FROM #message


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    • Marked as answer by Peter2285 Wednesday, August 07, 2013 11:42 AM
    Wednesday, August 07, 2013 11:38 AM
  • yes correct now. thx a lot for your help
    Wednesday, August 07, 2013 11:42 AM