Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2007
    Posts
    16

    Unanswered: Get Output from one SP in to another SP

    Hi I have an SP OmgångsVal
    Code:
    ALTER PROCEDURE OmgångsVal 
    @SexVal nvarchar,
    @Sasong int
    
    AS
    BEGIN
        
    SET NOCOUNT ON;
    
    SELECT MAX(Omgang) AS [Omgång]
    FROM Resultat 
    WHERE @SexVal = Lag And @Sasong = Säsong
    END
    And want [Omgång] as input in next SP

    Code:
    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 = @Omgång
    But i get error:
    Msg 102, Level 15, State 1, Line 16
    Incorrect syntax near '@Omgång'.

    B Regards
    Gert

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your sproc doesn't return a value...
    Take a look at the keyword OUTPUT when defining your sproc
    Code:
    ALTER PROCEDURE OmgångsVal 
    @SexVal nvarchar,
    @Sasong int,
    @test int OUTPUT
    AS
    BEGIN
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your derived table looks unnecessary.

    And why not make it all in one pass?

    Code:
    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)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't use sprocs for variable assignments. Use user-defined functions instead:
    Code:
    Create function 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
    Call it like this:
    Code:
    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.Omgång = dbo.OmgångsVal(@SexVal, @Sasong)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2007
    Posts
    16
    Got error:
    Msg 102, Level 15, State 1, Line 16
    Incorrect syntax near ')'.

    Code:
    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)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This probably isn't perfect, but it ought to get you started:
    Code:
    --  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

  8. #8
    Join Date
    Sep 2007
    Posts
    16
    Now i use that Blindman wrote with user-defined functions
    And it works fine.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    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

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Absolutely - I just wanted to check the optimser knew that. It should and I suspected it would - I just wasn't sure.

Posting Permissions

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