UDFs and SPs - 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

SQL Server 2000 Forums

Transact-SQL (2000)

UDFs and SPs

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 14:52:30

Hi,

I am trying to expand my knowledge of SQL so I started messing with user defined functions and stored procedures. I read some stuff about inline table and scalar functions and I thought I could solve my problem but obviously I have not :-(. Hence the question:

3 tables are involved:
-table_1 has 2 columns (machine and GUID)
-table_2 has 2 columns (component and GUID)
-table_3 has multiple columns (2 of which are the machine GUID and component GUID)

So the idea is to create a stored procedure that would create a new entry in table_3, but since I don't want to have the user know or remember the GUID, i thought I could write 2 functions(fx_mGUID,fx_cGUID) that would return the GUID from table_1 and table_2 respectively

So I imagined it to look like this

exec NewEntry 'item1',fx_mGUID('machine_name'),fx_cGUID('comp_name')

and this never worked

so I wanted to test my functions by themselves and I got this:

exec fx_mGUID 'machine_name'

The request for procedure 'fx_mGUID' failed because 'fx_mGUID' is a table valued function object.

SO any inupt on how I should approach this or what I am doing wrong with the current approach will be very appreciated

Thanx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 15:13:23

You cannot use a function as a stored procedure parameter.

exec NewEntry 'item1',fx_mGUID('machine_name'),fx_cGUID('comp_name'

You cannot execute a function.

exec fx_mGUID 'machine_name'

Use a table valued function in a select statement in place of a table

select * from fx_mGUID('machine_name')

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-13 : 15:15:39

your functions need to be returning scalars, not tables, if you want to pass the result to a sproc.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 15:17:00

I am sorry I did not make that clear, but I actually am using inline TVF. this is the whole statement as is right now:

exec InsertSTDTask 'Task One',1,(SELECT * from dbo.fx_GetCompGUID('COMP A')),(SELECT * from dbo.fx_GetMachineGUID('MACHINE A'))
but it doesn work. this is the error message:

Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 15:19:29

jezemine, I didn't have much luck writing a scalar function which has a query in itself.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 15:21:51

You cannot use a select statement as a stored procedure parameter.

Stored procedure parameters must be a literal or a local variable.

CODO ERGO SUM

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-13 : 15:24:51

What they said, plus

Because you cannot pass function calls as parameters, you'll need to do this to actually call the stored proc.

declare @mGUID uniqueidentifier
declare @cGUID uniqueidentifier
select @mGUID = dbo.fx_mGUID('machine_name')
, @cGUID = fx_cGUID('comp_name')
exec NewEntry 'item1', @mGUID, @cGUID

Alternatively, you could make the machine name and the component name the parameters to the stored proc and then call the functions inside the stored proc to get the GUIDs, which is probably even friendlier for the users.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-13 : 15:27:38

quote:
Originally posted by ronin2307

jezemine, I didn't have much luck writing a scalar function which has a query in itself.

It will be something like this

create function fx_mGUID(@machinename varchar(20))
returns uniqueidentifier 
as
begin
	declare @GUID
	select @GUID = guidCol from myTable where machinename = @machinename
	return @GUID
end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 15:37:35

quote:
Originally posted by snSQL

quote:
Originally posted by ronin2307

jezemine, I didn't have much luck writing a scalar function which has a query in itself.

It will be something like this
create function fx_mGUID(@machinename varchar(20))
returns uniqueidentifier
as
begin
declare @GUID
select @GUID = guidCol from myTable where machinename = @machinename
return @GUID
end

Just to be safe, I would do this:

select top 1 @GUID = guidCol from myTable where machinename = @machinename

CODO ERGO SUM

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-13 : 16:58:49

many thanx to all. the collection of information offerd here help me do what I needed
thanx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources