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

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
Erwin PombettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
ste5anSenior DeveloperCommented:
hmm, you can use a table variable and the INSERT INTO @table_var EXEC sproc syntax.
0
 
Erwin PombettAuthor Commented:
hello ste5an,
thank you for answer,

i'd like to avoid using an intermediary  table.

i'm looking to use Openquery().

Toshi
0
 
ste5anSenior DeveloperCommented:
You can do this, but keep in mind, that this will execute your procedure in a different scope.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Erwin PombettAuthor 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
0
 
Erwin PombettAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What really my_stored_proc returns?
0
 
Erwin PombettAuthor Commented:
my_stored_proc returns columns that were retrieve from diffrents tables.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post a sample (anonymize all sensitive data before posting).
0
 
Erwin PombettAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Erwin PombettAuthor 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
0
 
Erwin PombettAuthor Commented:
the store proc return about a hundred columns......
0
 
ste5anSenior DeveloperCommented:
Use the OPENQUERY() approach to generate a (temporary) table. Then you can extract the column definitions from this table.
0
 
Erwin PombettAuthor Commented:
hello ste5en..

on that possibility i have a problem because some columns have xml content ;/
0
 
Erwin PombettAuthor 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).
0
 
ste5anSenior DeveloperCommented:
Then dig into the stored procedures code... and modifiy it to create the table.
0
 
Erwin PombettAuthor Commented:
ste5en,

how can i do that ?
0
 
Erwin PombettAuthor 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
0
 
Erwin PombettAuthor 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
0
 
ste5anSenior DeveloperCommented:
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.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Erwin PombettAuthor 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
0
 
ste5anSenior DeveloperCommented:
Yup, almost: INTO #tempTable or INTO dbo.TempTable to avoid clean-up of the temp table, if going out-of scope.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Erwin PombettAuthor Commented:
...i can not find my ##TMP table ?

where did it create it ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It didn't. It was only a warning from me if you want to work with temporary tables inside stored procedures.
0
 
Erwin PombettAuthor 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Erwin PombettAuthor 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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Erwin PombettAuthor 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.