Stored SQL Functions & VB.net 2

[RoguePoet01]

Hi,

I'm stepping up to VB.net and just read an article about using stored SQL functions.

Though I use SQL for my database, I've never had the need to write a stored SQL function before, but this sounds like a good idea.

Would anybody have the time to show me a simple function that inserts three fields into a table, and returns the "RecIndex" which is the primary/identity key.

This is some pseudocode to help fill in the blanks:

Table "ClubMember"
RecIndex (primary key)
Name (char 35)
Age (numeric)
BirthDate (datetime)

CREATE FUNCTION AddMember
(@NAME CHAR(35), @AGE NUMERIC, @BIRTHDATE DATETIME)
RETURNS RI
AS
BEGIN
INSERT INTO CLUBMEMBER(Name, Age, Birthdate)
VALUES ('Albert', '33', '7/17/1977')
RETURN RI
END

 

[NoCoolHandle]

CREATE FUNCTION AddMember
(@NAME CHAR(35), @AGE NUMERIC, @BIRTHDATE DATETIME)
RETURNS RI int
AS
BEGIN
INSERT INTO CLUBMEMBER(Name, Age, Birthdate)
VALUES ('Albert', '33', '7/17/1977')
SEt RI = @@identity
RETURN RI
END

THis of course assums an identity property and no other tables that might be effected by an insert having an identity column and experiencing an insert because of the original insert..


Rob

 

[ESquared]

NoCoolHandle,

NEVER use @@Identity!!! Always use Scope_Identity()!!!! you can run into serious data corruption issues, otherwise. Search these forums for dozens of posts explaining why.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[donutman]

I can't think of a reason to use a function instead of a stored procedure (especially considering that you intend to call it from VB). There may be one but the vast majority of the time it would be more efficient to use a SP.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Karl is right! You can return a value from a stored procedure just the same as a function. And there might be some compilation benefits.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[NoCoolHandle]

ESquared...

Identity Scope has it's problems also... It is a matter of knowing what the weeknesses are that allows you to make a good choice of which function to use for which situtaion.

Wouldn't you agree?

 

[NoCoolHandle]

ESquared,

sorry.. I think I spent alot of time playing with this a few years back and found @@identity and scope_identity() to return much the same results.. (or at least that is what my memory seems to be telling me), I would like to think I was thinking of Ident_Current() but... I don't think so, I think maybe I was just wrong

Definitly not what is occouring now. (Rogue - use scope_identity())

Just a quick demo of how it can work

use crap
go
drop table a;drop table b; drop table c
go
create table a (i int identity (1000,1))
create table b(i int identity(2000,1),ip int)
create table c (i int identity(3000,1),ip int)
go
create trigger a_insert on a for insert
as
insert into b (ip) select max(i) from inserted
go
create trigger b_insert on b  for insert
as
insert into c (ip) select max(i) from inserted
go
insert into a default values
go

select @@identity as '@@identity' , SCOPE_IDENTITY() as 'SCOPE_IDENTITY()' , IDENT_CURRENT ('a') as 'IDENT_CURRENT (''a'') '
select * ,null,'Table a' from a  union
select * , 'Table b'  from b union
select * ,'Table c' from c

 

[RoguePoet01]

Thank you very much, everyone, for your professional wisdom.

The reason I was choosing a function is because I thought that a function was the only thing that would return a value (that's the way it is in vb).

I can't figure out how to do the insert from vb and get the record index at the same time. Basically I've been inserting the record then immediately retrieving the primary key (index). Works fine, for the amount of data entry we do, but I could see a possible problem with that in the future.

I'll give this code a try and I definitely appreciate your help!

 

[SQLSister]

No cool Handle, interesting datbase name you have there. Nice demo as to why to use scope_identity(). You can clearly see that @@identity returns the wrong identity.

Questions about posting. See faq183-874