sp help syntax - 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 2005 Forums

SQL Server Administration (2005)

sp help syntax

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 08:47:58

i have a stored procedure called
spcreatebatch which returns a number

how can I create a new batch from within another stored procedure

will set @batch=spcreatebatch return the result of spcreatebatch in @batch?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-07 : 09:42:08

create #temp(batchNum int)
insert into #temp
exec spcreatebatch

select batchNum from #temp

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 09:48:10

i need to do all this just to get the batch number (waht's returned from spcreatebatch) there's no faster way of doing?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-07 : 10:00:55

you can use an output parameter...
but that would mean you have to change your spcreatebatch sproc

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 10:06:15

can you explain - i can chagne that
i just want to make this as simple as possible and your code above did not seem to work

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 10:09:18

If your SP returns Scalar value through return statement, you can catch it within EXECUTE statement itself.

create proc test
as
return 1
go

declare @a int
exec @a = test
select @a

Otherwise, you have no option but to change your SP to use Output parameter or insert results of SP to temp table as spirit1 suggested.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 10:16:41

thanks i'll try this -- this should work as i do return the batch id

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 10:19:51

this didn't work thought

my code for createbatch is (am i doing somethign wrong here -- if i run it - it returns the next batch number)

CREATE PROCEDURE [dbo].[spcreatebatch]

AS
BEGIN
declare @id int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

insert into batches(mydate) values(getdate())
set @id=@@IDENTITY

SELECT @id AS [id]
return

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 10:23:46

[code]CREATE PROCEDURE [dbo].[spcreatebatch]
( @out int output)
AS
~~BEGIN~~
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into batches(mydate) values(getdate())
set @out = @@IDENTITY
GO

declare @i int
exec [dbo].[spcreatebatch] @i output
select @i[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-07 : 10:38:04

also use scope_identity() instead of @@identity

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 10:49:03

alter PROCEDURE [dbo].[spcreatebatch]
(@out int output)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into batches(mydate) values(getdate())
set @out =scope_identity()
GO

i get an error Msg 102, Level 15, State 1, Procedure spcreatebatch, Line 9
Incorrect syntax near ')'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 11:01:05

quote:
Originally posted by esthera

alter PROCEDURE [dbo].[spcreatebatch]
(@out int output)
AS
~~BEGIN~~
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into batches(mydate) values(getdate())
set @out =scope_identity()
GO

i get an error Msg 102, Level 15, State 1, Procedure spcreatebatch, Line 9
Incorrect syntax near ')'.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources