Calling a Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling a Stored Proc

Author

Topic

omsec
Starting Member

4 Posts

Posted - 2006-12-23 : 18:00:14

Hello
I got SQL-Server 2005 Express to try out some thing with Stored Procedures and Triggers. SO far, i wrote two Stored Procedures. One to insert a record and one to generate a logical ID (a random number of type int)

The idea is, that the add-SP should could the Get_ID-SP and then do the INSERT INTO...but I'm having syntactical problems. Here's my code:


ALTER PROCEDURE [dbo].[cmif_Add]
/* Eingabe-Parameter */
@Mandant int,
@CUser int, 
@CDate datetime, 
/* Ausgabe-Parameter */
@RecID int output
/* Nutzdaten */
@Daten varchar(50)
AS
declare @Status int /* = 0 ? */
declare @AppID int
select @AppID = execute Get_AppID(@Mandant, 1)
insert into cmif
(Mandant, AppID, RecVer, CUser, CDate, Daten)
values
(@Mandant, 101, @AppID, @CUser, @CDate, @Daten)
select @RecID = @@Identity

and the ID-Generator:


ALTER PROCEDURE [dbo].[Get_AppID]
/* Eingabe-Parameter */
@Mandant int,
@Tabelle int
AS
declare @newID int; /* = 0 ? */
select @newID = rand() * 1000000000;

while exists 
	(select AppID 
	from AppIDs
	where Mandant = @Mandant and Tabelle = @Tabelle)
select @newID = rand() * 1000000000;
return @newID;

How would I do this call right ?

Thank You - Roger

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-23 : 18:26:37

If would be helpful if you explained axactly what problem you are having that you want help with.

CODO ERGO SUM

omsec
Starting Member

4 Posts

Posted - 2006-12-23 : 18:36:01

OK. How can I call a Stored Procedure from another Stored Procedure ?

ALTER PROCEDURE [dbo].[cmif_Add]
/* Eingabe-Parameter */
@Mandant int,
@CUser int, 
@CDate datetime, 
/* Ausgabe-Parameter */
@RecID int output
/* Nutzdaten */
@Daten varchar(50)
AS
declare @Status int /* = 0 ? */
declare @AppID int
select @AppID = execute Get_AppID(@Mandant, 1)
insert into cmif
(Mandant, AppID, RecVer, CUser, CDate, Daten)
values
(@Mandant, 101, @AppID, @CUser, @CDate, @Daten)
select @RecID = @@Identity

I tried several variations of the bold line - but I always failed...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-23 : 19:03:48

your line wants to be:

exec @AppID = Get_AppID(@Mandant, 1)

see this article:

http://www.sqlteam.com/item.asp?ItemID=2644

www.elsasoft.org

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-23 : 22:58:50

exec your_SP @Your_variables_go_here

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-24 : 01:52:39

First of all make sure that the procedure Get_AppID has an output parameter.

Secondly you can not call the procedure using the syntax
~~execute Get_AppID(@Mandant, 1)~~

The Syntax should be like this


Exec Get_AppID @Mandant, 1,@var = @AppID output  

Here @var is the Variable name which you should defined in the procedure Get_AppID

Chirag

http://chirikworld.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-24 : 09:25:49

that's if using output params. I had assumed the OP wanted to get the return value of the sproc, which it seems they are trying to do from their code.

www.elsasoft.org

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 02:41:15

quote:
Originally posted by jezemine

that's if using output params. I had assumed the OP wanted to get the return value of the sproc, which it seems they are trying to do from their code.

www.elsasoft.org

I am not sure whether you can call the procedure in this way in SQL Server 2005, Can we?


exec @AppID = Get_AppID(@Mandant, 1)

Then other way which i use to do in SQL Server 2000, is take the output of the Sp, dump it into the Temp table and then work on that temp table.

I tried doing the following stuff on SQL SERVER 2005, but it didnt work for me, Correct me if i am missing somthing.


Create Proc Test( @i int)
As 
Select @i
GO 
Declare @Test int
Exec @Test = Test(1)
Select Test

I got Following Error 
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '1'.

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources