Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Posts
    13

    Unanswered: select from table

    Hello,

    I would like to do a select from a table which name is in a varchar

    for example:

    declare @table varchar(15)
    set @table = '_05_00002_B'
    select * from @table

    but this doesn't work

    does anybody know a way to do this?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    exec ('select * from ' + @table)

  3. #3
    Join Date
    Aug 2005
    Posts
    13
    It workes but I have another problem:

    DECLARE @sBorderel varchar(13)
    DECLARE @sTable varchar(11)
    DECLARE @sExec varchar(200)
    SET @sBorderel = '05/00002/0007'
    SET @sTable = '_05_00002_B'
    SET @sExec = 'SELECT * FROM ' + @sTable + ' WHERE sBorderel = ' + @sBorderel
    EXEC(@sExec)

    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value '05/00002/0001' to a column of data type int.

    SQLserver divides 5 by 2 by 7 which is 0 (int)

    @sBorderel has to remain a varchar

    I also tried this:

    DECLARE @sBorderel varchar(13)
    DECLARE @sTable varchar(11)
    DECLARE @sExec varchar(200)
    SET @sBorderel = '05/00002/0007'
    SET @sTable = '_05_00002_B'
    SET @sExec = 'SELECT * FROM ' + @sTable + ' WHERE sBorderel = ' + convert(varchar,@sBorderel)
    EXEC(@sExec)


    but same problem

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Try adding quotes around @sBorderel in the where clause so your @sExec looks like: SELECT * FROM _05_00002_B WHERE sBorderel = '05/00002/0007'

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi

    try this out

    DECLARE @sBorderel varchar(13)
    DECLARE @sTable varchar(11)
    DECLARE @sExec varchar(200)
    SET @sBorderel = '05/00002/0007'
    SET @sTable = 'TableName'

    SET @sExec = 'SELECT * FROM ' + @sTable + ' WHERE FieldName =' + '''05/00002/0007'''
    EXEC(@sExec)
    Cheers....

    baburajv

  6. #6
    Join Date
    Aug 2005
    Posts
    13
    can I use exec within a function?

    I have a function that returns a variable @var

    I tried Set @var = exec(...)

    but I got an error: invalid use of exec within a function

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    A good place to start is BOL (Books OnLine). Look up "functions, user-defined" in the index. If you had, you would have found this:



    The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:
    • Assignment statements.
    • Control-of-Flow statements.
    • DECLARE statements defining data variables and cursors that are local to the function.
    • SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
    • Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
    • INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
    • EXECUTE statements calling an extended stored procedures.


    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Aug 2005
    Posts
    13
    OK so it is allowed

    but how can I get the value in my variable?


    I tried

    Set @var = EXEC('SELECT COUNT(*) FROM ...')

    and

    EXEC('SET ' + @var + ' = SELECT COUNT(*) FROM ...')

    but I get this error:

    Invalid use of 'EXECUTE' within a function.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I got an idea...read the sticky at the top of the column, and then describe to us what you are trying to accomplish...

    What you are trying to force is conceptually quite wrong.
    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
    May 2002
    Posts
    299
    Here is an example for you.

    declare @i int,
    @sql nvarchar(1000),
    @tb sysname

    set @tb='Orders'
    set @sql = 'select @i=count(*) from Northwind..'+@tb

    exec sp_executesql @sql, N'@i int out', @i out

    select @i as cnt
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

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