Stored Procedure output to temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure output to temp table

Author

Topic

durgamavu
Starting Member

4 Posts

Posted - 2011-03-22 : 12:26:43

hi ..the scenario is like :a stored procedure takes input parameter and returns a single result..and now i want to store this result into temp: table,and one more thing is dont want to insert row into temp table if it is redundant..,when am trying to create a temp table using #temp..its not allowing to do so..dnt knw the reason..pls help as am newbie to sql server dev: thnks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 13:01:33

Please post the structure of the table you're trying to insert into (CREATE TABLE statement).

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:35:34

Are you doing

EXEC <sprocname> INTO #newTemp

???

Won't work

You need to create the table first, then do

INSERT INTO #temp (Col1, Col2, ColEtc)
EXEC <sprocname>

And does is redundant mean? If the sproc is returning dup rows, fix the sproc

Or clone it and change it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

durgamavu
Starting Member

4 Posts

Posted - 2011-03-23 : 01:42:28

quote:
Originally posted by robvolk

Please post the structure of the table you're trying to insert into (CREATE TABLE statement).

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[GetProductInfo]

@prodid int

AS

SELECT ProductName
FROM Products
WHERE (ProductID = @prodid)

insert into #temp(prodname)
exec [dbo].[GetProductInfo]

THIS IS THE TABLE AM TRYING TO CREATE..THE SP IS RETURNING THE RESULT BUE NOT IN A #TEMP TABLE,#temp table is not getting created

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-23 : 01:46:44

DID you even read what I posted?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

durgamavu
Starting Member

4 Posts

Posted - 2011-03-23 : 01:48:03

quote:
Originally posted by X002548

Are you doing

EXEC <sprocname> INTO #newTemp

???

Won't work

You need to create the table first, then do

INSERT INTO #temp (Col1, Col2, ColEtc)
EXEC <sprocname>

And does is redundant mean? If the sproc is returning dup rows, fix the sproc

Or clone it and change it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

hi..had done the same way..pls look at the table structure...i want to fix the sp itself to stop returning redundant values..that is it can check in the temp table and allow only those values that are not there..

durgamavu
Starting Member

4 Posts

Posted - 2011-03-23 : 02:33:33

quote:
Originally posted by X002548

DID you even read what I posted?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

i create the #temp table first and then written the above sp and whn i execute the sp the results are as

(1 row(s) affected)
Msg 208, Level 16, State 0, Procedure GetProductInfo, Line 13
Invalid object name '#temp'.

(1 row(s) affected)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-23 : 04:16:02

can you show us the code that create the temp table and also how you execute the Stored Procedure

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources