[SQL] SQL 서버 : 어떻게 저장 프로 시저의 매개 변수로 데이터베이스 이름을 얻을 수 있습니다

SQL 서버 : 어떻게 저장 프로 시저의 매개 변수로 데이터베이스 이름을 얻을 수 있습니다

나는 sys.tables 테이블을 조회하는 간단한 저장 프로 시저를 만들려고 해요.

CREATE PROCEDURE dbo.test
    @dbname NVARCHAR(255),
    @col NVARCHAR(255)
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    USE @dbname

    SELECT TOP 100 *
    FROM sys.tables 
    WHERE name = @col
GO

내가 사용 @dbname 후 GO를 넣어해야하지만이 절차의 작성을 종료 원인이 작동하지 않는 것? 어떻게 사용자가이 시저의 매개 변수로 데이터베이스 이름을 지정할 수 있도록하는 것이이 과정에이 데이터베이스 selction를 넣을 수 있습니다?

해결법

1. ==============================

   1.이 작업을 수행하는 적어도 두 가지 방법이 있습니다 :

   이 작업을 수행하는 적어도 두 가지 방법이 있습니다 :

   물론, 예를 들어, 당신은 할 수

       set @sql='select * from '+@dbname+'.sys.tables';
   

   . . 해상도 연산자는 사용 문을 사용하지 않고 다른 데이터베이스에 쿼리 객체로 할 수 있습니다.

   sproc에 임의의 데이터베이스를 사용할 수 있도록하는 것이 바람직 할 수있는 매우, 매우 드문 경우가 있습니다. 제 생각에는, 유일한 허용 사용은 코드 생성기, 또는 시간의 필요한 정보를 미리 알 수없는 데이터베이스 분석 도구의 일종이다.

   당신이 밖으로 업데이트 턴은 유일하게 확실한 방법으로 동적 SQL을 떠나, 저장 프로 시저에서 사용할 수 없습니다. 그래도, 내가 사용하는 것이 좋습니다 것

   select top 100 * from db_name.dbo.table_name
   

   오히려 사용하는 것보다.

2. ==============================

   2.@var 전달 된 이름과 일치하는 저장 프로 시저에 대한 SQL 서버 외모 - 당신은 (즉,하지 EXEC (@var) 괄호없이) EXEC @var 사용하는 경우. 이 세 부분의 이름을 사용할 수 있습니다.

   @var 전달 된 이름과 일치하는 저장 프로 시저에 대한 SQL 서버 외모 - 당신은 (즉,하지 EXEC (@var) 괄호없이) EXEC @var 사용하는 경우. 이 세 부분의 이름을 사용할 수 있습니다.

   sys.sp_executesql은 세 부분으로 된 이름으로 호출하면 컨텍스트가 호출하는 데이터베이스로 설정됩니다.

   그래서 당신은 아래와 같이 제로 SQL 주입 위험이 작업을 수행 할 수 있습니다.

   CREATE PROCEDURE dbo.test @dbname SYSNAME,
                             @col    SYSNAME
   AS
       SET NOCOUNT, XACT_ABORT ON;
   
       DECLARE @db_sp_executesql NVARCHAR(300) = QUOTENAME(@dbname) + '.sys.sp_executesql'
   
       EXEC @db_sp_executesql N'
                               SELECT TOP 100 *
                               FROM sys.columns 
                               WHERE name = @col',
                              N'@col sysname',
                              @col = @col 
   

   심지어 위의 경우에하는 것은 내가 아직도 여기로 안전한 방식이 동적 SQL을 사용할 수 있도록 완벽하게 가능하다고 주장 줄 수 없었습니다.

   CREATE PROCEDURE dbo.test
       @dbname SYSNAME, /*Use Correct Datatypes for identifiers*/
       @col SYSNAME
   AS
       SET NOCOUNT ON
       SET XACT_ABORT ON
   
       IF DB_ID(@dbname) IS NULL  /*Validate the database name exists*/
          BEGIN
          RAISERROR('Invalid Database Name passed',16,1)
          RETURN
          END
   
   DECLARE @dynsql nvarchar(max)  
   
    /*Use QUOTENAME to correctly escape any special characters*/
   SET @dynsql = N'USE '+ QUOTENAME(@dbname) + N'
   
                            SELECT TOP 100 *
                            FROM sys.tables 
                            WHERE name = @col'
   
    /*Use sp_executesql to leave the WHERE clause parameterised*/
   EXEC sp_executesql @dynsql, N'@col sysname', @col = @col
   

3. ==============================

   3.이 작업을 수행 할 수있는 유일한 방법은 강력하지만 위험 동적 SQL을 사용하는 것입니다.

   이 작업을 수행 할 수있는 유일한 방법은 강력하지만 위험 동적 SQL을 사용하는 것입니다.

   앞서이 문서를 읽어보십시오.

4. ==============================

   4.같은 말에 대한 다른 방법은 시스템 저장 프로 시저를 사용하는 것입니다.

   같은 말에 대한 다른 방법은 시스템 저장 프로 시저를 사용하는 것입니다.

   여러 데이터베이스에서 실행 - 참조 SQL 프로 시저 (들)을 저장.

   프로 시저 이름이 "SP_"로 시작하면, 마스터 DB에 후는 다음과 같이 호출 할 수 있습니다, sys.sp_MS_MarkSystemObject 표시 :

   Exec somedb.dbo.Test;
   Exec anotherdb.dbo.Test;
   

   또는과 같습니다 :

   Declare @Proc_Name sysname;
   Set @Proc_Name = 'somedb.dbo.Test';
   Exec @Proc_Name;
   

   매개 변수도 사용할 수 있습니다.

   이 기술을 사용하면 시스템 데이터베이스에 'SP_'접두사와 퍼팅 코드를 사용이 필요합니다. 그 오프셋은 동적 SQL을 사용하지 않을 경우, 그것은 당신의 선택입니다.

from https://stackoverflow.com/questions/3943823/sql-server-how-to-get-a-database-name-as-a-parameter-in-a-stored-procedure by cc-by-sa and MIT license