Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: How to retrieve value from Exec stmt?

    hi, all..
    the following is part of my sp
    I want to know how to assign result of stmt.1 to @tp

    declare @tp datetime
    declare @tableName varchar(100)
    SET @tableName = 'tblState'
    Exec ('SELECT MAX(UpdateTime) FROM ' + @tableName) -- stmt.1

    thank you..
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Try putting everything in an EXEC statement:
    Code:
    EXEC('
    declare @tp datetime
    declare @tableName varchar(100)
    SET @tableName = ''tblState''
    SELECT MAX(UpdateTime) FROM @tableName
    ')
    I can't say this is the optimal way to do it, but it should work. Note: Those are two single quotes around tblState, not double quotes.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Apr 2004
    Posts
    7

    Wink Try This

    declare @tp datetime
    declare @tableName varchar(100)
    SET @tableName = 'tblState'
    EXEC sp_executesql 'SELECT @tp=MAX(UpdateTime) FROM ' + @tableName, N'@tp datetime OUTPUT',@tp OUTPUT

    select @tp

    it should give u the exact result.

  4. #4
    Join Date
    Apr 2004
    Posts
    49
    Thank you for reply,
    however it gives following err...
    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near '+'.
    MCSD .NET, SCJP, SCJWD

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Modifying the previous example, I'd use:
    Code:
    declare @sql VARCHAR(250)declare @tp datetime
    declare @tableName varchar(100)
    SET @tableName = 'tblState'
    SET @sql = 'SELECT @tp=MAX(UpdateTime) FROM ' + @tableName
    
    EXEC sp_executesql @sql, N'@tp datetime OUTPUT',@tp OUTPUT
    
    select @tp
    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    
    DECLARE @tp datetime, @TABLE_NAME sysname, @COLUMN_NAME sysname, @SQL varchar(8000)
    
    SELECT @TABLE_NAME  = 'Orders', @COLUMN_NAME = 'OrderDate'
    
    CREATE TABLE #x(tp datetime)
    
    SET @SQL = 'INSERT INTO #x(tp) SELECT MAX('+@COLUMN_NAME+') FROM '+@TABLE_NAME
    
    EXEC(@SQL)
    
    SELECT @tp = tp FROM #x
    
    SELECT @tp
    
    DROP TABLE #x
    
    SET NOCOUNT OFF
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Posts
    49
    Thank you all..
    All are good hint..
    Now it works..

    my version is following..

    DECLARE @SQLString NVARCHAR(500), @tbl nvarchar(100)
    DECLARE @ParmDefinition NVARCHAR(500)
    declare @tp datetime
    SET @tbl = 'tblState'
    SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
    SET @ParmDefinition = N'@tp datetime OUTPUT'
    EXECUTE sp_executesql @SQLString, @ParmDefinition
    ,@tp OUTPUT
    Select @tp as UpdateTime
    MCSD .NET, SCJP, SCJWD

Posting Permissions

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