April 29, 2005 at 3:39 am
I'm new to SQL Server. I'm trying to convert some Oracle code to run on SQL Server 2000.
I've got a function that selects a row for update, does some manipulation of what it finds, increments a numeric value on the row and updates it back to the db, then returns the manipulated data as the return value from the function.
I've read that I can't use INSERT, UPDATE, DELETE from within a user defined function in SQL Server (and I can't call a procedure from within a user defined function).
Any ideas how to replicate this kind of logic in SQL Server?
Thanks for any suggestions,
Ian.
April 29, 2005 at 4:49 am
Use a stored procedure.
**ASCII stupid question, get a stupid ANSI !!!**
April 29, 2005 at 9:03 am
But I've read that in SQL Server you can't call a stored procedure from a function (or at least one that does a INSERT/UPDATE/DELETE anyway). I think I need to use a function because I need the manipulated data in the return value not just a 'success' or 'failure' type return code that you get from a procedure.
Thanks
Ian.
April 29, 2005 at 9:19 am
Ian - you can get more than a success/failure return from a stored procedure - you can get back a single value; a recordset - whatever you want!
**ASCII stupid question, get a stupid ANSI !!!**
April 29, 2005 at 9:28 am
Thanks for that. I'll give it a go. My oracle experience suggested I needed a function.
April 29, 2005 at 9:30 am
eg: this may help get you started.....
CREATE PROCEDURE MyProcedure
@RowIDInt,
@NumericVal Int
AS
DECLARE @ReturnVal Int
BEGIN
UPDATE MyTable
SET NumericVal = NumericVal + @NumericVal
WHERE RowID = @RowID
END
SELECT @ReturnVal = NumericVal FROM MyTable WHERE RowID = @RowID
RETURN @ReturnVal
**ASCII stupid question, get a stupid ANSI !!!**
April 29, 2005 at 10:25 am
Sushila,
That's really kind of you - thanks. It's sure to speed me up. I'll be trying it out next week. If you've got the time/inclination to help me further (no worries if not - you've helped a lot already) ...
I see you've got an 'int' return value - can procedures return 'varchar' return values? The numeric component that is incremented is just one part of a string.
The idea is to feed some parameters in, read the correct row based on the input paramters, retrieve various filepath & filename components from the db row (including a numeric component for uniqueness), build the full hierarchic filename and update the numeric column on the row to ensure that next time the process runs, the filename generated in unique.
Different jobs will call the process and they will require different filepaths, filename prefixes, suffixes, component separators, extensions, the numeric component may be different lengths (e.g. "path\x_0001_data.txt" or "path\abc0000001.dat" or "path\xyz-00000000001-file.abc")
I want the process to return the entire new path & filename (well that's how the existing oracle function works anyway).
Thanks,
Ian.
April 29, 2005 at 12:06 pm
Ian - here's some stuff that might help you start looking in the right direction....(re:BOL)
Returning Data from a Stored Procedure
Microsoft® SQL Server™ 2000 stored procedures return data in four forms:
Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time). For more information about using cursor variables, see Scope of Transact-SQL Cursor Names.
Return codes, which are always an integer value.
A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure. For more information about using the SELECT statement, see Query Fundamentals.
A global cursor that can be referenced outside the stored procedure. For more information about using cursor variables, see Scope of Transact-SQL Cursor Names.
******************************************************************************************
Returning Data Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.
Examples
The following example shows a stored procedure with an input and an output parameter. The first parameter in the stored procedure @title receives the input value specified by the calling program, and the second parameter @ytd_sales is used to return the value to the calling program. The SELECT statement uses the @title parameter to obtain the correct ytd_sales value, and assigns the value to the @ytd_sales output parameter.
CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS
-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
RETURN
GO
**ASCII stupid question, get a stupid ANSI !!!**
April 29, 2005 at 4:13 pm
you can use a User Defined Function to return a varchar value since stored procs only return INT type values.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 29, 2005 at 5:34 pm
Ian seems to have other constraints that he cannot use within an UDF - you can use a select statement to return recordsets from the procedure & I've used concatenated varchar fields to return values to code!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply