Sql way to do oracle functions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Sql way to do oracle functions?

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-06-11 : 15:27:23

Afternoon,

I am trying to do what I used to do with oracle, never thought I'd say that, create a reusable piece of code to return data from a select statement. All I want is if I put in a unit: job, material or sales order I want the program returned. I tried a couple of ways with no success. What I'm trying to do is embed the function/sp into a select statement:

Like so:


select function_name(job) as Program, job
from job

I have a function:


ALTER FUNCTION [dbo].[GetProgramName]
(
	-- Add the parameters for the function here
	@Unit nvarchar(25)
)
RETURNS varchar(30)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Program varchar(30)

	-- Add the T-SQL statements to compute the return value here
	select @Program = program
	FROM vw_GetProgram
	WHERE unit = @Unit

	-- Return the result of the function
	RETURN @Program

END

and a stored proc


CREATE PROCEDURE sp_GetProgramName
	@Unit nvarchar(25)
AS
BEGIN
	
	SET NOCOUNT ON;

	select program
	FROM vw_GetProgram
	WHERE unit = @Unit
END
GO

Any ideas would be great.

Thanks

Laura

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-06-15 : 12:18:52

You should be doing set-based processing, of course, but your function looks fine to me. What's your problem?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources