Stored procedures SQL server 7

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

SQL Server Development (2000)

Stored procedures SQL server 7

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-20 : 08:28:24

Georg writes "Hi,

Is there any possibility to use stored procedures as part of a select statement?

something simple like

1.define a stored procedure 'sp_test'
2.sp_test returns the fieldname 'name' from table person

example:

select id, exec sp_test from tbperson

thank you for your help

kind regards Georg"

dsdeming

479 Posts

Posted - 2002-05-20 : 08:35:01

NO, that won't work. You could try:

INSERT INTO #Temp EXECUTE sp

and then join #temp to your other table.

mfemenel
Professor Frink

1421 Posts

Posted - 2002-05-20 : 13:01:41

a cleaner alternative might be a derived table

select t.id,base.name
from
(select id,name from tblperson) as base
inner join
maintable t on t.id=base.id

Mike
"oh, that monkey is going to pay"

gsnk
Starting Member

24 Posts

Posted - 2002-05-20 : 17:32:40

although mfemenel's solution is the best, you have another option if you're using SS2K: UDFs

inside the function you can run the necessary select statement and return a string that could then be used in your select statement. eg:

CREATE FUNCTION dbo.fn_test (@Variable INT)
RETURN VARCHAR(50)
AS
BEGIN
DECLARE @RetVal VARCHAR(50)

SELECT @RetVal = Name FROM Person WHERE UserID = @Variable
RETURN @RetVal
END
-----------------------

Now you can use that function inline like this:

SELECT id, dbo.fn_test(id) AS Name FROM tbperson

The thing is that in this particular case it doesn't make much sense to use a UDF but there are other cases (date/time manipulation comes to mind) where this comes in VERY handy.

;)

Gero

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources