locked
How to assign a value to a variable RRS feed

  • Question

  • Hi,

    If i run this code

    Declare @oldmax bigint
    SELECT @oldmax = maxExportTimeStamp  FROM 
    OPENQUERY([ssc2008],'select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMPS')

    i would get 

    Command(s) completed successfully.

    But i would like to see the result set i.e the value of @oldmax.

    Can someone please help me with this?

    Thanks

    Tuesday, March 4, 2014 8:49 PM

Answers

  • Try this please:

    ALTER PROCEDURE [Staging].[pODS_Ins_tODS_TMS_StoreTraffic]
        @RowsProcessed INT OUTPUT ,
        @RowsInserted INT OUTPUT ,
        @RowsUpdated INT OUTPUT ,
        @RowsDeleted INT OUTPUT ,
        @tUTL_JobLogging_Key INT ,
        @oldmax BIGINT OUTPUT,		--change this variable to output
        @newmax BIGINT
    AS 
        BEGIN
            BEGIN TRANSACTION
            SET @RowsUpdated = 0
            SET @RowsDeleted = 0
    
            TRUNCATE TABLE
    ODS.Staging.tODS_TMS_StoreTraffic
    
            SET @oldmax = ( SELECT  *
                            FROM    OPENQUERY([ssc2008],
                                              'select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMPS')
                          )
            SELECT  @OldMax AS OldMax
    
            IF @oldmax IS NULL 
                SET @oldmax = 0
    
    
            INSERT  INTO ODS.Staging.tODS_TMS_StoreTraffic
                    SELECT  @tUTL_JobLogging_Key ,
                            *
                    FROM    dbo.fnExportTrafficTS(@oldmax)
                    ORDER BY storeID ,
                            TrafDate;
    
            SET @newmax = ( SELECT  *
                            FROM    OPENQUERY([ssc2008],
                                              'select max(TimeStamp) as MaxTimeStamp from TMS_Live.dbo.TRAFFIC')
                          )
            SELECT  @newmax AS NewMax
    
            IF @newmax > @oldmax 
                INSERT  INTO EXPORT_TIMESTAMPS
                VALUES  ( @newmax, GETDATE() )
    
    
            SET @RowsInserted = @@ROWCOUNT 
    
            COMMIT TRANSACTION
        END
        
    GO    
    ----------------------------------
    -- call sp:
    DECLARE 
        @RowsProcessed INT  ,
        @RowsInserted INT ,
        @RowsUpdated INT ,
        @RowsDeleted INT ,
        @tUTL_JobLogging_Key INT ,
        @oldmax BIGINT ,		--change this variable to output
        @newmax BIGINT ;
        
    EXEC [Staging].[pODS_Ins_tODS_TMS_StoreTraffic]
        @RowsProcessed  OUTPUT ,
        @RowsInserted  OUTPUT ,
        @RowsUpdated  OUTPUT ,
        @RowsDeleted  OUTPUT ,
        @tUTL_JobLogging_Key  ,
        @oldmax  OUTPUT,		--change this variable to output
        @newmax 
        
    SELECT @oldmax AS oldmax ;


    sqldevelop.wordpress.com

    • Proposed as answer by Naomi N Tuesday, March 4, 2014 9:26 PM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 6:06 AM
    Tuesday, March 4, 2014 9:12 PM

All replies

  • Declare @oldmax bigint
     SELECT @oldmax = maxExportTimeStamp  FROM 
     OPENQUERY([ssc2008],'select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMPS')
    
    SELECT @oldmax

    • Proposed as answer by Naomi N Tuesday, March 4, 2014 9:25 PM
    Tuesday, March 4, 2014 8:52 PM
  • Hi Beginner,

    This way i would get the output but the column name is None, is there anyway i can assign it to the variable,because this code is a part of my procedure i am using which needs this output value to be stored into this variable.

    Tuesday, March 4, 2014 8:54 PM
  • Hi,

    Try

    SELECT @OldMax AS OldMax


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, March 4, 2014 8:56 PM
  • Hi Sebastian,

    this is my procedure

    ALTER procedure [Staging].[pODS_Ins_tODS_TMS_StoreTraffic]
    @RowsProcessed INT OUTPUT,
    @RowsInserted INT OUTPUT,
    @RowsUpdated INT OUTPUT,
    @RowsDeleted INT OUTPUT,
    @tUTL_JobLogging_Key INT,
    @oldmax bigint,
    @newmax bigint
    AS
    BEGIN
    BEGIN TRANSACTION
    SET @RowsUpdated = 0
    SET @RowsDeleted = 0

    Truncate Table ODS.Staging.tODS_TMS_StoreTraffic

    SET @oldmax =  ( select *  FROM 
    OPENQUERY([ssc2008],'select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMPS'))
    SELECT @OldMax AS OldMax

    IF @oldmax IS NULL
    SET @oldmax = 0


    INSERT INTO ODS.Staging.tODS_TMS_StoreTraffic
    SELECT @tUTL_JobLogging_Key,* FROM dbo.fnExportTrafficTS(@oldmax) ORDER BY storeID, TrafDate;

    SET @newmax = (select *  FROM 
    OPENQUERY([ssc2008],'select max(TimeStamp) as MaxTimeStamp from TMS_Live.dbo.TRAFFIC'))
    select @newmax AS NewMax

    IF @newmax > @oldmax
    INSERT INTO EXPORT_TIMESTAMPS
    VALUES(@newmax, GETDATE())


    SET @RowsInserted = @@ROWCOUNT 

    COMMIT TRANSACTION
    END

    So even if i do like you said i still get the error saying

    Procedure or function  expects parameter '@oldm
    ax', which was not supplied.

    Can you help me get around with this?

    Thanks

    Tuesday, March 4, 2014 9:01 PM
  • Try this please:

    ALTER PROCEDURE [Staging].[pODS_Ins_tODS_TMS_StoreTraffic]
        @RowsProcessed INT OUTPUT ,
        @RowsInserted INT OUTPUT ,
        @RowsUpdated INT OUTPUT ,
        @RowsDeleted INT OUTPUT ,
        @tUTL_JobLogging_Key INT ,
        @oldmax BIGINT OUTPUT,		--change this variable to output
        @newmax BIGINT
    AS 
        BEGIN
            BEGIN TRANSACTION
            SET @RowsUpdated = 0
            SET @RowsDeleted = 0
    
            TRUNCATE TABLE
    ODS.Staging.tODS_TMS_StoreTraffic
    
            SET @oldmax = ( SELECT  *
                            FROM    OPENQUERY([ssc2008],
                                              'select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMPS')
                          )
            SELECT  @OldMax AS OldMax
    
            IF @oldmax IS NULL 
                SET @oldmax = 0
    
    
            INSERT  INTO ODS.Staging.tODS_TMS_StoreTraffic
                    SELECT  @tUTL_JobLogging_Key ,
                            *
                    FROM    dbo.fnExportTrafficTS(@oldmax)
                    ORDER BY storeID ,
                            TrafDate;
    
            SET @newmax = ( SELECT  *
                            FROM    OPENQUERY([ssc2008],
                                              'select max(TimeStamp) as MaxTimeStamp from TMS_Live.dbo.TRAFFIC')
                          )
            SELECT  @newmax AS NewMax
    
            IF @newmax > @oldmax 
                INSERT  INTO EXPORT_TIMESTAMPS
                VALUES  ( @newmax, GETDATE() )
    
    
            SET @RowsInserted = @@ROWCOUNT 
    
            COMMIT TRANSACTION
        END
        
    GO    
    ----------------------------------
    -- call sp:
    DECLARE 
        @RowsProcessed INT  ,
        @RowsInserted INT ,
        @RowsUpdated INT ,
        @RowsDeleted INT ,
        @tUTL_JobLogging_Key INT ,
        @oldmax BIGINT ,		--change this variable to output
        @newmax BIGINT ;
        
    EXEC [Staging].[pODS_Ins_tODS_TMS_StoreTraffic]
        @RowsProcessed  OUTPUT ,
        @RowsInserted  OUTPUT ,
        @RowsUpdated  OUTPUT ,
        @RowsDeleted  OUTPUT ,
        @tUTL_JobLogging_Key  ,
        @oldmax  OUTPUT,		--change this variable to output
        @newmax 
        
    SELECT @oldmax AS oldmax ;


    sqldevelop.wordpress.com

    • Proposed as answer by Naomi N Tuesday, March 4, 2014 9:26 PM
    • Marked as answer by Kalman Toth Saturday, March 15, 2014 6:06 AM
    Tuesday, March 4, 2014 9:12 PM
  • What is the datatype of ExportTimeStamp. Is it a datetime? you can't assign it to bigint.

    -Prashanth

    • Proposed as answer by Naomi N Tuesday, March 4, 2014 9:28 PM
    Tuesday, March 4, 2014 9:20 PM
  • In the declaration of your procedure you have

    @oldmax bigint,
    @newmax bigint

    Instead they are temporary values, so you need to declare them inside your SP.

    Or you can use them as OUTPUT parameters.

    Can you show the exact call of your procedure?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 4, 2014 9:27 PM
  • Also a good point!

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 4, 2014 9:28 PM
  • Sqldev12,

    Is the Export time in this query a datetime?

    select max(ExportTimeStamp) as maxExportTimeStamp from TMS_Live.dbo.EXPORT_TIMESTAMP

    If yes, your variable should also be a Datetime.. Assuming its a Integer, you can use Saeid's solution..

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Tuesday, March 4, 2014 9:32 PM
  • Sorry Guys,

    I thought may be the variable might be the problem but the whole thing is different and my original question is in this location

    http://social.msdn.microsoft.com/Forums/en-US/89d1692e-a1e2-4f27-b82b-e4238adca692/how-to-insert-data-from-a-function-on-server-a-into-a-table-on-server-b?forum=transactsql

    If someone can please help me with this please reply to that post it would be great.

    Thanks

    Tuesday, March 4, 2014 9:36 PM
  • I already provided many replies in that thread.

    Did you test every step individually? Does every step work? If so, how exactly you're calling your procedure and what is the error?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 4, 2014 9:42 PM
  • Hi Naomi,

    As per your request i posted my whole code there.

    Yup,if i run the whole code on the actual server it runs fine ,however i am unable to use it in the linked server procedure.

    The error i get if i try to create the procedure is 

    Must declare the scalar variable "@oldmax".

    Its not taking the parameter inside the linked server.Any way to get around it?

    Thanks

    Tuesday, March 4, 2014 9:48 PM