Get Output from one SP in to another SP

Hi I have an SP OmgngsVal
ALTER PROCEDURE OmgngsVal
@.SexVal nvarchar,
@.Sasong int

AS
BEGIN

SET NOCOUNT ON;

SELECT MAX(Omgang) AS [Omgng]
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Ssong
END

And want [Omgng] as input in next SP

DECLARE
@.SexVal nvarchar,
@.Omgng INT,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008
EXEC Ubc90OmgngsVal @.SexVal,@.Sasong

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgng = @.Omgng

But i get error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@.Omgng'.

B Regards
GertYour sproc doesn't return a value...
Take a look at the keyword OUTPUT when defining your sproc

ALTER PROCEDURE OmgångsVal
@.SexVal nvarchar,
@.Sasong int,
@.test int OUTPUT
AS
BEGIN
...|||Your derived table looks unnecessary.

And why not make it all in one pass?

SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång = (SELECT MAX(Omgang)
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Säsong)|||Don't use sprocs for variable assignments. Use user-defined functions instead:
Create function OmgngsVal
(@.SexVal nvarchar,
@.Sasong int)
returns int

AS
begin
return
(SELECT MAX(Omgang) AS [Omgng]
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Ssong)
end

Call it like this:
DECLARE
@.SexVal nvarchar,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008

SELECT Match.MatchId,
Match.matchdate AS MatchStart,
Team.team AS Hemma,
Team1.team AS Borta,
Match.score,
Match.vsscore
FROM Match
INNER JOIN Team ON Match.team = Team.TeamId
INNER JOIN --Team
(SELECT TeamId,
team,
GroupId
FROM Team) AS Team1
ON Match.vsteam = Team1.TeamId
WHERE Match.Omgng = dbo.OmgngsVal(@.SexVal, @.Sasong)|||Got error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

DECLARE
@.SexVal nvarchar,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma,
Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång =(SELECT MAX(Omgang)
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Säsong)|||There's no syntax error. Did you copy and paste what you put in your post exactly as you had in the query analyser\ management studio?|||This probably isn't perfect, but it ought to get you started:-- ptp 200710111 See http://www.dbforums.com/showthread.php?t=1623252
GO
-- ptp 200710111 Return the highest Omgang value

CREATE FUNCTION dbo.dbforums_OmgångsVal (
@.SexVal nvarchar
, @.Sasong int)
RETURNS INT AS BEGIN

RETURN (SELECT MAX(Omgang) AS [Omgång]
FROM Resultat
WHERE @.SexVal = Lag
AND @.Sasong = Säsong)
END
GO
-- ptp 200710111 Procedure to demonstrate use of dbo.dbforums_OmgångsVal

CREATE PROCEDURE dbo.dbforums_otherprocedure
AS

DECLARE
@.SexVal NVARCHAR
, @.Omgång INT
, @.Sasong INT

SET @.SexVal='A'
SET @.Sasong=20072008
EXEC Ubc90OmgångsVal @.SexVal,@.Sasong

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång = dbo.dbforums_OmgångsVal(@.SexVal, @.Sasong)
END-PatP|||Now i use that Blindman wrote with user-defined functions
And it works fine.|||Possibly a bit of a dumb question blindman but does the optimiser defo recognise that the scaler function will always return the same value and so execute it only once? I think I recall once finding a problem and ended up putting a function like that into a derived table to make it explicit to SQL Server that it was not to run it once per row.|||Because none of the parameters for the UDF are derived from the recordsets (@.SexVal and @.Sasong are essentially constants for the duration of the transaction), the UDF should only be called once.|||does the optimiser defo recognise that the scaler function will always return the same value and so execute it only once?This is part of why Transact-SQL functions can only call deterministic functions. A deterministic function always returns the same value for a given set of arguments.

This means that for the duration of a transaction (and every SQL statement executed by Microsoft SQL Server is part of a transaction whether it is explicitly declared or not), a UDF will always return the same value for any given set of arguments.

Following that logic one step further, a UDF only needs to be called repeatedly if one of its arguments is a column. Constants and variables should not change within the context of a single Transact SQL statement, so there is no need to re-evaluate the function call.

-PatP|||Absolutely - I just wanted to check the optimser knew that. It should and I suspected it would - I just wasn't sure.