Inserting Value into table from stored procedure

Question

Inserting Value into table from stored procedure

J.O.

Right there with Babe

Points: 756
More actions
August 27, 2008 at 3:29 pm

#70741

I have a stored procedure that calls other stored procedures from within and I would like to know if its possible to insert the value returned from those stored procedures into table. For example...
INSERT INTO table (Value)
EXEC sp_MyStoredProcedure @Parameter
sp_MyStoredProcedure is nothing more than a simple select statement that returns one value. I need that value to go inside a table. Is this possible with SQL 2000? Any input is appreciated.
thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Hopefully you would have a test database, even something as simple as the free express version.... then you could try your T-SQL code and answer your own question.

Read these from BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/717bcc19-9f86-4dcf-82cd-bc65a18fac6a.htm

and

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a2336a30-1793-4a29-bffc-500e24204d70.htm

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

J.O. Right there with Babe Points: 756 More actions · Answer 2

Bitbucket,

Those are simple inserts I was doing something a little bit different here...

INSERT INTO #Answers(Answer)

EXEC @Procedure @Parameter

This does work but it only inserts one column. I would like to insert more than one column at once. The results from the procedure is only one column but I need to insert additional fields as well. Now normally I know something like this works

INSERT INTO #Answers(id,answer,question)

values

(@ID,@Answer,@Question)

But something like that doesnt work when using EXEC for one of the values. I did try something like this...

INSERT INTO #Answers(Answer)

EXEC @Procedure @Parameter

SET @IDENTITY = (SELECT SCOPE_IDENTITY())

UPDATE #Answers

SET ID = @ID, Answer = @Answer, Question=@Question

WHERE ID = @IDENTITY

That only works if there is only one record being returned from my procedure. if its more than one, of course it only updates the very last record inserted. Hopefully someone can suggest an alternative to this. Thanks in advance.

J.O. Right there with Babe Points: 756 More actions · Answer 3

Ok I almost got this working with the following code....

OPEN GetProcedure_Cursor

FETCH NEXT FROM GetProcedure_Cursor

INTO @QuestionProcedure, @QuestionID

WHILE @@FETCH_STATUS = 0

BEGIN

declare @MyString varchar(8000)

set @MyString =

N'INSERT INTO #Answers2(QuestionID,Adusername,Answer)

SELECT '''+@QuestionID+''',''jeordonez'', *

FROM OPENROWSET(''SQLOLEDB'', ''server=(local);trusted_connection=yes'', ''set fmtonly off EXEC ''' + REPLACE(@QuestionProcedure, '''', '''''') + ''' ''jeordonez'''')'

print @mystring

FETCH NEXT FROM GetProcedure_Cursor

INTO @QuestionProcedure, @QuestionID

END

CLOSE GetProcedure_Cursor

DEALLOCATE GetProcedure_Cursor

It prints out this...

INSERT INTO #Answers2(QuestionID,Adusername,Answer)

SELECT '18','jeordonez', *

FROM OPENROWSET('SQLOLEDB', 'server=(local);trusted_connection=yes', 'set fmtonly off EXEC 'usp_GetEmergencyPhoneContact' 'jeordonez'')

It shouldnt have quotes around usp_GetEmergencyPhoneContact because this generates an error of invalid syntax...can someone please help with this??