hi,
[quoted text, click to view]

variables life is limited to the scope of the code declaring them...
if you have a stored procedure that declares a @MaxVal variable, it will be
accessible in the procedure code but not outside of it...
so you should rely on traditional tables you can manipulate form your own
code...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Hi, any help is appreciated. I dont know the best way I can do this
fast and easily.

What I want to do: Move a MAX value of a column to a variable from my
SQL Server and be able to store it as a table or other recommended way,
once this is done I want to be able to query this variable from my
Navision ERP database.

Is is possible to store a variable for an extended amount of time or
should I store the MAX value to its own table? If so does anyone have a
basic sample syntax I can use for this ? Thanks a lot for you help, if
you have any questions please let me know and I will respond ASAP

Thanks Andrea for the quick response. My next question is, what is an
easy way to move a MAX value to a table that would only need one row
and one column(the MAX value I want to query)?

something like:

select max(value) as maxValue
into tableName
from table
group by something

will do it. Or create the table and do

Insert into tableName (value)
select max(value) as maxValue
from table
group by something

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

[quoted text, click to view]

hi,
[quoted text, click to view]

this feature is often used to store "next id" values for tables without an
identity property for the surrogate key, and a specific table stores all
table names (or relative OBJECT_ID , as you prefer) which is queried and
updated when inserting new rows..
something like
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.sometable1 (
Id int NOT NULL
)
CREATE TABLE dbo.sometable2 (
Id int NOT NULL
)

CREATE TABLE dbo.MaxTbValues (
objName varchar(257) NOT NULL ,
MaxVal int
)
INSERT INTO dbo.sometable1 VALUES ( 50 )

GO
DECLARE @obj varchar(257) , @max int

SELECT @obj = 'dbo.sometable1' , @max = (SELECT ISNULL( MAX (Id) , 0 ) FROM
dbo.sometable1 )
SELECT @obj , @max
IF NOT EXISTS ( SELECT 1 FROM dbo.MaxTbValues WHERE objName = @obj ) BEGIN
INSERT INTO dbo.MaxTbValues VALUES ( @obj , @max )
END
ELSE BEGIN
UPDATE dbo.MaxTbValues SET MaxVal = @max FROM dbo.MaxTbValues WHERE objName
= @obj
END

INSERT INTO dbo.sometable1 VALUES ( 1000 )

SELECT @obj = 'dbo.sometable1' , @max = (SELECT ISNULL( MAX (Id) , 0 ) FROM
dbo.sometable1 )
SELECT @obj , @max
IF NOT EXISTS ( SELECT 1 FROM dbo.MaxTbValues WHERE objName = @obj ) BEGIN
INSERT INTO dbo.MaxTbValues VALUES ( @obj , @max )
END
ELSE BEGIN
UPDATE dbo.MaxTbValues SET MaxVal = @max FROM dbo.MaxTbValues WHERE objName
= @obj
END


SELECT @obj = 'dbo.sometable2' , @max = (SELECT ISNULL( MAX (Id) , 0 ) FROM
dbo.sometable2 )
SELECT @obj , @max
IF NOT EXISTS ( SELECT 1 FROM dbo.MaxTbValues WHERE objName = @obj ) BEGIN
INSERT INTO dbo.MaxTbValues VALUES ( @obj , @max )
END
ELSE BEGIN
UPDATE dbo.MaxTbValues SET MaxVal = @max FROM dbo.MaxTbValues WHERE objName
= @obj
END

SELECT * FROM dbo.MaxTbValues
GO
DROP TABLE dbo.sometable1 , dbo.sometable2 , dbo.MaxTbValues

where you store the object name and it's relative MAX value you can later
query for your defined purposes..
for the surrogate key scenario this solution does not scale very well, but
it is used...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply