SQL Server Performance

SQL SERVER 2000 PRINT function not working

Discussion in 'General Developer Questions' started by Lennie, Mar 5, 2010.

  1. Lennie New Member

    Hullo All Good Guys, [:D]
    I am coming to you all for help. I am using VBNET2008 codes to activate the SQL SERVER PRINT function in the Stored Procedure
    but having problem with the CURSOR in the Stored Procedure.
    I am using VBNET2008, SQL SERVER 2000 and NORTHWIND database to test the PRINT .
    Here are the VBNET2008 coding.
    Private Sub btnSQLSERVERPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLSERVERPrint.Click
    clsConnSrv = New ClassConnectionService
    connstr = clsConnSrv.PropAdoConnection
    sqlconn = New SqlConnection(connstr)
    sqlconn.Open()
    sqlcmd = New SqlCommand
    sqlcmd.Connection = sqlconn
    'Call Stored Procedure PRINT
    With sqlcmd
    .CommandText = "SProcPrintAllSales"
    .CommandType = CommandType.StoredProcedure
    End With

    sqlcmd.ExecuteNonQuery()
    sqlconn.Close()
    End Sub
    ---------------------------------------
    Here are the coding from STORED PROCEDURE
    Code:
    Create Procedure SProcPrintAllSales
    AS

    DECLARE AllSales Cursor
    Open AllSales

    /*** the following variables hold fields ***/
    declare @cCompany nchar(40)
    declare @prevCompany nchar(40)
    declare @cOrderId int
    declare @prevOrderId int
    declare @cProduct nvarchar(40)
    declare @cUnitPrice money
    declare @cQuantity smallint
    declare @cDiscount int
    declare @cExtPrice money
    /*** the following 2 variables hold the running totals ***/

    declare @CustomerTotal money
    declare @OrderTotal money
    Fetch next from AllSales into
    @cCompany, @cOrderID, @cProduct,
    @cUnitPrice, @cQuantity, @cDiscount, @cExtPrice
    Set @prevCompany = @cCompany
    set @prevOrderId = @cOrderID
    Print "COMPANY " + @cCompany
    Print " Order " + Convert(char(5), @cOrderId)
    Set @CustomerTotal = 0
    Set @OrderTotal = 0
    While @@Fetch_Status = 0
    Begin
    if @prevCompany <> @cCompany
    Begin
    set @customerTotal = @CustomerTotal + @OrderTotal
    Print " ********** TOTAL FOR ORDER " +
    CONVERT(char(5), @prevOrderID) + " ************* " +
    Convert(char(8), @OrderTotal )

    Print " TOTAL FOR " + @prevCompany + " " +
    Convert(char(10), @CustomerTotal )
    Set @CustomerTotal = 0
    Print " "
    Print "COMPANY " + @cCompany
    Print " ORDER " + Convert(char(5), @cOrderId)
    set @OrderTotal = 0
    End
    else
    Begin
    if @prevOrderID <> @cOrderId
    Begin
    Print " ****TOTAL FOR ORDER " +
    CONVERT(CHAR(8), @prevOrderId ) + " *** " +
    Convert(char(8), @OrderTotal )
    Print " ORDER " + convert(char(5), @cOrderID)
    Set @customerTotal = @CustomerTotal + @OrderTotal
    Set @OrderTotal = 0
    End
    Print " " +
    CONVERT(CHAR(30), @cProduct ) + " " +
    Convert(char(4), @cQuantity) + " X " +
    Convert(char(6), @cUnitPrice) + " @ " +
    Convert(char(2), @cDiscount) + " % " +
    Convert(char(12), @cExtPrice)
    Set @OrderTotal = @OrderTotal + @cExtPrice
    End
    Set @prevOrderId = @cOrderId
    Set @prevCompany = @cCompany
    Fetch Next from AllSales INTO
    @cCompany, @cOrderID, @cProduct,
    @cUnitPrice, @cQuantity, @cDiscount, @cExtPrice
    End
    Print " TOTAL for " + @prevCompany + " " + convert(char(10), @customerTotal)
    CLOSE ALLSALES
    GO
    --------------------------------------------------------
    Please help me. Once I got it working based on your information, I will posted the coding here to share it with Newbies like me having similiar problems.
    Thank you very much
    Cheers,
    Lennie


  2. satya Moderator

    How are you are trying to achieve the results, executing the VB Function in a SP or query?
    Have you tried to convert that VB code into extended stored proc
  3. Adriaan New Member

    PRINT is a command that you can use while developing/debugging.
    PRINT displays stuff on the Messages tab of the query window of the SQL Server program.
    PRINT does not return anything on the Results tab (which is what a client application would receive as the resultset of the call to SQL Server).
    In production code you should remove all PRINT commands - they can confuse your client application.
  4. Lennie New Member

    Thanks to you guys,
    But the question of CURSOR problem is still waiting for help.
  5. Adriaan New Member

    You seem to have skipped the most important part of the cursor definition, which normally comes between the DECLARE and OPEN lines, here:
    DECLARE AllSales Cursor
    Open AllSales
    Next up, you still need to work on what you want to use instead of PRINT ...
  6. Lennie New Member

    Hi Adriaan,
    Regarding the Quotation below what did I miss between DECLARE and OPEN ?
    You seem to have skipped the most important part of the cursor definition, which normally comes between the DECLARE and OPEN lines, here:
    DECLARE AllSales Cursor
    Open AllSales
  7. Adriaan New Member

    FOR ---your select query goes here----
  8. Lennie New Member

    Hi Adriaan,
    Do you mean this :-
    DECLARE AllSales Cursor
    Select
    Open AllSales
  9. Adriaan New Member

    It´s about time you start reading Books Online on the subject. We can´t do your work for you, and if you´re not familiar with the basic syntax then why are you even touching that script?
  10. Lennie New Member

    Hi Adriaan,
    You are not doing my work for me. If you are offering assistance at least provide a short sample coding to support your suggestion : SELECT
  11. FrankKalis Moderator

    I guess what Adriaan wants to say is that you are missing a quite important bit of syntax in your cursor declaration. Maybe one bit that could easily be identified by reading through the Books Online's "DECLARE CURSOR" explanations.
    Every cursor declaration is pretyy much useless when you don't provide a SELECT statement on which the cursor can work on. So, in pseudecode your cursor would look something like
    DECLARE AllSales CURSOR
    FOR
    SELECT yadayadayada... FROM whatever

    OPEN AllSales
    I don't have a SQL Server 2000 at hand any longer, but I would be suprised if you could get the procedure to compile without this FOR SELECT... thing. Should throw an error.
  12. Lennie New Member

    Hi FrankKalis,
    Thank you very much for your suggestion. You are just awesome providing me with sample coding. I have tried it and it's working now. Thank you very much.
  13. Madhivanan Moderator

    Can you post some sample data and the result you want?
    I think you can avoid a cursor
  14. Lennie New Member

    Hi there,
    For testing purposed I am using the NorthWind Database table, ORDERSDETAILS and ORDERS table
  15. Madhivanan Moderator

    [quote user="Lennie"]
    Hi there,
    For testing purposed I am using the NorthWind Database table, ORDERSDETAILS and ORDERS table
    [/quote]
    IIUC, you are trying to generate report output using a sql
    You should use a reporting tool for this purpose
    If it is only for a learning purpose you can also try ROLLUP and CUBE operator
    See the example codes in SQL Server help file
  16. moh_hassan20 New Member

    Complete your vb.net procedure and use sqldatareader (or datatable) ,and loop over its rows and print your result to the printing device using .net printing capability.
    No need for the stored pocedure and the cursor , especially its role is printing only.

Share This Page