SQL User Defined Function Help

[noobman]

Is there some magic to user-defined functions that I'm not understanding? I have created the following function:

-- contents of sql file to create function
CREATE FUNCTION fx_createProblemCategoryTest3
(@problemCategory nvarchar(128))
RETURNS nvarchar(128)
AS 
BEGIN
	declare @sym as nvarchar(128);


	-- This is the argument we pass
	set @sym=@problemCategory;
	-------------------------------

        -- work some magic... I've tested all the middle stuff and it works!
	insert into prob_ctg (sym, id, persid)
	values (@sym, @id, @persid);
	
	return @sym
END

As I understand it, that should create a function that I can use at anytime in the DB. The code within the function works.

Now, how do I execute this function??

What I really want to do is have the following run from a SQL statement:

-- contents of runMe.sql
fx_createProblemCategoryTest3('Cat1');
fx_createProblemCategoryTest3('Cat2');
fx_createProblemCategoryTest3('Cat3');
...
fx_CreateProblemCategoryTest3('Cat300');

I would be executing the SQL file from a batch script using osql (batch script is coded and works already). I don't know very much when it comes to SQL =(


The real goal is to have @sym be assigned to a passable argument, so that I can run my script automatically, instead of having to strip the code out of a function and running it 300+ times to add all of our problem categories.

 

[calebb]

You can't use SET inside a UDF. edit: I take that back! You can define variables with a set command no problem. But you should probably use a sproc, not a udf for this purpose.

I think you just want to use a regular stored procedure...


To use a UDF, you would do something like this:


DECLARE @idOrder INT
SET @idOrder=123123
SELECT dbo.getShippingFeeByIdOrder(@idOrder) AS ShippingFee


If you really wanted to use a (scalar) UDF for some reason, you should use @problemCategory in place of @sym in your insert and not attempt to redefine it into a new variable.

 

[noobman]

I didn't even know there was such a thing.


I just looked up stored procedures, and it sounds like what I'm looking for (which is the SQL equivalent of creating and calling a function) I will try it and let you know how it goes. Thanks!

 

[mikeblas]

Stored procedures aren't functions; user-defined functions are. It's very important to be clear about the differences; otherwise, you'll end up quite confused.

 

[max105]

Also, when executing user defined functions, you need to prefix the owner of the function. In other words, you need to add the "dbo" prefix or whoever the owner of the function is when executing it:

[B]dbo.[/B]fx_createProblemCategoryTest3('Cat1');

 

[noobman]

Thanks for the help, everyone. I *think* I have a better understanding of the difference between a UDF and a sproc.

I wound up using a sproc w/ added logic to ensure that no duplicates would be created (ID is the PK, thus it would be easy for a user to accidentally create multiple copies of the same category).