Function Question

fullingdale · April 7th, 2011, 11:07 AM

Could someone offer a simple example of a function which takes an input parameter, such as a NAME and returns the appropriate ID for the value from a table:

ID NAME
1 Dave
2 Brian
3 Charles

But, where the NAME is not present, add it to the list and return the new ID

function "Dave" = 1
function "Brian" = 2
function "Robert" = 4 (and "Robert" added to the table)

om_prakash · April 8th, 2011, 01:34 AM

You will not be able to insert/alter the data in a function. Instead you need to create stored procedure which does this.
something like:

Code:

ALTER procedure dbo.getData (@username varchar(50), @userid int output)
AS
begin
select @userid = userid from users where userName = @username
if @userid is not null 
begin
insert into users (username) values (@username)
SELECT @userid = @@IDENTITY 
end
end

fullingdale · April 8th, 2011, 04:14 AM

Thank you for the info.