title: SQL Server: stored procedure with several parameters
body: <p>In the Stored procedure I would like change my code, for use it on other database.
  DATA: is name of a database
  I have other databases for example: DATA2 and DATA3. I need to replace DATA by parameter @LocalBase.</p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM ['' + @localBase + ''].SYS.SCHEMAS WHERE NAME = @DestinationSchema)        
      BEGIN
          SELECT @SQL = N'USE DATA; EXEC(''CREATE SCHEMA '' + @DestinationSchema + '')'
          EXEC sp_executesql @SQL
          INSERT INTO dbo.TableLog SELECT @id, @SourceServer, @SourceSchema, 'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP, 'Schema ' + @DestinationSchema + ' created', 2
      END
  </code></pre>
  
  <p>Is it possible (correct) to do this?
  `</p>
  
  <pre><code>@SQL=N'IF NOT EXISTS (SELECT * FROM ['' + @localBase + ''].SYS.SCHEMAS WHERE NAME = @DestinationSchema)'
  EXEC sp_executesql @SQL
  
      BEGIN
          SELECT @SQL = N'USE ['' + @localBase + '']; EXEC(''CREATE SCHEMA '' + @DestinationSchema + '''')'
          EXEC sp_executesql @SQL
             INSERT INTO dbo.TableLog SELECT @id, @SourceServer, @SourceSchema, 'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP, 'Schema ' + @DestinationSchema + ' created', 2
      END
  </code></pre>
  
  <p>`</p>
  
  <p>I would like to call the stored procedure like that:</p>
  
  <pre><code>DECLARE @RC int
  DECLARE @SourceServer nvarchar(255)
  DECLARE @localBase nvarchar(255)
  
  SET @SourceServer = 'Serv1,10001'
  SET @localBase = 'DATA1'
  
  EXECUTE @RC = [dbo].[stor_proc_name] 
     @SourceServer,
     @localBase
  </code></pre>