Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Italy
    Posts
    7

    Unanswered: Stored procedure

    Hello to all,

    I would like to create the following stored procedure:

    #########################################
    CREATE PROC dbo.CopyFromDB

    @DBFrom varchar(50) ,
    @DBTo varchar(50),
    @RES_ID uniqueidentifier

    AS

    declare @FromName varchar(100)
    declare @ToName varchar(100)
    declare @IDResTableTo varchar(100)
    declare @Table varchar(25)

    SET @Table = '.dbo.Tab_Headers'
    SET @FromName = @DBFrom + @Table
    SET @ToName = @DBTo + @Table
    SET @IDResTableTo = @ToName + '.ID_Result'

    INSERT INTO @ToName SELECT * FROM @FromName WHERE @IDResTableTo = @RES_ID

    ###########################################

    but I have an error on the insert instruction regard the variables declaration.

    Is this kind of query construction possible?
    Am I wrong in somewhere?

    Thank you

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    Quote Originally Posted by Emafal

    INSERT INTO @ToName SELECT * FROM @FromName WHERE @IDResTableTo = @RES_ID
    you can't do that...

    construct the statement into a string..

    declare @sqlstmt varchar(200)

    set @sqlstmt = 'INSERT INTO ' + @ToName + ' SELECT * FROM ' + @FromName + 'WHERE ' + ...

    (take note : convert any non char-type variables to [VAR]CHAR ...)

    execute (@sqlstmt)

    HTH,

    Bill

Posting Permissions

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