Question about SP - SQL Server Forums

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

Question about SP

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-29 : 18:13:22

Hi!

I have a view, but I need to send a parameter to the view, I think that this is not posible.

So,I was thinking to do the query of the view in a sp, but I don't know how to do to get the result of the sp in a table var, to uses it in others store procedures.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-29 : 18:21:18

If you post your code, then we can probably help.

SELECT ...
FROM YourViewName
WHERE SomeViewColumn = @SomeParameter

To put the result of a stored procedure into a temp table (you can't use a table variable with INSERT and EXEC):

CREATE TABLE #TempTable...

INSERT INTO #TempTable
EXEC dbo.SomeProc

Tara Kizer
aka tduggan

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-29 : 18:54:16

This is the vw code, this returns me the last calification of each course (cla_curso) that a person (cla_trab) took.

SELECT CLA_EMPRESA, CLA_DEPTO, CLA_TRAB, CLA_CURSO, FECHA_ULT_CAMBIO,
calificacion, folio_curso
FROM dbo.RH_CURSO_TRAB
WHERE
and folio_curso= ( select max(r.folio_curso)
from RH_CURSO_TRAB r
where r.cla_empresa=RH_CURSO_TRAB.cla_empresa and
r.cla_depto=RH_CURSO_TRAB.cla_depto and
r.cla_trab=RH_CURSO_TRAB.cla_trab and
r.cla_curso=RH_CURSO_TRAB.cla_curso and

)

But for example if I do a report of a day that is not today maybe 01/31/2006 and a person took the course number 150 on 01/017/2006, and in 03/22/2006, the vw not will have the information of the 01/31/2006, and in my query ( one that join the last calification per course with another vw that have the list of the person that must took the course) this vw is not going to work so that is way I need to do something like a sp that return a table because in most of the report this kind of information will be require.

SELECT CLA_EMPRESA, CLA_DEPTO, CLA_TRAB, CLA_CURSO, FECHA_ULT_CAMBIO,
calificacion, folio_curso
FROM dbo.RH_CURSO_TRAB
WHERE
and folio_curso= ( select max(r.folio_curso)
from RH_CURSO_TRAB r
where r.cla_empresa=RH_CURSO_TRAB.cla_empresa and
r.cla_depto=RH_CURSO_TRAB.cla_depto and
r.cla_trab=RH_CURSO_TRAB.cla_trab and
r.cla_curso=RH_CURSO_TRAB.cla_curso and
(r.fecha_ult_cambio
BETWEEN CONVERT(DATETIME, '1994-12-01 00:00:00', 102) AND
CONVERT(DATETIME, '2006-03-28 00:00:00', 102))
)
order by FECHA_ULT_CAMBIO desc

I add the between because maybe later they will one just the course that was took in some period, the date 1994-12-01 is the date that they start to capture this information.

( of course this information was in dbfs and now the bought a new RH system with db in SQL Server 2000)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources