Is it possible to SELECT on an SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Is it possible to SELECT on an SP?

Author

Topic

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-10 : 02:18:37

I am executing an SP that have the output shows records. I want this output to be inserted in a new table. Is this possible?

The idea is like the code below (but this doesn't work). Can this be done?

select *
into #SP_output
from (exec sp_sample)

I need to insert the result in a new table because I want to add other information’s by joining the result to other tables. This is for testing purposes of the SP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 02:36:21

yup. you can.but you need to use OPENROWSET for it

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 03:15:11

quote:
Originally posted by chrianth

Can this be done?

select *
into #SP_output
from (exec sp_sample)

Yes. But you have the wrong syntax.
Try this

INSERT	#SP_output
EXEC	sp_sample

E 12°55'05.63"
N 56°04'39.26"

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-10 : 05:03:28

Thanks for the info.

I tried the approach discussed on the link which is...

execute :
Create table ##Table_SP_Output (Field1 as Varchar, Field2 as Varchar)

then execute :
Insert into ##Table_SP_Output Exec sp_sample

but returned with this error message:
Msg 8152, Level 16, State 14, Procedure sp_sample, Line 15
String or binary data would be truncated.

Can you point me to the possible problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-10 : 05:08:27

you created a table with two one-character columns.
set a size for the columns, please.

Create table ##Table_SP_Output (Field1 as Varchar(200), Field2 as Varchar(200))

E 12°55'05.63"
N 56°04'39.26"

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-10 : 05:44:02

Thanks! that solves the problem...
i thought it wasn't that important to declare length for the field because when you do
select *
into temp_table
from table
resulted to temp_table with automatic setting of field length.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-10 : 07:43:26

SELECT INTO will create a table with the same datatypes as the rows returned. INSERT INTO puts data into an already created table so you need to match the selection datatypes.

INSERT INTO is a lot faster than SELECT INTO as it is a set operation.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 09:20:01

quote:
Originally posted by chrianth

Thanks! that solves the problem...
i thought it wasn't that important to declare length for the field because when you do
select *
into temp_table
from table
resulted to temp_table with automatic setting of field length.

thats because it takes definition of new table columns to same as that of source column definition.

Also see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources