July 14, 2009 at 3:16 am
I'm looking for a generic template to create a stored procedure that contains an INSERT INTO command with all fields of the table, and returns with scope_identity.
I want something like Stored Procedure Wizard at MSSQL 2000.
Thanks for your help.
July 14, 2009 at 3:26 am
Have you looked at the templates in 2005's management studio? Ctrl-Alt-T to open template explorer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2009 at 3:30 am
I have but i havent found.
i want something like this:
CREATE PROCEDURE [insert_Bejovok_1]
(@BejovoTipus_1 [int],
@BejovoSorSzam_2 [varchar](50),
@BejovoDatum_3 [datetime],
@BejovoSzallito_4 [int],
@BejovoFizmod_5 [varchar](50),
@BejovoFizHat_6 [datetime],
@BejovoKiegyenlites_7 [datetime],
@BejovoDeviza_8 [int],
@BejovoBelsoSorszam_9 [varchar](50))
AS INSERT INTO [express_szamla].[dbo].[Bejovok]
( [BejovoTipus],
[BejovoSorSzam],
[BejovoDatum],
[BejovoSzallito],
[BejovoFizmod],
[BejovoFizHat],
[BejovoKiegyenlites],
[BejovoDeviza],
[BejovoBelsoSorszam])
VALUES
( @BejovoTipus_1,
@BejovoSorSzam_2,
@BejovoDatum_3,
@BejovoSzallito_4,
@BejovoFizmod_5,
@BejovoFizHat_6,
@BejovoKiegyenlites_7,
@BejovoDeviza_8,
@BejovoBelsoSorszam_9)
RETURN SCOPE_IDENTITY()
GO
July 14, 2009 at 7:32 am
You need to define an output parameter to pass the identifier back out. Something like:
CREATE PROCEDURE [insert_Bejovok_1]
(@BejovoTipus_1 [int],
@BejovoSorSzam_2 [varchar](50),
@BejovoDatum_3 [datetime],
@BejovoSzallito_4 [int],
@BejovoFizmod_5 [varchar](50),
@BejovoFizHat_6 [datetime],
@BejovoKiegyenlites_7 [datetime],
@BejovoDeviza_8 [int],
@BejovoBelsoSorszam_9 [varchar](50),
@ReturnID integer OUTPUT)
AS
BEGIN
INSERT INTO [express_szamla].[dbo].[Bejovok]
( [BejovoTipus],
[BejovoSorSzam],
[BejovoDatum],
[BejovoSzallito],
[BejovoFizmod],
[BejovoFizHat],
[BejovoKiegyenlites],
[BejovoDeviza],
[BejovoBelsoSorszam])
VALUES
( @BejovoTipus_1,
@BejovoSorSzam_2,
@BejovoDatum_3,
@BejovoSzallito_4,
@BejovoFizmod_5,
@BejovoFizHat_6,
@BejovoKiegyenlites_7,
@BejovoDeviza_8,
@BejovoBelsoSorszam_9,
)
set @returnID = SCOPE_IDENTITY()
END
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply