insert within a function

CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END
CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END

I m getting error at the Insert statement, it says error 443, invalid use of insert within a function,

Cann we use insert in a function, if we cann, what is the alternative to insert the values?
do help me asap.Try moving the "Insert" into a stored procedure and then "exec procedure" from your function. Other solution would be to transform your function in a stored procedure by itself|||Did you look at BOL?

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.

And why are you define the same udf twice...and why isn't this a sproc?

Insert thru a view to a table with an IDENTITY property

Why doesn't my identity property function normally when I try to insert
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:

>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>

>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.

>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert stored procedure with error check and transaction function

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char](4),
@.contactId [int])
as

if not exists (select studentid
from student
where studentid = @.studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @.contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

/*Error Check */
if @.@.error !=0 or @.@.rowcount !=1
begin
rollback transaction
print Insert is failed
return -1
end
print New contract has been added

commit transaction
return 0
goI recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

exec sp_insert_new_contract
@.contractDate = '01/01/2004',
@.tuition = 3000,
@.studentId = 'ABCD',
@.contactId = 1