Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: Table name as variable

    My sql looks like the following

    select max(date_time) from @table1

    and the want the result of this sql in a variable say @maxdate of datetime datatype. But the tablename for the query is being passed as a parameter.

    so I want the max date field in a table that is passed as a parameter and the this max date value i want to use in figuring the rest of the stuff in the prog.

    Any ideas?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    CREATE TABLE #myTable (Col1 datetime)
    DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @x datetime
    SELECT @TABLE_NAME = 'Orders'
    SELECT @SQL = 'INSERT INTO #myTable (Col1) SELECT MAX(OrderDate) FROM ' + @TABLE_NAME
    EXEC(@SQL)
    SELECT @x = Col1 FROM #myTable
    SELECT @x
    DROP TABLE #myTable
    GO

    ....but don't do it!
    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.

  3. #3
    Join Date
    Oct 2003
    Location
    Arizona
    Posts
    3

    Re: Table name as variable

    Originally posted by vmlal
    My sql looks like the following

    select max(date_time) from @table1

    and the want the result of this sql in a variable say @maxdate of datetime datatype. But the tablename for the query is being passed as a parameter.

    so I want the max date field in a table that is passed as a parameter and the this max date value i want to use in figuring the rest of the stuff in the prog.

    Any ideas?

    Thanks
    declare @t varchar(25), @t2 datetime, @sql varchar(1000)

    -- If you have SQL2000, use a table variable
    create table #t3 (maxdate datetime)

    select @t = yourtable

    select @sql = 'insert into #t3 select max(update_dt) from ' + @t
    select @sql

    execute(@sql)

    select @yourvar = maxdate
    from #t3

    drop table #t3

    Down and dirty, and you may be able to find something else, but this will work fine.

    HTH, Saint

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    Originally posted by Brett Kaiser
    Code:
    USE Northwind
    GO
    CREATE TABLE #myTable (Col1 datetime)
    DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @x datetime
    SELECT @TABLE_NAME = 'Orders'
    SELECT @SQL = 'INSERT INTO #myTable (Col1) SELECT MAX(OrderDate) FROM ' + @TABLE_NAME
    EXEC(@SQL)
    SELECT @x = Col1 FROM #myTable
    SELECT @x
    DROP TABLE #myTable
    GO

    ....but don't do it!
    Why not? I'm gettin the tablename from a sql into a variable, then i wanna use that variable to get the max date in that table.

    Any ideas on how to use dynamic SQL?

  5. #5
    Join Date
    Sep 2003
    Posts
    212
    Thanks that works for now!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many tables do you have to do this for?

    Dynamic SQL can not be compiled, so you'll lose performance benefits..

    Plus there are security risk

    Not to mention, that it might drive your entire coding methodolgy...

    Use it when there is no ther way...
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here...check this out...

    Notice the GO in the statement? Notice how that next statement executes?

    That could be any allowable transact SQL statement, like DROP DATABASE...

    Code:
    USE Northwind
    GO
    CREATE TABLE #myTable (Col1 datetime)
    DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @x datetime
    SELECT @TABLE_NAME = 'Orders'
    SELECT @SQL = 'INSERT INTO #myTable (Col1) SELECT MAX(OrderDate) FROM ' + @TABLE_NAME+ ' GO SELECT * FROM sysobjects'
    EXEC(@SQL)
    SELECT @x = Col1 FROM #myTable
    SELECT @x
    DROP TABLE #myTable
    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.

  8. #8
    Join Date
    Dec 2001
    Location
    India
    Posts
    25
    check it out...

    DECLARE @PARAMLIST NVARCHAR(255),
    @SQLST NVARCHAR(255),
    @TABLENAME NVARCHAR(100),
    @VAL DATETIME
    SET @TABLENAME = 'MYTEST'
    SET @PARAMLIST = '@VAL DATETIME OUTPUT'
    SET @SQLST = 'SELECT @VAL=MAX(VAL) FROM '+@TABLENAME
    EXEC SP_EXECUTESQL @SQLST, @PARAMLIST, @VAL OUTPUT
    SELECT @VAL

    Regards,
    Selva Balaji B.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Could you make it so we could use Northwinf as an example...because this doesn't work...

    Code:
    USE Northwind
    GO
    
    DECLARE @PARAMLIST NVARCHAR(255),@SQLST NVARCHAR(255),@TABLENAME NVARCHAR(100),@VAL DATETIME
    SELECT @TABLENAME = 'Orders', @PARAMLIST = '@VAL DATETIME OUTPUT', @SQLST = 'SELECT @VAL=MAX(VAL) FROM '+@TABLENAME
    EXEC SP_EXECUTESQL @SQLST, @PARAMLIST, @VAL OUTPUT
    SELECT @VAL
    Are you saying that, with this method, you can't insert multiple SQL Statements?
    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.

  10. #10
    Join Date
    Sep 2003
    Posts
    212
    Thanks, that works too!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You cut and pasted my example in Northwind...and you say that worked?

    No way...

    Can cut and paste what worked for you?
    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.

  12. #12
    Join Date
    Sep 2003
    Posts
    212
    No I was acknowledging the example before the North Wind Example

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cool, good luck tommorow!
    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.

Posting Permissions

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