September 30, 2011 at 12:55 pm
Today I came up with strage problem.
I have a query like below:
exec @return_value=my_proc @parameter
I have to run above query for the list of all data from one table and get all return value in single select statement( I am not allowed loop or create temp table).
In other word if I run query like
select * from my_proc @parameter(all data from table needs to passed as parameter)
should return all the value from the table which is returnable after processing in the proc.
I know it is confussing but this what I have been going through. Is it doable?
Appreciate your help and time.
Thanks
September 30, 2011 at 12:59 pm
You can pass the parameter as a Table Type (new for 2k8) of a table variable.
Another option is an XML string, or even a comma delimited string, which is then parsed internally.
If you give us more information, in particular the query, the parameter(s) you're expecting, and what exactly you're expecting to be returned, we could probably help more, but as it is it's too vague.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2011 at 12:59 pm
You're right. That is totally unclear. Can you explain what you mean a bit more clearly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 1:02 pm
yes, if you have myproc return a table. so much depends on what "my_proc" actually does.
CREATE PROCEDURE MyProc @param int
AS
BEGIN
SET NOCOUNT ON
SELECT ColumnList From SomeTable where Id >= param
END--PROC
GO
create table #Capture_Results(ColumnList)
INSERT INTO Capture_Results
EXEC MyProc 55
Lowell
September 30, 2011 at 1:08 pm
Here is what I want.
exec @return_value1=my_proc @para1
exec @return_value2=my_proc @para2
exec @return_value3=my_proc @para3
I have a situation where I can run only one select statement which will have all return value from the above statement.
For EG:
select return_value from my_proc Should produce the following results.
return_value
return_value1
return_value2
return_value3
Hope this helps to clarify my questions.
September 30, 2011 at 1:14 pm
Pretty sparse on the details...so you can't create a temp table (that is a strange mandate), no looping (good idea to avoid looping whenever possible).
So this is like calling the same method in a programming language 3 times, each with a different parameter. Now, without storing the results of each method call I want to return the results of all 3.
You are going to have to store the results somewhere until you have all of them. I just don't see any way around it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 1:19 pm
I must be honest with you, I have no idea to help you. I can't see from here what you see there, and what you have provided is so vague that anything we give you will simply be shots in the dark.
We don't know what the procedure does, nor do we know what information is being passed to the procedure from the data source.
September 30, 2011 at 1:21 pm
Thank you guys. I don't think it doable. But I was giving a try if there was way arround.
Just to clarify the proc will take one parameter, process that and based on the criteria return one single value.
Thanks again for your time and help
September 30, 2011 at 1:26 pm
With a temp table this is simple.
create table #MyVals(RowID int identity, returnVal int)
insert #MyVals exec my_proc @para1
insert #MyVals exec my_proc @para2
insert #MyVals exec my_proc @para3
select * from #MyVals order by RowID
Why the mandate of no temp table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 1:28 pm
Thank you Sean,
We are not running the query SSMS we put query itself in some application which will take select statment only.
September 30, 2011 at 1:33 pm
Walton (9/30/2011)
Thank you Sean,We are not running the query SSMS we put query itself in some application which will take select statment only.
Okay. Please ask yourself, If I knew nothing about the question I am asking, would I be able to answer the question I am asking of others?
There are many people out here who would love to help you, but you haven't given us anything to help us understand the problem you are trying to resolve. How can we help you if we don't understand the problem or situation.
September 30, 2011 at 1:38 pm
Walton (9/30/2011)
Thank you Sean,We are not running the query SSMS we put query itself in some application which will take select statment only.
Don't be so sure about that. 😉 Try plugging this into your single select statement.
create table #MyVals(RowID int identity, returnVal int);insert #MyVals exec my_proc @para1;insert #MyVals exec my_proc @para2; insert #MyVals exec my_proc @para3; select * from #MyVals order by RowID;
That entire code block is your SINGLE statement. It works in .NET when using a SqlDataAdapter just fine. My guess is whatever app you are using can do something similair.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply