Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50

    Unanswered: Return values from Stored procedures

    Hi,
    I want to know how I can return a value through an OUTPUT parmeter with out using a CURSOR.
    The value I want to return is simply a SUM query result.

    I will really appreciate your help.
    Khalid Yousef

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    declare the parameter as output

    create procedure a
    @outparm int output
    as

    select @outparm = 5
    go

    Then in the client you will have to also declare the parameter as output to receive a result.
    In ado the parameter object is declared as adInputOutput and you will have to close any result set before the parameter is available.

    To get the parameter into a t-sql script

    declare @aparm int
    exec a @aparm output

  3. #3
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50

    One more problem!

    Thank you very much, I really like solving problems by providing small examples.
    What you have shown me, did help a lot. But I am still facing a little problem.

    I script ASP and I am new to SQL Server, so I couldn't pass the OUTPUT parameter from the ASP script.

    If I pass it as a variable as:

    oConn.Execute "CountNewMessages 1, 12, '2002/02/17 10:08:03', " & NoOfNewSubjects & " OUTPUT "

    I get the following:

    Microsoft OLE DB Provider for SQL Server (0x80040E07)
    Error converting data type nvarchar to int.

    And if I pass it withing the string as:

    oConn.Execute "CountNewMessages 1, 12, '2002/02/17 10:08:03', NoOfNewSubjects OUTPUT "

    I get the following:

    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Cannot use the OUTPUT option when passing a constant to a stored procedure.


    My stored procedure is like this:

    CREATE PROC CountNewMessages
    @forum_UserNo int,
    @forum_MemberNo int,
    @forum_LastVisitDate datetime,
    @forum_ReturnValue int OUTPUT
    AS
    SET DATEFORMAT dmy
    DECLARE @MessagesNo int
    DECLARE ctr_cursor CURSOR
    FOR SELECT Count(No) AS ForumMessages
    FROM tblForumMessages
    WHERE tblForumMessages.[No]=@forum_UserNo AND
    tblForumMessages.MessageTarget=0 AND
    tblForumMessages.MessageQuestion=1 AND
    tblForumMessages.MemberNo <> @forum_MemberNo AND
    (DATEDIFF(n, MessageDate, CONVERT(DATETIME,@forum_LastVisitDate,102)) > 0)

    OPEN ctr_cursor
    FETCH NEXT FROM ctr_cursor INTO @MessagesNo

    SELECT @forum_ReturnValue = @MessagesNo

    CLOSE ctr_cursor
    DEALLOCATE ctr_Cursor

    =================================

    from an ASP script I call it like this:

    oConn.Execute "CountNewMessages 1, 12, '2002/02/17 10:08:03', NoOfNewSubjects OUTPUT "

    Thanks
    I am glad I found a professional in MS SQL Server
    All the best
    Khalid Yousef

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You will need to set up a parameter object to receive the output.
    See
    www.nigelrivett.com
    Call stored procedures from ASP

    Probably something like this would work.
    The link above shows code which is a lot easier.

    dim objCmd, objRset, objParm, i
    set objCmd = Server.CreateObject("ADODB.Command")
    set objParm = Server.CreateObject("ADODB.Parameter")
    objParm.Direction = adParamOutput
    objParm.Type = adInteger
    objCmd.Parameters.append objParm

    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "CountNewMessages"
    objCmd.Execute
    i = objCmd.parameters(0)
    Set objCmd = Nothing

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    A few comments about the t-sql.
    You are using a cursor - these are never necessary in t-sql and are usually wrong.


    in this case the code should be

    CREATE PROC CountNewMessages
    @forum_UserNo int,
    @forum_MemberNo int,
    @forum_LastVisitDate datetime,
    @forum_ReturnValue int OUTPUT
    AS
    SELECT @forum_ReturnValue = Count(No) AS ForumMessages
    FROM tblForumMessages
    WHERE tblForumMessages.[No]=@forum_UserNo
    AND tblForumMessages.MessageTarget=0
    AND tblForumMessages.MessageQuestion=1
    AND tblForumMessages.MemberNo <> @forum_MemberNo
    AND DATEDIFF(n, MessageDate, CONVERT(DATETIME,@forum_LastVisitDate,102)) > 0
    go

  6. #6
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    I think you are the right person to answer my question:

    A bit of a background:
    I want to use stored procedures to transfer data from one table to the other nightly.

    I will first read the data from one table as in [code fragment A] and then into another as in [code fragment B]. There can be more than one row matching the crieteria. I have succeeded in reading and inserting only one row whether there is only one row or many. Can you show me how to read and insert if there a re many row. Thanks
    If I do not hear from you, taday I will go ahead and create a new thread.

    [code fragment A]

    SELECT @BilletID_Inserted = [BilletID] FROM NGBCST_7032002.dbo.Billet where datepart(M, [DateTimeStamp]) = @MonthInt and datepart(D, [DateTimeStamp]) = @DayInt and datepart(YY, [DateTimeStamp]) = @YearInt

    SELECT @BilletName_Inserted = [BilletName] FROM NGBCST_7032002.dbo.Billet where datepart(M, [DateTimeStamp]) = @MonthInt and datepart(D, [DateTimeStamp]) = @DayInt and datepart(YY, [DateTimeStamp]) = @YearInt

    SELECT @Description_Inserted = [Description] FROM NGBCST_7032002.dbo.Billet where datepart(M, [DateTimeStamp]) = @MonthInt and datepart(D, [DateTimeStamp]) = @DayInt and datepart(YY, [DateTimeStamp]) = @YearInt

    SELECT @DateTimeStamp_Inserted = [DateTimeStamp] FROM NGBCST_7032002.dbo.Billet where datepart(M, [DateTimeStamp]) = @MonthInt and datepart(D, [DateTimeStamp]) = @DayInt and datepart(YY, [DateTimeStamp]) = @YearInt

    SELECT @UpdaterID_Inserted = [UpdaterID] FROM NGBCST_7032002.dbo.Billet where datepart(M, [DateTimeStamp]) = @MonthInt and datepart(D, [DateTimeStamp]) = @DayInt and datepart(YY, [DateTimeStamp]) = @YearInt

    [code fragment B]

    INSERT NGBCST_7032002.dbo.Billet_Mart (BilletID, BilletName, Description,DateTimeStamp,UpdaterID) VALUES (@BilletID_Inserted,
    @BilletName_Inserted,
    @Description_Inserted,
    @DateTimeStamp_Inserted,
    @UpdaterID_Inserted)

  7. #7
    Join Date
    Jul 2002
    Posts
    2
    To insert all the rows of a resultset into another Table you could use do this...


    /* Assume that you are
    selecting from a sinlge TABLE
    with same criteria */

    DECLARE @SQL As VARCHAR(1000)
    DECLARE @MonthInt As DATETIME
    DECLARE @DayInt As DATETIME
    DECLARE @YearInt As DATETIME

    @SQL ='SELECT [BilletID]' +
    ', [BilletName]' +
    ', [Description]' +
    ', [DateTimeStamp]' +
    ', [UpdaterID]' +
    'FROM NGBCST_7032002.dbo.Billet '+
    'WHERE DATEPART(M, [DateTimeStamp]) ='
    +@MonthInt +
    'AND DATEPART(D, [DateTimeStamp]) ='.
    + @DayInt +
    'AND DATEPART(YY, [DateTimeStamp]) = '
    +@YearInt


    INSERT INTO NGBCST_7032002.dbo.Billet_Mart
    BilletID, BilletName, Description,DateTimeStamp,UpdaterID)
    EXEC (@SQL)

    I was not sure why did you go selecting each column at a time....

    Hope it helps you

    Charles Selvaraj
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, wango, you don't even need to select the data into stored proc variables before inserting them...
    Code:
    INSERT into NGBCST_7032002.dbo.Billet_Mart
         ( BilletID
         , BilletName
         , Description
         , DateTimeStamp
         , UpdaterID) 
    SELECT [BilletID]
         , [BilletName]
         , [Description]
         , [DateTimeStamp]
         , [UpdaterID] 
      FROM NGBCST_7032002.dbo.Billet 
     where datepart(M, [DateTimeStamp]) = @MonthInt 
       and datepart(D, [DateTimeStamp]) = @DayInt 
       and datepart(YY, [DateTimeStamp]) = @YearInt
    nigel, i get a server error on your site: "This server has encountered an internal error which prevents it from fulfilling your request. The most likely cause is a misconfiguration. Please ask the administrator to look for messages in the server's error log."

    rudy
    http://rudy.ca/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •