call store procedure from another store procedure

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]

AS

BEGIN

SET NOCOUNT ON;

DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

Hi,

Use the OUTPUT parameter in your stored procedure and return this in one variable.

Regards,

|||

First, drop the "values" statement from your SQL. It should be

insert into table(col1 datatype, col2 datatype)

exec sproc 'param'

The values statement is for inserting one row at a time with specified values, if I am not mistaken.

Also, do the columns in your sproc match those in your table?

By the way, instead of dropping and recreating a table, why not truncate it:

ALTER PROCEDURE [dbo].[test]

AS

BEGIN

SET NOCOUNT ON;

TRUNCATE TABLE tbl1

INSERT INTO tbl1 (first_name,last_name)

exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen'

END

|||

INSERT INTO tbl1 (first_name,last_name)

exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen'

It would be great, having that supported by MSSQL, but, unfortunately, it's not so. Destination table should correspond the dataset returned by sp, except, maybe, identity columns. In some cases it is possible to use table function instead, but there are a lot of restictions.

|||

Are you saying that the statement in your email won't work? You can specify columns:

create table test
(
testId int identity,
value1 varchar(10) null,
value2 varchar(10) null,
value3 varchar(10) null
)
go
create procedure test$return
as
select '1','2'
go

insert into test (value1, value2)
exec test$return
insert into test (value2, value3)
exec test$return

go
select *
from test
go

testId value1 value2 value3
-- - - -
1 1 2 NULL
2 NULL 1 2

|||

Let's say @.res1 is output parameter and I want to save the query result in it. If i do like this, it doesn't work:

set @.re1 = SELECT SUM(dbo.InvHead.TotalToPay)

FROM dbo.InvHead INNER JOIN

dbo.StationsForUsers ON dbo.InvHead.StationNum = dbo.StationsForUsers.StationNum

WHERE ( (dbo.InvHead.invDate>= @.FromDate) AND (dbo.InvHead.invDate<= +@.ToDate) AND

(dbo.StationsForUsers.UserName =@.User_Name ))

How can I save the query result in other way?

THANKS!

|||

Hi Moria,

Try this:

SELECT @.re1 = SUM(dbo.InvHead.TotalToPay)

FROM dbo.InvHead INNER JOIN

dbo.StationsForUsers ON dbo.InvHead.StationNum = dbo.StationsForUsers.StationNum

WHERE ( (dbo.InvHead.invDate>= @.FromDate) AND (dbo.InvHead.invDate<= +@.ToDate) AND

(dbo.StationsForUsers.UserName =@.User_Name ))

Regards,

|||

He told me error:

"Incorrect syntax near the keyword 'SELECT'

anybody know why?