November 4, 2008 at 9:05 am
Hi everybody,
i'm a beginner with sql server 2005
i'd like to call a store procedure into a other !
the first store procedure return the @@identity (it"s a simple insert)
the first store procedure
LTER PROCEDURE [dbo].[TestStoreProcedure]
AS
INSERT INTO TPersonnes(Nom, Prenom) VALUES('tot', '')
print @@IDENTITY
return @@IDENTITY
the second store procedure who call the first
LTER PROCEDURE [dbo].[TestCallOtherProcedure]
@valeur int
as
set @valeur = exec TestStoreProcedure
but that doesn't work and i can't find a simple sample !
can you help me !?
Thanks for your knowledge
Christophe
November 4, 2008 at 9:12 am
Hi Christophe,
Look at output parameters.... here's a brief example:
ALTER PROCEDURE [dbo].[TestStoreProcedure]
(@my_param int output)
AS
INSERT INTO TPersonnes(Nom, Prenom) VALUES('tot', '')
set @my_param = @@identity
Then...
ALTER PROCEDURE [dbo].[TestCallOtherProcedure]
as
declare @valeur int
exec TestStoreProcedure @my_param = @valeur output
select @valeur
November 4, 2008 at 2:08 pm
Use ident_current(table_name) instead of @@identity.
@@identity gives the last inserted identity value across the server.
November 4, 2008 at 3:28 pm
Actually, even better, use the SCOPE_IDENTITY() function, it will only reference the last identity value created within your stored procedure in the current session.
@@Identity is current session but any scope
IDENT_CURRENT() is any session any scope
November 5, 2008 at 1:06 am
Hi guys,
thanks you for your knowledge...that's exactly what i want !
thanks for all
Christophe
November 5, 2008 at 7:35 am
Another option would be to have the top level procedure call a function rather than a procedure. Functions can return not just single values, but also tables.
In the end, the final decision depends on your needs and which tool does the job with the least amount of database processing.
The more you are prepared, the less you need it.
November 5, 2008 at 7:39 am
Hi Andrew Peterson,
thanks for your information !
is it possible to have a little sample about a function who returns a table !?
I'm a beginner of sql server a really !
Thanks for your knowledge
Christophe
November 5, 2008 at 9:49 am
here is a good example, and btw, this function is very useful. It converts strings to a table
-----------------------------------
-- call it
declare @strPermissionName nvarchar(4000)
select @strPermissionName = N'dkljdflaj,kdkkd,dkjajlf,'
select * from dbo.fnNStringToArray(@strPermissionName, N',')
----------------------------------------------
CREATE
--alter
FUNCTION dbo.fnNStringToArray
(
@strList nvarchar(4000),
@strDelim nvarchar(10)
)
RETURNS
@tblParsedList table
(
rowId int IDENTITY(1,1) NOT NULL
,ListItem nvarchar(255)
)
AS
BEGIN
DECLARE @strListItem nvarchar(255)
DECLARE @intPos int
-- Ensure we have a trailing delimiter
IF RIGHT(@strList,LEN(@strDelim)) <> @strDelim
SET @strList = LTRIM(RTRIM(@strList))+ @strDelim
SET @intPos = CHARINDEX(@strDelim, @strList, 1)
IF REPLACE(@strList, @strDelim, N'') <> N''
BEGIN
-- Break up the string
WHILE @intPos > 0
BEGIN
SET @strListItem = LTRIM(RTRIM(LEFT(@strList, @intPos - 1)))
IF @strListItem <> N''
INSERT INTO @tblParsedList (ListItem) VALUES (@strListItem)
SET @strList = RIGHT(@strList, LEN(@strList) - @intPos - LEN(@strDelim) + 1)
SET @intPos = CHARINDEX(@strDelim, @strList, 1)
END
END
RETURN
END
The more you are prepared, the less you need it.
November 5, 2008 at 2:05 pm
Andrew Peterson (11/5/2008)
Another option would be to have the top level procedure call a function rather than a procedure. Functions can return not just single values, but also tables.In the end, the final decision depends on your needs and which tool does the job with the least amount of database processing.
The only problem is that you can't modify anything within a function, so returning the identity of something you've just created/inserted can't be done with a function, since you can't insert through a function.
----------------------------------------------------------------------------------
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?
November 5, 2008 at 2:09 pm
Andrew Peterson (11/5/2008)
here is a good example, and btw, this function is very useful. It converts strings to a table
Andrew -
A loop is a rather slow way to do that - you should look at Jeff Moden's article on using a tally table. Will blow the doors right off of any loop solution doing this.
Article is found here:
http://qa.sqlservercentral.com/articles/TSQL/62867/[/url]
----------------------------------------------------------------------------------
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?
November 6, 2008 at 7:50 am
Thanks for the tip. Always looking for new approaches.
But what I am really starting to look is a way to have an application pass in an XML value, and then convert that to a table.
The more you are prepared, the less you need it.
November 6, 2008 at 7:54 am
Agree that functions have some limits, but the use here is to simply repackage.
The more you are prepared, the less you need it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply