פתרון לבעיית ה-Pivot הדינאמי (2)

כנראה שסוף טוב הכל טוב- לאחר שבפוסט קודם מצאתי פתרון רעוע שהיווה יותר אתגר טכני מאשר פתרון אמין- הפעם מדובר בפתרון יציב ואמין (עד כמה שהספקתי לבדוק אותו): במקום להתבסס על פקודת ה-Pivot החדשה יחסית, נשתמש דווקא בשיטה הישנה יותר- בניית SQL דינאמי.
עד 2005 הפתרון היה נראה בערך כך:

Declare @SQL NVarchar(Max);

Select  @SQL=Isnull(@SQL+','+Char(13)+Char(9)+Char(9),'')+'Count(Case When type_desc='''+type_desc+''' Then 1 End) As ['+type_desc+']'

From    sys.objects

Group By type_desc;

Select  @SQL='Select Schema_id,'+Char(13)+

             Char(9)+Char(9)+@SQL+Char(13)+

             'From sys.objects'+Char(13)+

             'Group By Schema_id;';

Print @SQL;

Exec(@SQL);

למען האמת- כשפגשתי לראשונה את הפתרון בשיטת ה-SQL הדינאמי בימי גרסת 2000 העליזים – זה התחיל עם Cursor שעבר על כל ערכי type_desc ובנה כך את ה-SQL, ואינני זוכר אם שיטת השרשור הזו בעזרת Select לתוך משתנה פעלה אז, אבל בשורה התחתונה- קיבלנו SQL דינאמי.

את כל זה נעטוף כעת ב-SP_ExecuteSQL תוך שאנחנו מכפילים את כל ה'תגים' שבתוך הפקודה (ומשמיטים את פקודת ה-Print):

Exec SP_ExecuteSQL N'

Declare @SQL Varchar(Max);

Select  @SQL=Isnull(@SQL+'',''+Char(13)+Char(9)+Char(9),'''')+''Count(Case When type_desc=''''''+type_desc+'''''' Then 1 End) As [''+type_desc+'']''

From    sys.objects

Group By type_desc;

Select  @SQL=''Select Schema_id,''+Char(13)+

             Char(9)+Char(9)+@SQL+Char(13)+

             ''From sys.objects''+Char(13)+

             ''Group By Schema_id;'';

Exec(@SQL);

';

ולסיום נעטוף את כל זה ב-OpenRowSet תוך שמכפילים שוב את כל 'התגים':

Select * From OpenRowset('MSDASQL','DRIVER={SQL Server}; SERVER=localhost;trusted_connection=yes','Set FmtOnly Off 

Exec SP_ExecuteSQL N''

Declare @SQL Varchar(Max);

Select  @SQL=Isnull(@SQL+'''',''''+Char(13)+Char(9)+Char(9),'''''''')+''''Count(Case When type_desc=''''''''''''+type_desc+'''''''''''' Then 1 End) As [''''+type_desc+'''']''''

From    sys.objects

Group By type_desc;

Select  @SQL=''''Select Schema_id,''''+Char(13)+

             Char(9)+Char(9)+@SQL+Char(13)+

             ''''From sys.objects''''+Char(13)+

             ''''Group By Schema_id;'''';

Exec(@SQL);

'';

');

קצת מורכב, אבל גם דינאמי וגם מאפשר לבצע פילטור, סינון, מיון, Join וכו'.

הקוד הנ"ל נבדק בגרסאות 2005 ו-2008 ועבד ללא דופי. ב-2012 הוא נפל והופיעה הודעת שגיאה כדלקמן:

Msg 11513, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because statement 'Exec SP_ExecuteSQL @SQL' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

With Result Sets הוא אופרטור חדש בגרסת 2012 שנועד להגדיר במפורש ולעצב את סט התוצאות שפרוצדורה מחזירה.

השתמשתי בו כך (תוך שאני מגביל מראש את ערכי העמודות):

Select * From OPENROWSET ('SQLNCLI','Server=(Local);Trusted_Connection=Yes','Set FmtOnly Off

Exec SP_ExecuteSQL N''

Declare @SQL NVarchar(Max);

Select @SQL=Isnull(@SQL+'''',''''+Char(13)+Char(9)+Char(9),'''''''')+''''Count(Case When type_desc=''''''''''''+type_desc+'''''''''''' Then 1 End) As [''''+type_desc+'''']''''

From   sys.objects

Where  type_desc In (''''System_Table'''',''''View'''')

Group By type_desc;

Select @SQL=''''Select Schema_id,''''+Char(13)+

            Char(9)+Char(9)+@SQL+Char(13)+

            ''''From sys.objects''''+Char(13)+

            ''''Group By Schema_id;'''';

Exec   SP_ExecuteSQL @SQL

With Result Sets(([Schema_id] Int Not Null,

                  [System_Table] Int Not Null,

                  [View] Int Not Null));

'';

');

כמובן שכאן הדרא קושיא לדוכתא וכעת עלינו להגדיר מראש ובמפורש את ה-Result Sets.

אני חושד שמיקרוסופט החליטו לסגור את הפרצות של OpenRowset שאיפשרו לנו להכשיר שרצים ולי לכתוב פוסטים..