I have a stored procedure (say sp_1) that returns a number.
A simplified form of sp_1 could be
Create procedure sp_1 as
Select 1
Go
Now, I would like to call sp_1 from another sp (say sp_2)
and store the value in a variable. I tried this
Create procedure sp_2 as
Declare @.xyz as integer
Set @.xyz = (exec sp_1)
This does not work.
Could somebody point me the mistake?
Thanks,
RamThis is a multi-part message in MIME format.
--=_NextPart_000_01C5_01C3CDFB.55950220
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Try;
create proc sp1
( @.parm int output)
as
select @.parm = 1
go
create proc sp2
as
declare @.p as int
exec sp1 @.p output
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:04b401c3ce22$4b1c9480$a001280a@.phx.gbl...
Hi,
I have a stored procedure (say sp_1) that returns a number.
A simplified form of sp_1 could be
Create procedure sp_1 as
Select 1
Go
Now, I would like to call sp_1 from another sp (say sp_2)
and store the value in a variable. I tried this
Create procedure sp_2 as
Declare @.xyz as integer
Set @.xyz = (exec sp_1)
This does not work.
Could somebody point me the mistake?
Thanks,
Ram
--=_NextPart_000_01C5_01C3CDFB.55950220
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try;
create proc sp1
( @.parm int =output)
as
select @.parm =3D 1
go
create proc sp2
as
declare @.p as int
exec sp1 @.p output
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Ram" wrote in message news:04b401c3ce22$4b=1c9480$a001280a@.phx.gbl...Hi,I have a stored procedure (say sp_1) that returns a number.A =simplified form of sp_1 could beCreate procedure sp_1 asSelect =1GoNow, I would like to call sp_1 from another sp (say sp_2) and store the =value in a variable. I tried thisCreate procedure sp_2 as Declare @.xyz =as integerSet @.xyz =3D (exec sp_1)This does not =work.Could somebody point me the =mistake?Thanks,Ram
--=_NextPart_000_01C5_01C3CDFB.55950220--|||Hi,
Create a Linked server from server 2 and then call the SP_1
Thanks
Hari
MCDBA
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:04b401c3ce22$4b1c9480$a001280a@.phx.gbl...
> Hi,
> I have a stored procedure (say sp_1) that returns a number.
> A simplified form of sp_1 could be
> Create procedure sp_1 as
> Select 1
> Go
> Now, I would like to call sp_1 from another sp (say sp_2)
> and store the value in a variable. I tried this
> Create procedure sp_2 as
> Declare @.xyz as integer
> Set @.xyz = (exec sp_1)
> This does not work.
> Could somebody point me the mistake?
>
> Thanks,
> Ram
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01F3_01C3CDFC.4D684E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Why would you need a linked server for this?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#aYouVizDHA.2308@.TK2MSFTNGP11.phx.gbl...
Hi,
Create a Linked server from server 2 and then call the SP_1
Thanks
Hari
MCDBA
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:04b401c3ce22$4b1c9480$a001280a@.phx.gbl...
> Hi,
> I have a stored procedure (say sp_1) that returns a number.
> A simplified form of sp_1 could be
> Create procedure sp_1 as
> Select 1
> Go
> Now, I would like to call sp_1 from another sp (say sp_2)
> and store the value in a variable. I tried this
> Create procedure sp_2 as
> Declare @.xyz as integer
> Set @.xyz = (exec sp_1)
> This does not work.
> Could somebody point me the mistake?
>
> Thanks,
> Ram
>
--=_NextPart_000_01F3_01C3CDFC.4D684E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Why would you need a linked server for =this?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Hari Prasad"
--=_NextPart_000_01F3_01C3CDFC.4D684E80--|||Hi,
You can use output parameter.
create procedure sp_1 @.x int output
as
select @.x=1
go
-- executing above sp.
declare @.xx int
exec sp_1 @.xx output
print @.xx
--
- Vishal|||This is a multi-part message in MIME format.
--=_NextPart_000_00B2_01C3CE55.AA81C840
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Sorry Tom, you are correct, I misunderstood SP2 as server 2.
Thanks
Hari
MCDBA
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#CBH8YizDHA.1744@.TK2MSFTNGP12.phx.gbl...
Why would you need a linked server for this?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message =news:#aYouVizDHA.2308@.TK2MSFTNGP11.phx.gbl...
Hi,
Create a Linked server from server 2 and then call the SP_1
Thanks
Hari
MCDBA
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:04b401c3ce22$4b1c9480$a001280a@.phx.gbl...
> Hi,
> I have a stored procedure (say sp_1) that returns a number.
> A simplified form of sp_1 could be
>
> Create procedure sp_1 as
> Select 1
> Go
>
> Now, I would like to call sp_1 from another sp (say sp_2)
> and store the value in a variable. I tried this
>
> Create procedure sp_2 as
> Declare @.xyz as integer
> Set @.xyz =3D (exec sp_1)
>
> This does not work.
>
> Could somebody point me the mistake?
>
>
> Thanks,
> Ram
>
--=_NextPart_000_00B2_01C3CE55.AA81C840
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Sorry Tom, you are correct, I =misunderstood SP2 as server 2.
Thanks
Hari
MCDBA
"Tom Moreau"
Why would you need a linked server =for this?
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Hari Prasad"
--=_NextPart_000_00B2_01C3CE55.AA81C840--|||This will not work
> Create procedure sp_2 as
> Declare @.xyz as integer
> Set @.xyz = (exec sp_1)
because the sproc sp_1 will get you the return value.
You should always have a
RETURN some_integer
at the end of each sproc to inform the caller about the status.
so
> Create procedure sp_1 as
> Select 1
> RETURN 20 << added this
> Go
Then
> Declare @.xyz as integer
> EXEC @.xyz = (exec sp_1)
@.xyz will be 20 and not 1.
There is another way besides output params you can use to store the
resultset from the sproc:
CREATE PROC sp_1
AS
SELECT 'ABCD', 10
UNION
SELECT 'EFGH', 20
RETURN @.@.ERROR
GO
CREATE TABLE TAB1 (F1 VARCHAR(10), F2 INT)
GO
-- **************************
INSERT INTO TAB1
EXEC sp_1
GO
-- **************************
SELECT * FROM TAB1
GO
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:04b401c3ce22$4b1c9480$a001280a@.phx.gbl...
> Hi,
> I have a stored procedure (say sp_1) that returns a number.
> A simplified form of sp_1 could be
> Create procedure sp_1 as
> Select 1
> Go
> Now, I would like to call sp_1 from another sp (say sp_2)
> and store the value in a variable. I tried this
> Create procedure sp_2 as
> Declare @.xyz as integer
> Set @.xyz = (exec sp_1)
> This does not work.
> Could somebody point me the mistake?
>
> Thanks,
> Ram
>|||Thanks for the solutions Daniel, Tom and Vishal. It worked.
Ram
>--Original Message--
> Hi,
> You can use output parameter.
> create procedure sp_1 @.x int output
> as
> select @.x=1
> go
> -- executing above sp.
> declare @.xx int
> exec sp_1 @.xx output
> print @.xx
> --
> - Vishal
>
>.
>sql
No comments:
Post a Comment