output from stored procedure containing min function.
-
I have created a follwoing stored procedure and want to have output in tow variables declared at the beginning of stored procedure. But when I try to assign them at final select statment stored procedure throws error:A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. Also how to receive these variables in another stored procedure.
If any one can please help me with this problem.
Thank you in advance.
Bharat.
==============stored rpocedure===========
CREATE PROC hcc_hier_100101
(
@mem_id nvarchar (21) OUTPUT,
@hcc101 nvarchar (7) OUTPUT
)
AS
CREATE Table #Temp_100101
(
member_id nvarchar(21),
hcc nvarchar(7)
)
INSERT INTO #Temp_100101
(
member_id,
hcc
)
(
SELECT DISTINCT @mem_id = member_id,
@hcc101 = MIN(CASE WHEN hcc IN (100,101)THEN hcc ELSE NULL
END)
FROM mem_hist_dist_hcc WHERE member_id IN ('000401916','44128060301')
GROUP BY member_id
)
-
The way you have it written, you are trying to insert into a temp table and variables at the same time. If you want to do both, you will need two selects. Since I see no merit in that, remove the INSERT portion of your query along with the creation of the temp table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thank you for your kind reply. I did what you suggested and wrote the query as follows:
======
CREATE PROCEDURE T_1519
AS
SELECT DISTINCT member_id,
MIN(CASE WHEN hcc IN (15,16,17,18,19)THEN hcc ELSE NULL
END)
FROM mem_hist_dist_hcc WHERE member_id IN ('000401916','44128060301')
GROUP BY member_id
=====
This gives me correct output as a table with 2 columns(memebr_id,hcc) and 2 rows. (as tested without create procedure statement)
My question is how to get output of this table into another stored procedure.I do not know proper syntext and construct.
Thank you once again for your answer.
-
In your "other" stored proc, you can execute this stored proc and put the result set in a temp table. eg:
create proc myotherproc as
create table #temp (member_id int, hcc int)
insert #temp exec t_1519
...
Good luck.
-
Hi Jeff,
Thank you so much. However when I do like
create table #temp (mem_id nvarchar, hcc1519 nvarchar)
insert #temp exec t_1519
select * FROM #temp
I get empty table back!!
Any reason?
Thank you in advance.
Bharat.
-
Actually, Martin gave you the previous answer... but thank you, anyway.
I've not had the time to "play" with it, but you are missing a column alias in your stored procedure which may also be causing part of the problem... (highlighed below)...
======
CREATE PROCEDURE T_1519
AS
SELECT DISTINCT member_id,
MIN(CASE WHEN hcc IN (15,16,17,18,19)THEN hcc ELSE NULL
END) AS HCC
FROM mem_hist_dist_hcc WHERE member_id IN ('000401916','44128060301')
GROUP BY member_id
=====
I believe the real problem is that the column names in the Temp Table should match the names of the columns in the result set of the stored proc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thank you for your reply. As I was having problems with temp table I created real tables in database and solved the query. But I would like to work with temp tables as otherwise database will be clobbered with tables. I will definately work with your suggestion to see if I can use temp tables.
Thank you and Thanks to Martin.
Bharat.
-
What ever you make with a permanent table, should also work with a temp table except FK constraints.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thank you.
Finally I got everything working.
Bharat.
-
Great... thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply