Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL - Query Assistance - Cursors

Posted on 2015-02-17
8
111 Views
Last Modified: 2015-02-17
Hello experts,

I have a database table that may hold up to 8 rows in it, and I need to cursor through them each and add their values to variables.  I need a clever way to use a counter to increment the variable position as I cycle through the records, as opposed to declaring a variable for each.  

Here is the code I have so far:


ALTER PROCEDURE Path_Cursor (

-- inputs
@enterprise_id			CHAR(5),
@practice_id			CHAR(4),
@person_id				varchar(36),
@enc_id					varchar(36),

-- outputs
@bottle_count_1_OUT		INT OUTPUT,
@organ_1_OUT			varchar(255) OUTPUT,
@location_1_OUT			varchar(255) OUTPUT,
@instrument_1_OUT		varchar(255) OUTPUT,
@description_1_OUT		varchar(255) OUTPUT,
@bottle_count_2_OUT		INT OUTPUT,
@organ_2_OUT			varchar(255) OUTPUT,
@location_2_OUT			varchar(255) OUTPUT,
@instrument_2_OUT		varchar(255) OUTPUT,
@description_2_OUT		varchar(255) OUTPUT,
@bottle_count_3_OUT		INT OUTPUT,
@organ_3_OUT			varchar(255) OUTPUT,
@location_3_OUT			varchar(255) OUTPUT,
@instrument_3_OUT		varchar(255) OUTPUT,
@description_3_OUT		varchar(255) OUTPUT,
@bottle_count_4_OUT		INT OUTPUT,
@organ_4_OUT			varchar(255) OUTPUT,
@location_4_OUT			varchar(255) OUTPUT,
@instrument_4_OUT		varchar(255) OUTPUT,
@description_4_OUT		varchar(255) OUTPUT,
@bottle_count_5_OUT		INT OUTPUT,
@organ_5_OUT			varchar(255) OUTPUT,
@location_5_OUT			varchar(255) OUTPUT,
@instrument_5_OUT		varchar(255) OUTPUT,
@description_5_OUT		varchar(255) OUTPUT,
@bottle_count_6_OUT		INT OUTPUT,
@organ_6_OUT			varchar(255) OUTPUT,
@location_6_OUT			varchar(255) OUTPUT,
@instrument_6_OUT		varchar(255) OUTPUT,
@description_6_OUT		varchar(255) OUTPUT,
@bottle_count_7_OUT		INT OUTPUT,
@organ_7_OUT			varchar(255) OUTPUT,
@location_7_OUT			varchar(255) OUTPUT,
@instrument_7_OUT		varchar(255) OUTPUT,
@description_7_OUT		varchar(255) OUTPUT,
@bottle_count_8_OUT		INT OUTPUT,
@organ_8_OUT			varchar(255) OUTPUT,
@location_8_OUT			varchar(255) OUTPUT,
@instrument_8_OUT		varchar(255) OUTPUT,
@description_8_OUT		varchar(255) OUTPUT

)

AS

DECLARE @count int
set @count = 1

declare @bottle_count	INT
declare @organ	varchar(255)
declare @location	varchar(255)
declare @instrument	varchar(255)
declare @description	varchar(255)


-- cursor  
DECLARE cursor1 CURSOR FOR
          
SELECT Biopsy_Bottle_Number, Organ, Biopsy_Site, Biopsy_Tool, Biopsy_Reason
FROM IGG_Biopsy_Record_
WHERE enterprise_id = @enterprise_id
AND practice_id = @practice_id
AND person_id = @person_id
AND enc_id = @enc_id
order by create_timestamp 
     
     
FOR READ ONLY

/* read first (most recent) record and assign to internal variables */

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @bottle_count, @organ, @location, @instrument, @description


set @bottle_count_1_OUT = @bottle_count
set @organ_1_OUT	= 		@organ
set @location_1_OUT		= 		@location
set @instrument_1_OUT		= 	@instrument
set @description_1_OUT		= @description

-- increment counter
set @count = @count + 1

-- NEXT LOOP NEED TO ASSIGN _2_OUT TO SAME VARIABLES, SOMETHING LIKE:

set @bottle_count_ + @count + _OUT = @bottle_count 

WHILE @@FETCH_STATUS = 0
BEGIN

-- read next record, and loop 
FETCH NEXT FROM cursor1 INTO @bottle_count, @organ, @location, @instrument, @description

END

CLOSE cursor1
DEALLOCATE cursor1

Open in new window

0
Comment
Question by:robthomas09
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40614433
What for do you need that many variables? What are you going to do with all those variables?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 40614520
Can you give us a sample of your structure, data and expected outcome? This will help us understand what you're trying to do a little better.
0
 

Author Comment

by:robthomas09
ID: 40614541
The stored proc script is for use in a program that has built placeholder fields for each of the 40 _OUT variables, so I sadly do need to have that many outputs.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 350 total points
ID: 40614558
Then I'm afraid the only solution is to use IF:
IF @count=1
	BEGIN
		set @bottle_count_1_OUT = @bottle_count
		set @organ_1_OUT	= 		@organ
		set @location_1_OUT		= 		@location
		set @instrument_1_OUT		= 	@instrument
		set @description_1_OUT		= @description
	END
ELSE IF @count=2
	BEGIN
		set @bottle_count_2_OUT = @bottle_count
		set @organ_2_OUT	= 		@organ
		set @location_2_OUT		= 		@location
		set @instrument_2_OUT		= 	@instrument
		set @description_2_OUT		= @description
	END
(... until @count=8)

Open in new window

0
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 100 total points
ID: 40614616
Another option is that you don't use a cursor, but you number the rows so that you know which row goes into which set of variables. You can do this by inserting your (max) 8 rows into a temp table and then assigning the values into the variables with 8 statements. You can even check if the row exists, just to make sure that you don't assign empty values (or NULL).

Something like this:

IF OBJECT_ID('tempdb.dbo.#tmpTable') IS NOT NULL
	DROP TABLE #tmpTable;

DECLARE @T TABLE 
(
	Biopsy_Bottle_Number INT, 
	Organ VARCHAR(255), 
	Biopsy_Site VARCHAR(255), 
	Biopsy_Tool VARCHAR(255), 
	Biopsy_Reason VARCHAR(255),
	create_timestamp DATETIME
)
INSERT INTO @T(Biopsy_Bottle_Number
	, Organ
	, Biopsy_Site
	, Biopsy_Tool
	, Biopsy_Reason
	, create_timestamp)
VALUES(1,'Organ 1', 'Site 1', 'Tool 1', 'Reason 1', GETDATE())
	, (2,'Organ 2', 'Site 2', 'Tool 2', 'Reason 2', GETDATE())
	, (3,'Organ 3', 'Site 3', 'Tool 3', 'Reason 3', GETDATE())
	, (4,'Organ 4', 'Site 4', 'Tool 4', 'Reason 4', GETDATE())
	, (5,'Organ 5', 'Site 5', 'Tool 5', 'Reason 5', GETDATE())
	, (6,'Organ 6', 'Site 6', 'Tool 6', 'Reason 6', GETDATE())
	, (7,'Organ 7', 'Site 7', 'Tool 7', 'Reason 7', GETDATE())
	, (8,'Organ 8', 'Site 8', 'Tool 8', 'Reason 8', GETDATE())

SELECT Biopsy_Bottle_Number, Organ, Biopsy_Site, Biopsy_Tool, Biopsy_Reason
	, ROW_NUM = ROW_NUMBER() OVER(ORDER BY create_timestamp)
INTO #tmpTable
FROM @T IGG_Biopsy_Record_
order by create_timestamp 

DECLARE @bottle_count_1_OUT		INT ,
@organ_1_OUT			varchar(255) ,
@location_1_OUT			varchar(255) ,
@instrument_1_OUT		varchar(255) ,
@description_1_OUT		varchar(255) ,
@bottle_count_2_OUT		INT ,
@organ_2_OUT			varchar(255) ,
@location_2_OUT			varchar(255) ,
@instrument_2_OUT		varchar(255) ,
@description_2_OUT		varchar(255) ,
@bottle_count_3_OUT		INT ,
@organ_3_OUT			varchar(255) ,
@location_3_OUT			varchar(255) ,
@instrument_3_OUT		varchar(255) ,
@description_3_OUT		varchar(255) ,
@bottle_count_4_OUT		INT ,
@organ_4_OUT			varchar(255) ,
@location_4_OUT			varchar(255) ,
@instrument_4_OUT		varchar(255) ,
@description_4_OUT		varchar(255) ,
@bottle_count_5_OUT		INT ,
@organ_5_OUT			varchar(255) ,
@location_5_OUT			varchar(255) ,
@instrument_5_OUT		varchar(255) ,
@description_5_OUT		varchar(255) ,
@bottle_count_6_OUT		INT ,
@organ_6_OUT			varchar(255) ,
@location_6_OUT			varchar(255) ,
@instrument_6_OUT		varchar(255) ,
@description_6_OUT		varchar(255) ,
@bottle_count_7_OUT		INT ,
@organ_7_OUT			varchar(255) ,
@location_7_OUT			varchar(255) ,
@instrument_7_OUT		varchar(255) ,
@description_7_OUT		varchar(255) ,
@bottle_count_8_OUT		INT ,
@organ_8_OUT			varchar(255) ,
@location_8_OUT			varchar(255) ,
@instrument_8_OUT		varchar(255) ,
@description_8_OUT		varchar(255) 

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 1)
	SELECT @bottle_count_1_OUT = T.Biopsy_Bottle_Number
		, @organ_1_OUT = T.Organ
		, @location_1_OUT = T.Biopsy_Site
		, @instrument_1_OUT  = T.Biopsy_Tool
		, @description_1_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 1

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 2)
	SELECT @bottle_count_2_OUT = T.Biopsy_Bottle_Number
		, @organ_2_OUT = T.Organ
		, @location_2_OUT = T.Biopsy_Site
		, @instrument_2_OUT  = T.Biopsy_Tool
		, @description_2_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 2

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 3)
	SELECT @bottle_count_3_OUT = T.Biopsy_Bottle_Number
		, @organ_3_OUT = T.Organ
		, @location_3_OUT = T.Biopsy_Site
		, @instrument_3_OUT  = T.Biopsy_Tool
		, @description_3_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 3

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 4)
	SELECT @bottle_count_4_OUT = T.Biopsy_Bottle_Number
		, @organ_4_OUT = T.Organ
		, @location_4_OUT = T.Biopsy_Site
		, @instrument_4_OUT  = T.Biopsy_Tool
		, @description_4_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 4

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 5)
	SELECT @bottle_count_5_OUT = T.Biopsy_Bottle_Number
		, @organ_5_OUT = T.Organ
		, @location_5_OUT = T.Biopsy_Site
		, @instrument_5_OUT  = T.Biopsy_Tool
		, @description_5_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 5

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 6)
	SELECT @bottle_count_6_OUT = T.Biopsy_Bottle_Number
		, @organ_6_OUT = T.Organ
		, @location_6_OUT = T.Biopsy_Site
		, @instrument_6_OUT  = T.Biopsy_Tool
		, @description_6_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 6

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 7)
	SELECT @bottle_count_7_OUT = T.Biopsy_Bottle_Number
		, @organ_7_OUT = T.Organ
		, @location_7_OUT = T.Biopsy_Site
		, @instrument_7_OUT  = T.Biopsy_Tool
		, @description_7_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 7

IF EXISTS(SELECT 1 FROM #tmpTable T WHERE T.ROW_NUM = 8)
	SELECT @bottle_count_8_OUT = T.Biopsy_Bottle_Number
		, @organ_8_OUT = T.Organ
		, @location_8_OUT = T.Biopsy_Site
		, @instrument_8_OUT  = T.Biopsy_Tool
		, @description_8_OUT  = T.Biopsy_Reason
	FROM #tmpTable T
	WHERE T.ROW_NUM = 8

Open in new window



P.S. I have removed the "OUTPUT" from the variable declaration, so be careful if you copying this.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40614709
You don't need cursor and you don't need to use dynamic queries if you know that always the numbers are between 1 and 8. What you need to do is to select top 8 from your query and then insert the result into a temp table to which you add an identity column which then you will use to select one row at a time. A bit of editing was needed but here is the procedure:
ALTER PROCEDURE Path_Cursor (

-- inputs
@enterprise_id			CHAR(5),
@practice_id			CHAR(4),
@person_id				varchar(36),
@enc_id					varchar(36),

-- outputs
@bottle_count_1_OUT		INT OUTPUT,
@organ_1_OUT			varchar(255) OUTPUT,
@location_1_OUT			varchar(255) OUTPUT,
@instrument_1_OUT		varchar(255) OUTPUT,
@description_1_OUT		varchar(255) OUTPUT,
@bottle_count_2_OUT		INT OUTPUT,
@organ_2_OUT			varchar(255) OUTPUT,
@location_2_OUT			varchar(255) OUTPUT,
@instrument_2_OUT		varchar(255) OUTPUT,
@description_2_OUT		varchar(255) OUTPUT,
@bottle_count_3_OUT		INT OUTPUT,
@organ_3_OUT			varchar(255) OUTPUT,
@location_3_OUT			varchar(255) OUTPUT,
@instrument_3_OUT		varchar(255) OUTPUT,
@description_3_OUT		varchar(255) OUTPUT,
@bottle_count_4_OUT		INT OUTPUT,
@organ_4_OUT			varchar(255) OUTPUT,
@location_4_OUT			varchar(255) OUTPUT,
@instrument_4_OUT		varchar(255) OUTPUT,
@description_4_OUT		varchar(255) OUTPUT,
@bottle_count_5_OUT		INT OUTPUT,
@organ_5_OUT			varchar(255) OUTPUT,
@location_5_OUT			varchar(255) OUTPUT,
@instrument_5_OUT		varchar(255) OUTPUT,
@description_5_OUT		varchar(255) OUTPUT,
@bottle_count_6_OUT		INT OUTPUT,
@organ_6_OUT			varchar(255) OUTPUT,
@location_6_OUT			varchar(255) OUTPUT,
@instrument_6_OUT		varchar(255) OUTPUT,
@description_6_OUT		varchar(255) OUTPUT,
@bottle_count_7_OUT		INT OUTPUT,
@organ_7_OUT			varchar(255) OUTPUT,
@location_7_OUT			varchar(255) OUTPUT,
@instrument_7_OUT		varchar(255) OUTPUT,
@description_7_OUT		varchar(255) OUTPUT,
@bottle_count_8_OUT		INT OUTPUT,
@organ_8_OUT			varchar(255) OUTPUT,
@location_8_OUT			varchar(255) OUTPUT,
@instrument_8_OUT		varchar(255) OUTPUT,
@description_8_OUT		varchar(255) OUTPUT

)

AS

SELECT top 8
	identity(int,1,1) as id,
	Biopsy_Bottle_Number, 
	Organ, 
	Biopsy_Site, 
	Biopsy_Tool, 
	Biopsy_Reason
	into #eight_rows
FROM 
	IGG_Biopsy_Record_
WHERE 
	enterprise_id = @enterprise_id
	AND practice_id = @practice_id
	AND person_id = @person_id
	AND enc_id = @enc_id
order by 
	create_timestamp

select 
	@bottle_count_1_OUT		Biopsy_Bottle_Number,
	@organ_1_OUT			Organ,
	@location_1_OUT			Biopsy_Site,
	@instrument_1_OUT		Biopsy_Tool,
	@description_1_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=1


select 
	@bottle_count_2_OUT		Biopsy_Bottle_Number,
	@organ_2_OUT			Organ,
	@location_2_OUT			Biopsy_Site,
	@instrument_2_OUT		Biopsy_Tool,
	@description_2_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=2

select 
	@bottle_count_3_OUT		Biopsy_Bottle_Number,
	@organ_3_OUT			Organ,
	@location_3_OUT			Biopsy_Site,
	@instrument_3_OUT		Biopsy_Tool,
	@description_3_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=3

select 
	@bottle_count_4_OUT		Biopsy_Bottle_Number,
	@organ_4_OUT			Organ,
	@location_4_OUT			Biopsy_Site,
	@instrument_4_OUT		Biopsy_Tool,
	@description_4_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=4

select 
	@bottle_count_5_OUT		Biopsy_Bottle_Number,
	@organ_5_OUT			Organ,
	@location_5_OUT			Biopsy_Site,
	@instrument_5_OUT		Biopsy_Tool,
	@description_5_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=5

select 
	@bottle_count_6_OUT		Biopsy_Bottle_Number,
	@organ_6_OUT			Organ,
	@location_6_OUT			Biopsy_Site,
	@instrument_6_OUT		Biopsy_Tool,
	@description_6_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=6

select 
	@bottle_count_7_OUT		Biopsy_Bottle_Number,
	@organ_7_OUT			Organ,
	@location_7_OUT			Biopsy_Site,
	@instrument_7_OUT		Biopsy_Tool,
	@description_7_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=7

select 
	@bottle_count_8_OUT		Biopsy_Bottle_Number,
	@organ_8_OUT			Organ,
	@location_8_OUT			Biopsy_Site,
	@instrument_8_OUT		Biopsy_Tool,
	@description_8_OUT		Biopsy_Reason
from 
	#eight_rows
where
	id=8
	

Open in new window

0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 50 total points
ID: 40614725
Oops, I forgot the = signs:
ALTER PROCEDURE Path_Cursor (

-- inputs
@enterprise_id			CHAR(5),
@practice_id			CHAR(4),
@person_id				varchar(36),
@enc_id					varchar(36),

-- outputs
@bottle_count_1_OUT		INT OUTPUT,
@organ_1_OUT			varchar(255) OUTPUT,
@location_1_OUT			varchar(255) OUTPUT,
@instrument_1_OUT		varchar(255) OUTPUT,
@description_1_OUT		varchar(255) OUTPUT,
@bottle_count_2_OUT		INT OUTPUT,
@organ_2_OUT			varchar(255) OUTPUT,
@location_2_OUT			varchar(255) OUTPUT,
@instrument_2_OUT		varchar(255) OUTPUT,
@description_2_OUT		varchar(255) OUTPUT,
@bottle_count_3_OUT		INT OUTPUT,
@organ_3_OUT			varchar(255) OUTPUT,
@location_3_OUT			varchar(255) OUTPUT,
@instrument_3_OUT		varchar(255) OUTPUT,
@description_3_OUT		varchar(255) OUTPUT,
@bottle_count_4_OUT		INT OUTPUT,
@organ_4_OUT			varchar(255) OUTPUT,
@location_4_OUT			varchar(255) OUTPUT,
@instrument_4_OUT		varchar(255) OUTPUT,
@description_4_OUT		varchar(255) OUTPUT,
@bottle_count_5_OUT		INT OUTPUT,
@organ_5_OUT			varchar(255) OUTPUT,
@location_5_OUT			varchar(255) OUTPUT,
@instrument_5_OUT		varchar(255) OUTPUT,
@description_5_OUT		varchar(255) OUTPUT,
@bottle_count_6_OUT		INT OUTPUT,
@organ_6_OUT			varchar(255) OUTPUT,
@location_6_OUT			varchar(255) OUTPUT,
@instrument_6_OUT		varchar(255) OUTPUT,
@description_6_OUT		varchar(255) OUTPUT,
@bottle_count_7_OUT		INT OUTPUT,
@organ_7_OUT			varchar(255) OUTPUT,
@location_7_OUT			varchar(255) OUTPUT,
@instrument_7_OUT		varchar(255) OUTPUT,
@description_7_OUT		varchar(255) OUTPUT,
@bottle_count_8_OUT		INT OUTPUT,
@organ_8_OUT			varchar(255) OUTPUT,
@location_8_OUT			varchar(255) OUTPUT,
@instrument_8_OUT		varchar(255) OUTPUT,
@description_8_OUT		varchar(255) OUTPUT

)

AS

SELECT top 8
	identity(int,1,1) as id,
	Biopsy_Bottle_Number, 
	Organ, 
	Biopsy_Site, 
	Biopsy_Tool, 
	Biopsy_Reason
	into #eight_rows
FROM 
	IGG_Biopsy_Record_
WHERE 
	enterprise_id = @enterprise_id
	AND practice_id = @practice_id
	AND person_id = @person_id
	AND enc_id = @enc_id
order by 
	create_timestamp

select 
	@bottle_count_1_OUT		=Biopsy_Bottle_Number,
	@organ_1_OUT			=Organ,
	@location_1_OUT			=Biopsy_Site,
	@instrument_1_OUT		=Biopsy_Tool,
	@description_1_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=1


select 
	@bottle_count_2_OUT		=Biopsy_Bottle_Number,
	@organ_2_OUT			=Organ,
	@location_2_OUT			=Biopsy_Site,
	@instrument_2_OUT		=Biopsy_Tool,
	@description_2_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=2

select 
	@bottle_count_3_OUT		=Biopsy_Bottle_Number,
	@organ_3_OUT			=Organ,
	@location_3_OUT			=Biopsy_Site,
	@instrument_3_OUT		=Biopsy_Tool,
	@description_3_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=3

select 
	@bottle_count_4_OUT		=Biopsy_Bottle_Number,
	@organ_4_OUT			=Organ,
	@location_4_OUT			=Biopsy_Site,
	@instrument_4_OUT		=Biopsy_Tool,
	@description_4_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=4

select 
	@bottle_count_5_OUT		=Biopsy_Bottle_Number,
	@organ_5_OUT			=Organ,
	@location_5_OUT			=Biopsy_Site,
	@instrument_5_OUT		=Biopsy_Tool,
	@description_5_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=5

select 
	@bottle_count_6_OUT		=Biopsy_Bottle_Number,
	@organ_6_OUT			=Organ,
	@location_6_OUT			=Biopsy_Site,
	@instrument_6_OUT		=Biopsy_Tool,
	@description_6_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=6

select 
	@bottle_count_7_OUT		=Biopsy_Bottle_Number,
	@organ_7_OUT			=Organ,
	@location_7_OUT			=Biopsy_Site,
	@instrument_7_OUT		=Biopsy_Tool,
	@description_7_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=7

select 
	@bottle_count_8_OUT		=Biopsy_Bottle_Number,
	@organ_8_OUT			=Organ,
	@location_8_OUT			=Biopsy_Site,
	@instrument_8_OUT		=Biopsy_Tool,
	@description_8_OUT		=Biopsy_Reason
from 
	#eight_rows
where
	id=8
	

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40614897
Can I ask why you chose the other solutions as preferable over mine? I am just curios. When it comes in terms of simplicity and efficiency I don't think they are better at all. As a general rule you don't have to choose the first solution you are given.

Thank you.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Microsoft – Kerberos Configuration Manager. Delegation service account query 1 37
sql, case when & top 1 14 29
SQL, add where clause 5 24
Select question from MySQL 1 13
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question