Little help with procedure and temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Little help with procedure and temporary table

Author

Topic

Gaspa79
Starting Member

2 Posts

Posted - 2011-08-25 : 12:06:20

Hello! =)

As the title says, my goal is to create a procedure that returns a temporary table.

Currently, I have this while block:

DECLARE @Names VARCHAR(8000)
Declare @msb int
select @msb = 1
While @msb < 40
begin
Select @Names = NULL
SELECT @Names = COALESCE(@Names + '<br>', '') + Detalle FROM iorlarauz.dbo.GViewEstudioDetalles
where IDEstudio = @msb
Select distinct IDServicio, Estudio, @Names from iorlarauz.dbo.GViewEstudioDetalles
where IDEstudio = @msb

select @msb = @msb + 1
end

Which returns 40 tables with 1 row each.
My goal is to insert each row into a table or temporary table using a procedure.

I've no clue, and I mean it. I'm a Java programmer starting with sql reading tutorials n stuff. The thing is, I don't have time for this this weekend... so I have to ask for help from you guys =(

Thank you very much in advance!

Damian

The years teach much the days never knew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-25 : 12:18:21

Something like this

Create Table #t (IDServicio int, Estudio int, names varchar(8000))

DECLARE	@Names VARCHAR(8000)
Declare	@msb int 
select	@msb = 1
While	@msb < 40
begin
	Select	@Names = NULL
	SELECT	@Names = COALESCE(@Names + '<br>', '') + Detalle
	FROM	iorlarauz.dbo.GViewEstudioDetalles
	where	IDEstudio = @msb
	
	INSERT	#t
	Select	distinct IDServicio, Estudio, @Names
	from	iorlarauz.dbo.GViewEstudioDetalles
	where	IDEstudio = @msb

select @msb = @msb + 1
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 12:36:57

seeing your requirement, i feel like what you may be after is a table valued function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gaspa79
Starting Member

2 Posts

Posted - 2011-08-26 : 08:28:50

I've no idea what a table valued function is, but that code worked!

It's actually a pretty simple modification, you just create a temporary table (now I know how to), and insert the results of my select statement that returned 40 different tables with 1 row. It's perfect.

I just added a final select statement to select from the temporary table and it worked flawlessly. I also drop the temporary table at the end to prevent some possible issues.

Thanks very much for your help guys, I'm gonna check that table valued function thing now.

Thanks again =)!

The years teach much the days never knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources