Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Unanswered: How do you pass variables to a statement plz help

    iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong

    here is the code


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20)
    set @TABLEDESC = 'ClientComment'
    set @DBO = '.dbo.'
    set @BDFR = 'Commander' + @DBO + @TABLEDESC
    set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC
    set @EQID = '80_300_113'
    insert into @BDTO
    select from @BDFR where Eqid = @EQID

  2. #2
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    Is this something that is going to happen for multiple sets of tables, or just this set? If it is just this set, hard code the things. In addition, BOL states, "Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE."

    Dandy

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why the Double post?

    -PatP

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Maybe he is building a fence

    [BLOCK]

    |------------|----------|
    |------------|----------|
    |------------|----------|

    [/BLOCK]

  5. #5
    Join Date
    May 2004
    Posts
    9

    Thumbs up Wrong syntax

    Hello hillcat, Your syntax is completely wrong,

    1st, you cannot insert into data in a variable other than a table type
    2nd, your select stmnt select from @BDFR where Eqid = @EQID has to be like that select @BDFR from @BDTO where Eqid = @EQID

    Hope this helps.

  6. #6
    Join Date
    Mar 2005
    Location
    Philippines, Makati
    Posts
    11
    hillcat,

    what your trying to do is to create an sql statement dynamically. This would work by declaring a variable to store your sql statement and execute the query from your variable.

    declare @sqlString NVARCHAR(1000)

    SET @sqlString = 'insert into ' + @BDTO +
    ' select from ' + @BDFR +
    ' where Eqid = ' + @EQID

    EXEC sp_ExecuteSql @sqlString

    Regards,
    K3n

Posting Permissions

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