How to assign a value to a variable

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

Answer 1

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

SELECT @oldmax

Answer 2

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.

Answer 3

Hi,

Try

SELECT @OldMax AS OldMax

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

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

Answer 5

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

Answer 6

What is the datatype of ExportTimeStamp. Is it a datetime? you can't assign it to bigint.

-Prashanth

Answer 7

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

Answer 8

Also a good point!

---

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


My blog

My TechNet articles

Answer 9

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.

Answer 10

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

Answer 11

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

Answer 12

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