شرح - stored procedure sql




إدراج نتائج إجراء مخزن في جدول مؤقت (16)

كيف أفعل SELECT * INTO [temp table] FROM [stored procedure] ؟ ليس FROM [Table] وبدون تحديد [temp table] ؟

Select جميع البيانات من BusinessLine إلى tmpBusLine يعمل بشكل جيد.

select *
into tmpBusLine
from BusinessLine

أنا أحاول نفس الشيء ، ولكن استخدام إجراء stored procedure بإرجاع البيانات ، ليس تماماً كما هو.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

رسالة الإخراج:

Msg 156، Level 15، State 1، Line 2 Incorrect syntax near the word 'exec'.

لقد قرأت عدة أمثلة على إنشاء جدول مؤقت له نفس بنية الإجراء المخزن المخزن ، والذي يعمل بشكل جيد ، ولكن سيكون من الجيد عدم توفير أي أعمدة.


الحل الأسهل:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

إذا كنت لا تعرف المخطط ، فيمكنك القيام بما يلي. يرجى ملاحظة أن هناك مخاطر أمنية شديدة في هذه الطريقة.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

  1. أنا بصدد إنشاء جدول يحتوي على المخطط والبيانات التاليين.
  2. قم بإنشاء إجراء مخزن.
  3. الآن أعرف ما هي نتيجة الإجراء الخاص بي ، لذلك أقوم بإجراء الاستعلام التالي.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
    CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF
    
    create procedure GetDate
    as
    begin
        select Id,ParentId from tblTestingTree
    end
    
    create table tbltemp
    (
        id int,
        ParentId int
    )
    insert into tbltemp
    exec GetDate
    
    select * from tbltemp
    

إذا كان الاستعلام لا يحتوي على معلمة ، فاستخدم OpenQuery else استخدم OpenRowset .

الشيء الأساسي سيكون إنشاء مخطط حسب الإجراء المخزن وإدراج في هذا الجدول. على سبيل المثال:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc

إذا كان جدول نتائج proc المخزنة معقدًا للغاية لكتابة عبارة "إنشاء جدول" يدويًا ، ولا يمكنك استخدام OPENQUERY أو OPENROWSET ، يمكنك استخدام sp_help لإنشاء قائمة الأعمدة وأنواع البيانات نيابة عنك. وبمجرد الانتهاء من قائمة الأعمدة ، فإنها مجرد مسألة تنسيقها لتناسب احتياجاتك.

الخطوة 1: إضافة "إلى #temp" إلى استعلام الإخراج (على سبيل المثال "حدد [...] إلى #temp من [...]").

أسهل طريقة لتحرير استعلام الإخراج في proc مباشرة. إذا لم تتمكن من تغيير البرنامج المخزن ، يمكنك نسخ المحتويات إلى نافذة استعلام جديدة وتعديل الاستعلام هناك.

الخطوة 2: قم بتشغيل sp_help على الجدول المؤقت. (على سبيل المثال "exec tempdb..sp_help #temp")

بعد إنشاء الجدول المؤقت ، قم بتشغيل sp_help على الجدول المؤقت للحصول على قائمة بالأعمدة وأنواع البيانات بما في ذلك حجم حقول varchar.

الخطوة 3: قم بنسخ أعمدة البيانات وأنواعها في بيان جدول إنشاء

لدي ورقة Excel التي أستخدمها لتنسيق إخراج sp_help إلى عبارة "إنشاء جدول". لا تحتاج إلى أي شيء يتوهم ، فقط قم بنسخه ولصقه في محرر SQL. استخدم أسماء الأعمدة والأحجام والأنواع لإنشاء "إنشاء جدول #x [...]" أو "statementx table [...]" الذي يمكنك استخدامه لإدراج نتائج الإجراء المخزن.

الخطوة 4: أدخل في الجدول المنشأ حديثًا

سيكون لديك الآن طلب بحث مثل الحلول الأخرى الموضحة في سلسلة المحادثات هذه.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

يمكن استخدام هذه التقنية أيضًا لتحويل جدول مؤقت ( #temp ) إلى متغير جدول ( @temp ). على الرغم من أن هذا قد يكون أكثر من مجرد كتابة عبارة create table بنفسك ، إلا أنه يمنع حدوث خطأ يدوي مثل الأخطاء المطبعية ونوع بيانات عدم التطابق في العمليات الكبيرة. قد يستغرق تصحيح أخطاء مطبعي وقتًا أطول من كتابة طلب البحث في المقام الأول.


إذا كنت محظوظًا بما يكفي للحصول على SQL 2012 أو أعلى ، فيمكنك استخدام dm_exec_describe_first_result_set_for_object

لقد قمت للتو بتحرير مزود قدمتها gotqn. شكرا gotqn.

يؤدي ذلك إلى إنشاء جدول مؤقت عالمي يحمل نفس اسم اسم الإجراء. يمكن استخدام الجدول المؤقت لاحقًا كما هو مطلوب. لا تنس أن تسقطها قبل إعادة التنفيذ.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end

عندما يقوم الإجراء المخزن بإرجاع الكثير من الأعمدة ولا تريد يدوياً "إنشاء" جدول مؤقت للاحتفاظ بالنتيجة ، لقد وجدت أن أسهل طريقة هي الانتقال إلى الإجراء المخزن وإضافة جملة "إلى" على آخر اختيار بيان وإضافة 1 = 0 إلى حيث جملة.

قم بتشغيل الإجراء المخزن مرة واحدة والعودة وإزالة رمز SQL الذي أضفته للتو. الآن ، سيكون لديك جدول فارغ يطابق نتيجة الإجراء المخزن. يمكنك إما "الجدول النصي كما خلق" لجدول مؤقت أو ببساطة إدراج مباشرة في هذا الجدول.


لإدراج مجموعة السجلات الأولى من الإجراء المخزن في جدول مؤقت تحتاج إلى معرفة ما يلي:

  1. يمكن إدراج مجموعة الصف الأول فقط من الإجراء المخزن في جدول مؤقت
  2. يجب أن لا تنفيذ الإجراء المخزن عبارة T-SQL الحيوية ( sp_executesql )
  3. تحتاج إلى تحديد بنية الجدول المؤقت أولاً

قد يبدو أعلاه كقيد ، ولكن IMHO يكون منطقيًا تمامًا - إذا كنت تستخدم sp_executesql يمكنك إرجاع عمودين مرة واحدة وعشر مرة ، وإذا كان لديك مجموعات نتائج متعددة ، فلا يمكنك إدراجها في عدة جداول أيضًا - يمكنك إدراج الحد الأقصى في جهازي في عبارة T-SQL (باستخدام جملة OUTPUT و بدون مشغلات).

لذلك ، تكون المشكلة بشكل أساسي كيفية تعريف بنية الجدول المؤقتة قبل تنفيذ العبارة EXEC ... INTO ...

يعمل الأول مع OBJECT_ID بينما يعمل الثاني والثالث مع استعلامات Ad-hoc أيضاً. أفضل استخدام DMV بدلاً من sp كما يمكنك استخدام CROSS APPLY ثم إنشاء تعريفات الجدول المؤقت لإجراءات متعددة في نفس الوقت.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

أيضًا ، انتبه إلى حقل system_type_name لأنه يمكن أن يكون مفيدًا للغاية. يخزن العمود تعريف كامل. فمثلا:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

ويمكنك استخدامه مباشرة في معظم الحالات لإنشاء تعريف الجدول.

لذا ، أعتقد أنه في معظم الحالات (إذا كان الإجراء المخزن يتطابق مع معايير معينة) ، يمكنك بسهولة إنشاء عبارات ديناميكية لحل مثل هذه المشكلات (إنشاء الجدول المؤقت ، وإدراج نتيجة الإجراء المخزن فيه ، والقيام بما تحتاجه مع البيانات) .

لاحظ أن الكائنات أعلاه تفشل في تعريف بيانات مجموعة النتائج الأولى في بعض cases مثل عند تنفيذ عبارات T-SQL الديناميكية أو استخدام الجداول المؤقتة في الإجراء المخزن.


لقد وجدت Passing Arrays / DataTables في إجراءات مخزنة قد تعطيك فكرة أخرى عن كيفية حل مشكلتك.

يقترح الارتباط استخدام معلمة نوع صورة لتمريرها إلى الإجراء المخزن. ثم في الإجراء المخزن ، يتم تحويل الصورة إلى متغير جدول يحتوي على البيانات الأصلية.

ربما هناك طريقة يمكن استخدامها مع جدول مؤقت.


هذه إجابة على نسخة معدلة قليلاً من سؤالك. إذا كان بإمكانك التخلي عن استخدام إجراء مخزن لدالة معرفة من قبل المستخدم ، فيمكنك استخدام دالة معرّفة من قبل المستخدم مضمنة الجدول. هذا بشكل أساسي إجراء مخزن (ستتخذ معلمات) إرجاع جدول كمجموعة نتائج؛ وبالتالي سوف تضع بشكل جيد مع بيان INTO.

هنا مقالة سريعة جيدة عليها ووظائف أخرى محددة من قبل المستخدم. إذا كان لا يزال لديك حاجة إلى محرك أقراص لإجراء مخزن ، يمكنك لف الدالة المضمنة من قبل المستخدم المضمنة في الجدول باستخدام إجراء مخزن. الإجراء المخزن فقط يمرر المعلمات عندما يقوم باستدعاء select * من دالة معرفة من قبل المستخدم مضمنة جدول الجدول.

على سبيل المثال ، سيكون لديك وظيفة معرّفة من قِبل المستخدم ومحددة القيمة للجدول للحصول على قائمة عملاء لمنطقة معينة:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

يمكنك بعد ذلك استدعاء هذه الوظيفة للحصول على نتائجك على النحو التالي:

SELECT * FROM CustomersbyRegion(1)

أو للقيام SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

إذا كنت لا تزال بحاجة إلى إجراء مخزن ، فقم بلف الوظيفة على هذا النحو:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

أعتقد أن هذه هي طريقة "الاختراق أقل" للحصول على النتائج المرجوة. ويستخدم الميزات الموجودة كما كان يقصد استخدامها دون مضاعفات إضافية. من خلال تداخل وظيفة معرّفة من قبل المستخدم مضمّنة في الجدول في الإجراء المخزن ، يمكنك الوصول إلى الوظيفة بطريقتين. زائد! لديك نقطة صيانة واحدة فقط لرمز SQL الفعلي.

تم اقتراح استخدام OPENROWSET ، ولكن هذا ليس ما تم إعداده لاستخدام الدالة OPENROWSET لـ (من كتب عبر الإنترنت):

يتضمن كافة معلومات الاتصال المطلوبة للوصول إلى البيانات البعيدة من مصدر بيانات OLE DB. تعتبر هذه الطريقة بديلاً للوصول إلى الجداول في ملقم مرتبط وهي عبارة عن طريقة مؤقتة مخصصة للاتصال والوصول إلى البيانات البعيدة باستخدام OLE DB. لمزيد من المراجع المتكررة إلى مصادر بيانات OLE DB ، استخدم الخوادم المرتبطة بدلاً من ذلك.

باستخدام OPENROWSET سيتم إنجاز المهمة ، ولكنه سيحمل بعض الحمل الإضافي لفتح الاتصالات المحلية وتنظيم البيانات. كما أنه قد لا يكون خيارًا في جميع الحالات حيث إنه يتطلب تصريح استعلام مخصصًا والذي يشكل خطرًا أمنيًا وبالتالي قد لا يكون مرغوبًا. أيضاً ، سيحول النهج OPENROWSET دون استخدام الإجراءات المخزنة إرجاع أكثر من مجموعة نتائج واحدة. يمكن تحقيق التفاف العديد من وظائف معرّفة من قِبل المستخدم - قيمة الجدول المضمنة في إجراء مخزن واحد هذا.


هل يقوم الإجراء المخزن باسترداد البيانات فقط أو تعديلها أيضًا؟ إذا تم استخدامه فقط للاسترجاع ، يمكنك تحويل الإجراء المخزن إلى دالة واستخدام تعبيرات جدول شائعة (CTE) دون الحاجة إلى إعلانه ، كما يلي:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

ومع ذلك ، ينبغي استخدام أي شيء يحتاج إلى استرجاعه من CTE في بيان واحد فقط. لا يمكنك القيام with temp as ... ومحاولة استخدامه بعد سطرين من SQL. يمكن أن يكون لديك CTEs متعددة في عبارة واحدة لاستعلامات أكثر تعقيداً.

فمثلا،

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

يمكنك استخدام OPENROWSET لهذا. الق نظرة. لقد قمت أيضًا بتضمين رمز sp_configure لتمكين Ad Hoc Distributed Queries ، في حالة عدم تمكينه بالفعل.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

الشفرة

CREATE TABLE #T1
(
    col1 INT NOT NULL,
    col2 NCHAR(50) NOT NULL,
    col3 TEXT NOT NULL,
    col4 DATETIME NULL,
    col5 NCHAR(50) NULL,
    col6 CHAR(2) NULL,
    col6 NCHAR(100) NULL,
    col7 INT NULL,
    col8 NCHAR(50) NULL,
    col9 DATETIME NULL,
    col10 DATETIME NULL
)

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)


SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col6,
    col7,
    col8,
    col9,
    col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

آمل أن يساعد هذا. يرجى التأهل حسب الاقتضاء.


I met the same problem and here is what I did for this from Paul's suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.


SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')




stored-procedures