We help IT Professionals succeed at work.

in sqlserver how can i pass a select against a result of a stored proc ?

Erwin Pombett
Erwin Pombett asked
on
Hello,
i have a stored proc that gives me as a result 10 columns, 100 rows.

How can  i pass a select on this result ?

if possible without creating a table.


thank in advance

Toshi
Comment
Watch Question

ste5anSenior Developer

Commented:
hmm, you can use a table variable and the INSERT INTO @table_var EXEC sproc syntax.

Author

Commented:
hello ste5an,
thank you for answer,

i'd like to avoid using an intermediary  table.

i'm looking to use Openquery().

Toshi
ste5anSenior Developer

Commented:
You can do this, but keep in mind, that this will execute your procedure in a different scope.

Author

Commented:
ste5an,

ok, if i use your "insert into @table_var ...."

i need to create the table before ?  
can i copy the table columns in an easy way. ?

can you help me further on your possibility ,

thakn you in advance.

Toshi

Author

Commented:
declare @tatavar Table(
      PersonID int,
      FriendlyName varchar,
      Name varchar,
      Login varchar
);
INSERT INTO @tatavar EXEC my_stored_proc  @OrgRoleID=1003

select * from @tatavar


i receive the error :
Column name or number of supplied values does not match table definition.

how can i have the columns - returned by the stored proc - in the created table

toshi
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What really my_stored_proc returns?

Author

Commented:
my_stored_proc returns columns that were retrieve from diffrents tables.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Please post a sample (anonymize all sensitive data before posting).

Author

Commented:
i've been told to create a temp table as follow,
the problèm is that i need to create the table with the columns.

1. how could i create the table with the columns that are returned by the stored_proc ?
sort of cloning of my table.

here is what i' trying to do (i've stopped trying with the openquery() function:

declare @tatavar Table(                        # i need to have the table with the columns like the stored proc returns. I
      PersonID int,
      FriendlyName varchar,
      Name varchar,
      Login varchar
);
# here i'd fill the table with the response of the storeproc
INSERT INTO @tatavar EXEC my_stored_proc  @OrgRoleID=1003
# i could then select columns and add a where clause
select * from @tatavar



thank you in advance for your help.

toshi
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If you don't post a sample will be hard to help you. Only thing I can do is guessing.

For example, you said: "i have a stored proc that gives me as a result 10 columns, 100 rows."
And then you post a table with only 4 columns:
declare @tatavar Table(                        # i need to have the table with the columns like the stored proc returns. I
       PersonID int,
       FriendlyName varchar,
       Name varchar,
       Login varchar
 );

And that's explain the error: Column name or number of supplied values does not match table definition.

But I can't help more without knowing the column names and type returned by the stored procedure.

Author

Commented:
hello,

absolutely,

my question is :
how to get the columns that are responded by the storedproc ?
i dont want to write them...there's many.

is there a way to create the table ?

thank you in advance

Author

Commented:
the store proc return about a hundred columns......
ste5anSenior Developer

Commented:
Use the OPENQUERY() approach to generate a (temporary) table. Then you can extract the column definitions from this table.

Author

Commented:
hello ste5en..

on that possibility i have a problem because some columns have xml content ;/

Author

Commented:
hello ste5ne again,

here's the error on that possibility :
Xml data type is not supported in distributed queries. Remote object 'OPENQUERY' has xml column(s).
ste5anSenior Developer

Commented:
Then dig into the stored procedures code... and modifiy it to create the table.

Author

Commented:
ste5en,

how can i do that ?

Author

Commented:
how to create the table from :

BEGIN

      SET NOCOUNT ON;

      SELECT p.*
      FROM Person p(NOLOCK)
      JOIN func_PersonID_BLABLA(@OROZID,0) l
            ON p.personID = l.PersonID

END

Author

Commented:
ste5en

is it possible to create a temporary table ?
something that would be created on launching the store proc but that wont be created in the db ?

thank you

toshi
Senior Developer
Commented:
There must be a final SELECT in your sproc. Just add an INTO temporarTable after column list. When you've created this table once, remove the INTO clause.

Author

Commented:
Hello ste5en,

do you mean  as follow :


BEGIN

      SET NOCOUNT ON;

      SELECT p.*  INTO @myTempTable
      FROM Person p(NOLOCK)
      JOIN func_PersonID_BLABLA(@OROZID,0) l
            ON p.personID = l.PersonID

END
ste5anSenior Developer

Commented:
Yup, almost: INTO #tempTable or INTO dbo.TempTable to avoid clean-up of the temp table, if going out-of scope.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
INTO #tempTable won't work since the temporary table will be destroyed when exit from SP. Need to be a global temporary table and that should be in this format ##tempTable. Problem may occur if the SP will be called often inside the same connection.

Author

Commented:
...i can not find my ##TMP table ?

where did it create it ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
It didn't. It was only a warning from me if you want to work with temporary tables inside stored procedures.

Author

Commented:
Vitor,

correct me if i'm wrong but :

you said that with "##" as prefix this would create the table as a global table.
you said this problem will occur only with one "#".

so with "##" i should be supposed to find the table as a global temporary table.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes. If you create a #tempTable inside a stored procedure it will be destroyed when you exit from that stored procedure. Using ##tempTable the temporary will be available until you explicitly destroy it (DROP TABLE ##tempTable) or when you disconnect from the database.
Be careful when using ##tempTable since it can grow if you only make inserts and never deletes.

Author

Commented:
hello,

as i couldn't find my temptable ##TMP table
i created a new one - not within the SP- that got display in the : Databases -> System Databases -> tempdb -> temporary Tables.

If I can create a temporary table and i can check where this is displayed, I can now confirm that adding "INTO ##myTempTable" did not add any temporary table.

BEGIN

      SET NOCOUNT ON;

      SELECT p.*  INTO ##myTempTable
      FROM Person p(NOLOCK)
      JOIN func_PersonID_BLABLA(@OROZID,0) l
            ON p.personID = l.PersonID

END


question :
is it because there's a call to a function after the select. Indeed the inner call to the function select a value inside.
how can i do to create a table with the SP  result ?

thank you in advance.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
as i couldn't find my temptable ##TMP table
I think you misunderstood what have been told here. The temporary table was an advice for you to create inside the Stored Procedure. Isn't something that already exists.

i created a new one - not within the SP- that got display in the : Databases -> System Databases -> tempdb -> temporary Tables.
Please drop that table. It wasn't that what you should do.

The temporary table solution wasn't my advice. I just made a point to use global temporary table instead of local one and I already explained why.
Also, I already asked you to post a sample of what the Stored Procedure returns but you keep going in another way.

Author

Commented:
hello Victor, thank you for your patience.

I didn't think that the ##TMP table already exists.  But it should have been created after i added "INTO ##myTempTable" to my SP.

as the SP did not create the ##TMPtable  i 'm asking if it's because there's a function inside the SP, and because that function is also using "select".  (I was advised to add the "INTO ##myTempTable" after the select)

i'm sorry, i'm not a native english speaker, i'm trying to do my best.

hope you understand,

thank you in advance for further help
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I didn't think that the ##TMP table already exists.  But it should have been created after i added "INTO ##myTempTable" to my SP.
Sorry. Then it was me that misunderstood your comments.
But you can't see the ##myTempTable in SSMS because it's another connection. That temp table it's only visible within the connection that created it so others won't be available to see it. The only way to make the test is to run a SELECT * FROM ##myTempTable to verify if any row is returned.

Oh, I'm also not a native English speaker. ;)

Cheers