SQL Stored Procedure Using tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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)

here is my sql statement:

SQL

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

Posted 10-May-11 5:08am

Geofferz

Updated 10-May-11 7:09am

Umair Feroze

v3

Add a Solution

Comments

OriginalGriff 10-May-11 11:25am

I was going to use the "Improve question" widget to format that chunk of SQL so it can be read. Then I saw you have no formatting in your original.

Do us *all* a favour: Reformat that with indentation so it is easier to work out what is going on. Then edit your question, and put it in a code block (highlight it, and click on "Code Block".

It makes it easy for us to read, improving your chances of an answer.
It makes it easier for you to read, making maintenance simpler.

You know it makes sense!

wizardzz 10-May-11 12:30pm

I agree with Griff. Where are you having the actual issue, too?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Umair Feroze · Accepted Answer · 2011-05-10T07:08:00

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

mhwasim · Accepted Answer · 2011-05-10T07:51:00

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 Stored Procedure Using tables

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0