使用表的SQL存储过程 [英] SQL Stored Procedure Using tables

问题描述

你好，

我需要一些有关如何在sql中创建存储过程以创建临时表的帮助.我希望存储过程使用下面的代码创建一个临时表. (它不一定是下面的代码，我只是想获得如何创建存储过程以将来自不同sql数据库的数据插入到一个我可以读取数据的漂亮表中的基础)

参数:

Hi there,

I need some help in how to create a stored procedure in sql to create a temp table. I want the stored procedure to create a temp table using the code below. (it doesnt have to be code below i just want to get the basis how to create a stored procedure to insert data from different sql database into a nice table where i can be able to read the data)

parameters:

@Request_ID int,
	@Request_Name nvarchar(64),
	@Request_Description nvarchar(255),
	@Request_Date_Start datetime,
	@Data_Value_Text nvarchar(4000)

这是我的sql语句:

here is my sql statement:

SELECT
        dbo.WFPROCESS_INST.ID_PROCESS_INST AS REQUEST_ID
        , dbo.WFPROCESS_INST.NAME AS REQUEST_NAME
        , dbo.WFPROCESS_INST.DESCRIPTION AS REQUEST_DESCRIPTION
        , dbo.WFPROCESS_INST.DATE_START AS REQUEST_DATE_START
        , dbo.WFDATASET_VALUE.VALUE_TEXT
        , dbo.WFRELDATA.DESCRIPTION
FROM dbo.WFPROCESS_INST
INNER JOIN dbo.WFPROCESS ON dbo.WFPROCESS_INST.ID_PROCESS = dbo.WFPROCESS.ID_PROCESS
INNER JOIN dbo.WFPROCESS_INST_RELDATA ON dbo.WFPROCESS_INST.ID_PROCESS_INST = dbo.WFPROCESS_INST_RELDATA.ID_PROCESS_INST
INNER JOIN dbo.WFRELDATA ON dbo.WFPROCESS_INST_RELDATA.ID_RELDATA = dbo.WFRELDATA.ID_RELDATA
INNER JOIN dbo.WFDATASET ON dbo.WFPROCESS_INST_RELDATA.ID_DATASET = dbo.WFDATASET.ID_DATASET
LEFT OUTER JOIN dbo.WFDATASET_VALUE ON dbo.WFDATASET.ID_DATASET = dbo.WFDATASET_VALUE.ID_DATASET
INNER JOIN dbo.USERS ON dbo.WFPROCESS_INST.ID_USER_REQUESTER = dbo.USERS.ID_USER
INNER JOIN dbo.DIRECTORY ON dbo.USERS.ID_DIRECTORY = dbo.DIRECTORY.ID_DIRECTORY
INNER JOIN dbo.WFPROCESS_RELDATA ON dbo.WFPROCESS.ID_PROCESS = dbo.WFPROCESS_RELDATA.ID_PROCESS AND dbo.WFRELDATA.ID_RELDATA = dbo.WFPROCESS_RELDATA.ID_RELDATA
WHERE
    (dbo.WFDATASET.ID_DATATYPE <> N'FILE') AND (dbo.WFPROCESS_INST.TEST <> N'Y')
    AND (dbo.WFPROCESS_INST.DESCRIPTION = N'Purchasing Requisition')

在此先感谢

Thanks in advance

推荐答案

下面是存储过程的代码.这将创建一个临时表，将数据放入其中，选择后删除该临时表.希望对您有帮助.

Below is the code of stored procedure. This will create a temp table, put data in it and after selection drop the temp table. Hope this will help you.

CREATE PROCEDURE PutData
(
	@Request_ID int,
	@Request_Name nvarchar(64),
	@Request_Description nvarchar(255),
	@Request_Date_Start datetime,
	@Data_Value_Text nvarchar(4000)
)
AS
	CREATE TABLE #Test
	(
		RequestID INT,
		RequestName VARCHAR(500),
		RequestDescription VARCHAR(5000),
		RequestDateStart DATETIME,
		ValueText TEXT,
		Description VARCHAR(5000)
	)
	INSERT INTO #Test
	SELECT
			dbo.WFPROCESS_INST.ID_PROCESS_INST AS REQUEST_ID
			, dbo.WFPROCESS_INST.NAME AS REQUEST_NAME
			, dbo.WFPROCESS_INST.DESCRIPTION AS REQUEST_DESCRIPTION
			, dbo.WFPROCESS_INST.DATE_START AS REQUEST_DATE_START
			, dbo.WFDATASET_VALUE.VALUE_TEXT
			, dbo.WFRELDATA.DESCRIPTION
	FROM dbo.WFPROCESS_INST
	INNER JOIN dbo.WFPROCESS ON dbo.WFPROCESS_INST.ID_PROCESS = dbo.WFPROCESS.ID_PROCESS
	INNER JOIN dbo.WFPROCESS_INST_RELDATA ON dbo.WFPROCESS_INST.ID_PROCESS_INST = dbo.WFPROCESS_INST_RELDATA.ID_PROCESS_INST
	INNER JOIN dbo.WFRELDATA ON dbo.WFPROCESS_INST_RELDATA.ID_RELDATA = dbo.WFRELDATA.ID_RELDATA
	INNER JOIN dbo.WFDATASET ON dbo.WFPROCESS_INST_RELDATA.ID_DATASET = dbo.WFDATASET.ID_DATASET
	LEFT OUTER JOIN dbo.WFDATASET_VALUE ON dbo.WFDATASET.ID_DATASET = dbo.WFDATASET_VALUE.ID_DATASET 
	INNER JOIN dbo.USERS ON dbo.WFPROCESS_INST.ID_USER_REQUESTER = dbo.USERS.ID_USER
	INNER JOIN dbo.DIRECTORY ON dbo.USERS.ID_DIRECTORY = dbo.DIRECTORY.ID_DIRECTORY
	INNER JOIN dbo.WFPROCESS_RELDATA ON dbo.WFPROCESS.ID_PROCESS = dbo.WFPROCESS_RELDATA.ID_PROCESS AND dbo.WFRELDATA.ID_RELDATA = dbo.WFPROCESS_RELDATA.ID_RELDATA
	WHERE 
		(dbo.WFDATASET.ID_DATATYPE <> N''FILE'') AND (dbo.WFPROCESS_INST.TEST <> N''Y'')
		AND (dbo.WFPROCESS_INST.DESCRIPTION = N''Purchasing Requisition'')
	SELECT * FROM #Test
	DROP TABLE #Test
GO

兄弟Umair Feroze的解决方案是正确的.

The solution from brother Umair Feroze is correct..I am just putting another way to get the same results using his solution...

CREATE PROCEDURE PutData
(
	@Request_ID int,
	@Request_Name nvarchar(64),
	@Request_Description nvarchar(255),
	@Request_Date_Start datetime,
	@Data_Value_Text nvarchar(4000)
)
AS

	SELECT
			dbo.WFPROCESS_INST.ID_PROCESS_INST AS REQUEST_ID
			, dbo.WFPROCESS_INST.NAME AS REQUEST_NAME
			, dbo.WFPROCESS_INST.DESCRIPTION AS REQUEST_DESCRIPTION
			, dbo.WFPROCESS_INST.DATE_START AS REQUEST_DATE_START
			, dbo.WFDATASET_VALUE.VALUE_TEXT
			, dbo.WFRELDATA.DESCRIPTION
	INTO #Test
	FROM dbo.WFPROCESS_INST
	INNER JOIN dbo.WFPROCESS ON dbo.WFPROCESS_INST.ID_PROCESS = dbo.WFPROCESS.ID_PROCESS
	INNER JOIN dbo.WFPROCESS_INST_RELDATA ON dbo.WFPROCESS_INST.ID_PROCESS_INST = dbo.WFPROCESS_INST_RELDATA.ID_PROCESS_INST
	INNER JOIN dbo.WFRELDATA ON dbo.WFPROCESS_INST_RELDATA.ID_RELDATA = dbo.WFRELDATA.ID_RELDATA
	INNER JOIN dbo.WFDATASET ON dbo.WFPROCESS_INST_RELDATA.ID_DATASET = dbo.WFDATASET.ID_DATASET
	LEFT OUTER JOIN dbo.WFDATASET_VALUE ON dbo.WFDATASET.ID_DATASET = dbo.WFDATASET_VALUE.ID_DATASET 
	INNER JOIN dbo.USERS ON dbo.WFPROCESS_INST.ID_USER_REQUESTER = dbo.USERS.ID_USER
	INNER JOIN dbo.DIRECTORY ON dbo.USERS.ID_DIRECTORY = dbo.DIRECTORY.ID_DIRECTORY
	INNER JOIN dbo.WFPROCESS_RELDATA ON dbo.WFPROCESS.ID_PROCESS = dbo.WFPROCESS_RELDATA.ID_PROCESS AND dbo.WFRELDATA.ID_RELDATA = dbo.WFPROCESS_RELDATA.ID_RELDATA
	WHERE 
		(dbo.WFDATASET.ID_DATATYPE <> N''FILE'') AND (dbo.WFPROCESS_INST.TEST <> N''Y'')
		AND (dbo.WFPROCESS_INST.DESCRIPTION = N''Purchasing Requisition'')

	SELECT * FROM #Test
	DROP TABLE #Test
GO

这篇关于使用表的SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！