EXECUTE A STORED PROCEDURE IN A SELECT STATEMENT

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

SQL Server 2000 Forums

Transact-SQL (2000)

EXECUTE A STORED PROCEDURE IN A SELECT STATEMENT

Author

Topic

pipi
Starting Member

18 Posts

Posted - 2005-05-20 : 03:49:51

HI!!!

I HAVE A STORED PROCEDURE which return a double value and i want to execute it in a select statement.for each row from one table.
e.g

set dateformat dmy;
declare @out1 FLOAT
declare @apo datetime
declare @eos datetime
SELECT @apo=cast('01/01/2005' as datetime)
SELECT @eos=cast('31/12/2005' as datetime)

select ktm_loga_typo,
ktm_loga_kodi,
ktm_loga_dik1,
@out1=exec GetPelaLogaMesoYpolVale ktm_loga_dik1,ktm_loga_kodi,@apo,@eos,@mesoypol=@out1 OUTPUT
from ktm

what I m I doing wrong?
please, can anyone help me with this one?

/deppi

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 04:07:38

You can't do that.
Create a function instead would be the best bet or populate a temp table and use the sp to update it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-20 : 04:17:32

use function:

select ktm_loga_typo,
ktm_loga_kodi,
ktm_loga_dik1,dbo.GetPelaLogaMesoYpolVale(ktm_loga_dik1,ktm_loga_kodi) from ktm

CREATE FUNCTION dbo.GetPelaLogaMesoYpolVale(
@inputfield1 datatype,
@inputfield2 datatype,
)
RETURNS datatype AS
BEGIN
DECLARE @retVal Datatype
{to do
SET @retVal = ......
}

RETURN @RetVal

END

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

pipi
Starting Member

18 Posts

Posted - 2005-05-20 : 05:12:40

I FIXED THE STORED PROCEDURE AS A FUNCTION BUT I USED TEMPORARY TABLES SO,I GET THIS ERROR MESSAGE
"CANNOT ACCES TEMP TABLES FROM WITHIN A FUNCTION.

???

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 05:22:29

Means what it says.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-20 : 05:22:41

you cannot use temp tables in a function, use it on the stored procedure..

Cursors are for those who doesn't know how to use SQL
K.I.S.S. - Keep it simple stupid
raclede™

pipi
Starting Member

18 Posts

Posted - 2005-05-20 : 06:14:17

thanks.I tried this with cursor.I use cursors for first time,but i still have a problem.the execution of the stored procedure is not working

e.g

dECLARE @typo varchar(20)
declare @apo datetime
declare @eos datetime
DECLARE @meso money

SELECT @apo=CONVERT(datetime,'01/01/2005')
--SELECT @eos=cast('31/12/2005' as datetime)
SELECT @eos=CONVERT(datetime,'31/12/2005')

DECLARE c2 CURSOR FOR
select ktm_loga_kodi,ktm_loga_dik1
from ktm

OPEN c2

FETCH NEXT FROM c2
INTO @loga,@pela

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @loga
print @pela
EXEC GetPelaLogaMesoYpolVale @pela,@loga,@apo,@eos,@mesoypol=@meso OUTPUT

FETCH NEXT FROM c2
INTO @loga,@pela

END

CLOSE c2
DEALLOCATE c2

can you help me.
i want for reach row to return 3 columns

pela,loga,mesoypol

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-05-20 : 20:34:50

Turn you temp table into a declare table and you will be able to use a function.

Mike Petanovitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources