Create a function inside of procedure? - 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 2000 Forums

Transact-SQL (2000)

Create a function inside of procedure?

Author

Topic

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-06 : 13:48:38

Hi,
I came from Oracle and newbie in Sql Server. I need to create a local function inside of my procedure then call it inside of my procedure. Is that possible? If yes, what is the syntax? Thanks.

Something like the following.

Create Proc MyProc
declare ....

create myFunction (....,....) return varchar(10)
declare ...
begin
return 'Test'
end
....
....

If myFunction(...,...) = 'Test'
begin
print ....
end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-06 : 13:56:54

It is not possible to create function inside Stored Proc. You will have to create function separately and call it inside your SP.

Create Function Xyz(...)
Returns varchar(10)
as
begin
...
end

Create Procedure MyProc
(...)
as
begin
Declare @v varchar(10)

set @v = dbo.xyz(...)
...
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-06 : 14:02:42

Thanks Harsh.
I guess that could be a good enhancement for Sql Server next version. That way we can package all related codes in one place.
Thanks once again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-06 : 14:33:59

That would definitely not be a good enhancement for SQL Server! There is no reason to do it that way. Please explain why you think you'd want this feature.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-06 : 15:23:03

!

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-06 : 18:14:56

quote:
Originally posted by tkizer

That would definitely not be a good enhancement for SQL Server! There is no reason to do it that way. Please explain why you think you'd want this feature.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Tara,
Here is a sample of what I want to do. What's your local object alternative for Function myFunc in the following sample?

CREATE PROC myProc
AS
DECLARE ...

FUNCTION myFunc (@c1 int, @c2 int, @c3 int, @c4 int)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @MissKeys varchar(50)
SET @MissKeys=''

IF @c1 IS NULL SET @MissKeys='Col1,'
IF @c2 IS NULL SET @MissKeys=@MissKeys+'Col2,'
IF @c3 IS NULL SET @MissKeys=@MissKeys+'Col3,'
IF @c4 IS NULL SET @MissKeys=@MissKeys+'Col4,'

IF substring(@MissKeys,len(@MissKeys),1)=','
BEGIN
SET @MissKeys = substring(@MissKeys,1,len(@MissKeys)-1)
END

RETURN @MissKeys
END

...
...
...

SELECT col1,col2,col3,col4, myFunc(col1,col2,col3,col4) AS MissingKeys
FROM table_name
WHERE col1 is NULL
OR col2 is NULL
OR col3 is NULL
OR col4 is NULL

/* END OF PROC */

RESULT:

col1 col2 col3 col4 MissingKeys
----- ----- ----- ----- ---------------
100 200 300 400 null
null 201 301 null Col1,Col4
null 202 null null Col1,Col3,Col4

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-06 : 18:23:06

I don't understand what you are trying to do. Could you show us a before example of your data as well?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-06 : 18:29:43

I cannot post the exact code.
Never mind. I go for a global function.
Thank You.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-06 : 18:36:30

You don't have to post code for us to help you. You just need to show us a data example. Show the before and after.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-06 : 19:21:56

quote:
Originally posted by Ali Chadorbaf

I cannot post the exact code.
Never mind. I go for a global function.
Thank You.

Ah, but you don't have to:

select	col1
	,col2
	,col3
	,col4
	,substring(	case when col1 is null then ',col1' else '' end
			+case when col2 is null then ',col2' else '' end
			+case when col3 is null then ',col3' else '' end
			+case when col4 is null then ',col4' else '' end, 2, 50)
from
	(	
		select 1,2,3,4
		union all
		select 5,6,7,null
		union all
		select null,8,9,10
		union all
		select null,11,12,null
		union all
		select 13,null,null,14
		union all
		select null,null,null,null
	) as cols(col1,col2,col3,col4)
where
	col1 is null
	or col2 is null
	or col3 is null
	or col4 is null

>> "I guess that could be a good enhancement for Sql Server next version"
Maybe, maybe not. Local functions, yes cool.
I think they can be put to both use and abuse equally

rockmoose

Ali Chadorbaf
Starting Member

44 Posts

Posted - 2007-04-09 : 12:16:31

Thanks rockmoose,
I'll try your suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources