select * from table order by @col

이 경우는 동적 SQL을 사용하지 않고 다음과 같이 처리하는 것이 가능하다:

<PRE> SELECT col1, col2, col3 FROM tbl ORDER BY CASE @col1 WHEN 'col1' THEN col1 WHEN 'col2' THEN col2 WHEN 'col3' THEN col3 END</PRE>

다시 한번 말하지만, 이해가 가지 않는다면 온라인 도움말의 Case 표현을 참조하라.

열(column)의 데이타 형태가 다른 경우에는 하나의 Case 표현식으로 나타낼 수 없다는 점을 기억하라. 이런 경우에는 다음과 같이 처리할 수 있다.:

<PRE> SELECT col1, col2, col3 FROM tbl ORDER BY CASE @col1 WHEN 'col1' THEN col1 ELSE NULL END, CASE @col1 WHEN 'col2' THEN col2 ELSE NULL END, CASE @col1 WHEN 'col3' THEN col3 ELSE NULL END</PRE>

이 주제에 대해 SQL Server MVP인 Itzik Ben-Gan이 SQL Server Magazine 2001년 3월호에서 투고한 좋은 기사에서 다른 해결책을 제시하였다.

select top @n from table order by @col

동적 SQL을 사용하지 않는 간단한 방법이 아래에 나와있다.:

<PRE> CREATE PROCEDURE get_first_n @var int WITH RECOMPILE AS SET ROWCOUNT @var SELECT * FROM authors ORDER BY au_id SET ROWCOUNT 0</PRE>

SQL 옵티마이저는 SET ROWCOUNT 옵션 설정값을 무시한다고 배웠을 수 있다. TOP이 제공되지 않아 다른 대안이 존재하지 않았던 SQL 6.5에서는 이 말이 사실이었다. 하지만 SQL 7과 SQL 2000에서는 그렇지 않다. 그러므로, SET ROWCOUNT의 입력값으로 사용하기 위해 매개변수(지역변수가 아닌)를 주의해서 사용하지 않는다면, 옵티마이저는 그 값을 인식하지 못하고 테이블 스캔을 하려고 할 것이다.

해당 프로시저 내의 다른 SQL문에도 영향을 미치게 되므로, Select 문 다음에 SET ROWCOUNT 0을 사용해야 한다는 점도 주의하라.

온라인 도움말에 보면 SET ROWCOUNT에 대한 참고 사항이 나와있는데, SET ROWCOUNT를 Delete, Insert 및 Update 문과 함께 사용하는 것을 권장하지 않는다. 정확히 왜 그런지는 잘 모르겠으나, SET ROWCOUNT 옵션이 설정된 상태에서 임시 테이블에 대한 INSERT는 괜찮다는 것을 제안하고 싶다.(?) ROWCOUNT 옵션 설정값은 트리거에도 영향을 미치기 때문에, 트리거가 걸린 테이블에 대한 Insert는 엉뚱한 결과를 일으킬 수 있다.

왜 이 기능을 사용해야 하는지 살펴보는 것은 의미있는 일이 될 것이다. 만약 웹페이지에 출력할 경우, 한번에 500개의 행을 읽어들여서 전체 데이타베이스를 뒤지지 않게 하는 것은 좋은 전략이다. 사용자는 다음 화면을 볼 때 "Next" 버튼을 클릭하면 된다. (개인적으로는, 한번에 보여지는 결과를 10-20개로 제한해놓은 웹사이트를 좋아하지 않는다.)

create table @tbl

이 경우는 권한 혹은 캐쉬와 관련된 문제가 없으며(동적 SQL을 사용하지 않더라도, 저장 프로시저에서 사용자가 테이블 생성권한을 필요로 하기 때문이다.) 의존성 문제와도 관련이 없다. 이 목적으로 동적 SQL을 사용하는 것에 대한 별다른 논쟁거리는 없다.

그럼에도 불구하고 여전히 다음과 같은 질문은 남아있다: 왜? 왜 이런 식으로 작업해야 하는가? 서로 유사한 일련의 테이블을 생성시킬 필요가 있는 관리자용의 스크립트 제작을 위한 작업인 경우는 의미있다. 하지만 응용프로그램에서 실시간으로 테이블을 생성시켜야 한다면, 이것은 데이타베이스 디자인의 기본사항을 망각한 것이다. 관계형 데이타베이스에서 테이블 및 칼럼은 고정적인 개체인 것으로 가정된다. 새로운 버전의 설치시에는 변경될 수 있겠지만, 실행시간에 변경되어서는 안된다. select * from sales + @yymm에서 이 주제에 대해 살펴보라.

간혹 임시로 사용되는 테이블의 이름을 유일하게(unique) 주기 위해 이런 작업방식을 사용하는 사람을 보는 경우도 있는데, 이것은 SQL 서버에서 기본적으로 제공되는 기능으로, 불필요한 작업이다. 다음과 같이 사용하면 된다:

<PRE> CREATE TABLE #nisse (a int NOT NULL)</PRE>

실제 테이블 이름은 보여지는 것보다 훨씬 긴 이름이 사용되며, 다른 사용자는 #nisse의 인스턴스를 들여다볼 수 없다.

Disconnected record sets를 사용하거나 혹은 임시 테이블을 사용할 수 없는 경우, 연결(connection)에 대해 유일한 테이블을 사용하고 싶다면 모든 클라이언트가 공유할 수 있고, 각각의 클라이언트를 의미하는 키값을 가지는 칼럼을 추가한 영구 테이블을 사용하는 편이 더 낫다.

Linked servers

이것은 데이타베이스 이름을 변수로 사용하고자 하는 문제와 유사하지만, 해결책은 다르다. 연결된 서버에 저장 프로시저를 정의하는 것이 가능하다면, 저장 프로시저의 이름을 동적으로 사용하는 것 또한 가능하다.:

<PRE> SET @sp = @server + 'db.dbo.some_sp' EXEC @ret = @sp @par1, @par2...</PRE>

로컬에 존재하는 테이블과, 연결된 서버의 유동적으로 결정되는 원격 테이블에 대해 조인작업을 수행하고 싶다면, 동적 SQL이 아마도 가장 좋은 방법일 것이다.

비록 특정 환경에서 사용가능한 방법이긴 하지만 여기에도 여전히 대안이 존재한다. 다음에서 인용된 예와 같이, sp_addlinkedserver에서 별칭을 만들 수 있다.: <PRE> EXEC sp_addlinkedserver MYSRV, @srvproduct='Any', @provider='SQLOLEDB', @datasrc=@@SERVERNAME go CREATE PROCEDURE linksrv_demo_inner WITH RECOMPILE AS SELECT * FROM MYSRV.master.dbo.sysdatabases go EXEC sp_dropserver MYSRV go CREATE PROCEDURE linksrv_demo @server sysname AS IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'MYSRV') EXEC sp_dropserver MYSRV EXEC sp_addlinkedserver MYSRV, @srvproduct='Any', @provider='SQLOLEDB', @datasrc=@server EXEC linksrv_demo_inner EXEC sp_dropserver MYSRV go EXEC linksrv_demo 'Server1' EXEC linksrv_demo 'Server2'</PRE>

2개의 프로시저가 사용되었는데, 내부의 프로시저는 실행시간에 조회하려고 하는 연결된 서버의 별칭으로 MYSRV를 사용하였고, 작업이 완료되면 별칭을 없앤다. 내부의 프로시저에 링크드 서버에 실제로 접속하기 위한 코드가 포함되어 있다. 다른 서버를 가리키는 실행계획이 필요하지 않다는 것을 확실히 하기 위해 WITH RECOMPILE 옵션을 사용하였다.

위의 예는 다음과 같은 조건에서만 사용가능하다.:

• 프로시저는 연결된 서버를 구성할 수 있는 권한을 가진 사용자에 의해 실행되어야 한다. 일반적으로 sysadmin 혹은 setupadmin 고정서버역할이 이런 권한을 갖고 있다. 그러므로, 일반 사용자에게는 적용되지 않는다.
• 서버에 영향을 미치는 정의를 변경하는 것이므로, 실행되는 프로시져에 대한 여러개의 인스턴스를 실행시킬 수 없다. (말할 필요도 없이, 해당 별칭은 이 프로시져 내부에서만 사용하여야 한다.)(?)

주의: If you test are likely to find that it works without WITH RECOMPILE.. You may get it work to have the call to sp_addlinkedserver in the same procedure as the reference to the linked server, but if the linked server is not defined when SQL Server needs to build a query plan for the procedure, the procedure will fail..

OPENQUERY

행집합(row-set)을 반환하는 함수인 Openquery와 Openrowset을 사용할 때는 종종 동적 SQL이 필요하다. 해당 함수들의 2번째 매개변수는 SQL문으로, 변수를 사용할 수 없다. 작은 따옴표를 여러번 사용해야 하는 문제로 종종 혼란스럽기도 한데, 이전에 제시한 quotestring()을 사용하면 많은 도움이 될 것이다.: <PRE> DECLARE @remotesql nvarchar(4000), @localsql nvarchar(4000), @state char(2) SELECT @state = 'CA' SELECT @remotesql = 'SELECT * FROM pubs.dbo.authors WHERE state = ' + dbo.quotestring(@state) SELECT @localsql = 'SELECT * FROM OPENQUERY(MYSRV, ' + dbo.quotestring(@remotesql) + ')', PRINT @localsql EXEC (@localsql)</PRE>

SQL문의 길이가 입력한계인 129자를 넘는 경우가 많기 때문에, 내장함수인 quotename()은 쓰이지 못하는 경우가 많다.

열의 너비를 동적으로 변화시키고자 할 때 (Dynamic Column Widths)

쿼리 분석기에서 실행되며, 어떤 종류의 데이타 출력을 위한 저장 프로시저를 생각해보자 (아마도 대부분 관리자용 프로시져일 것이다.) 결과를 잘 볼 수 있게 하려면 데이타가 짤려서 보이지 않도록 열의 너비가 충분해야 하지만, 필요이상의 공간이 보여질 필요도 없다. 이런 경우 동적 SQL을 활용하여 해결할 수 있다. 대개 이런 작업은 임시테이블을 사용하게 되는데, 권한 문제를 신경쓰지 않아도 무방하기 때문이다.

여기서 예제를 제시하지는 않았지만, 온라인 도움말에 나오지는 않지만 많이 알려져 있는 시스템 프로시저 sp_who2가 가장 접하기 쉬운 예이다. exec master..sp_helptext sp_who2 쿼리를 실행시키거나, 쿼리분석기 혹은 EM의 개체 브라우저를 사용하여 코드를 직접 볼 수 있다.

aba_lockinfo에서도 다른 예를 찾아볼 수 있다.