UDF, SPROC in SQL Server Express 2008
-
Hi, I'd like to put all my database management in StoredProcedures or Functions, but I'm having a small problem: 1) I know I can't use INSERT, UPDATE or DELETE in UDFs, right? 2) So i created a SPROC that inserts a record to a simple table (id, name) and returns the ID in an output parameter. It's working fine. 3) I create a UDF that executes this SPROC and returns the ID... and here is my problem... looks like I can't execute my own SPROCs from a UDF. :( 4) So.. how do I achieve this goal? Inserting a record and returning the auto generated id back to my web browser (asp.net application). Here is some of my code:
CREATE PROCEDURE finsTeste
@snome varchar(50),
@nid int output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO teste (nome) VALUES (@snome)
SELECT @nid = SCOPE_IDENTITY()
END
GO
CREATE FUNCTION insTeste
(
@snome varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @nid int
EXECUTE finsTeste @snome, @nid output
RETURN @nid;
END
GOAnd then in the asp.net page I run a ExecuteScalar(StoredProcedure, "insTeste", params);
Thanks, Dirso
-
Hi, I'd like to put all my database management in StoredProcedures or Functions, but I'm having a small problem: 1) I know I can't use INSERT, UPDATE or DELETE in UDFs, right? 2) So i created a SPROC that inserts a record to a simple table (id, name) and returns the ID in an output parameter. It's working fine. 3) I create a UDF that executes this SPROC and returns the ID... and here is my problem... looks like I can't execute my own SPROCs from a UDF. :( 4) So.. how do I achieve this goal? Inserting a record and returning the auto generated id back to my web browser (asp.net application). Here is some of my code:
CREATE PROCEDURE finsTeste
@snome varchar(50),
@nid int output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO teste (nome) VALUES (@snome)
SELECT @nid = SCOPE_IDENTITY()
END
GO
CREATE FUNCTION insTeste
(
@snome varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @nid int
EXECUTE finsTeste @snome, @nid output
RETURN @nid;
END
GOAnd then in the asp.net page I run a ExecuteScalar(StoredProcedure, "insTeste", params);
Thanks, Dirso
Why do you need to create a UDF? You can get the value that you need directly from the SP.
-
Why do you need to create a UDF? You can get the value that you need directly from the SP.