July 9, 2010 at 10:26 am
Hi everyone,
I have a small question regarding stored procedure result set.
let' say i have procedure returning these values:
ID Name Job
1 john engineer
2 john manager
3 chris help desk manager
ID column is the only unique value here .
Now I want to use this ID column to call another procedure(exec procedurename @ID) .how am i going to do that...
July 9, 2010 at 10:32 am
Execute the Stored procedure for each ID present in the output of the other SP?
July 9, 2010 at 10:45 am
I just gave an example ,but this is what it is doing exactly:
the current stored procedure is inserting the rows depending on the input parameters and returning only one row at a time
ex:
ID Name Job
34 joy Designer
Now how should i call ID 34 in another procedure .
July 9, 2010 at 11:42 am
Put the result of your SP into a temp table like this:
INSERT INTO TABLE EXEC SP1
Then use column values into variables and the execute SP2..
July 9, 2010 at 12:59 pm
Thank you.
July 26, 2010 at 5:28 am
Unfortunately, there is no built-in support for arrays in SQL Server's T-SQL.
We cannot create arrays of variables or input parameters or columns in T-SQL.. 😀
Workaround:- Write a proc (dynamic sql) that can call list of values seperated by commas, as an input parameter & exec.
Example:
CREATE PROC dbo.sample
(
@input_var varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT Col1,Col2,Col3
FROM dbo.table_name
WHERE Col1 IN (' + @input_var + ')'
EXEC(@SQL)
END
GO
GRANT EXEC ON dbo.sample TO WebUser
GO
GRANT SELECT ON dbo.table_name TO WebUser
GO
August 12, 2010 at 6:58 am
If the first proc is truly retruning only one row then make ID an output param and capture it in a variable then execute the second proc using that variable.
e.g.
DECLARE @id int
EXECUTE firstproc @param,@id OUTPUT
EXECUTE secondproc @id
Far away is close at hand in the images of elsewhere.
Anon.
August 13, 2010 at 8:20 pm
Another way to solve this problem is to create a temp table in the first procedure. The second procedure looks for the existence of the temp table and adds the records to it if the table exists. After the call to the second procedure, the first procedure will have access to the values in the temp table.
CREATE -- DROP
PROC ProcTwo
AS
SET NOCOUNT ON
DECLARE @Jobs TABLE
(
ID INT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Job VARCHAR(20) NOT NULL
)
INSERT
INTO @Jobs
(ID, Name, Job)
SELECT 1, 'John', 'Enginee' UNION
SELECT 2, 'John', 'Manager' UNION
SELECT 3, 'Chris', 'Help Desk Manager'
IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
INSERT
INTO #TempTable
(ID, Name, Job)
SELECT ID, Name, Job
FROM @Jobs
ELSE
BEGIN
SET NOCOUNT OFF
SELECT ID, Name, Job
FROM @Jobs
END
GO
CREATE -- DROP
PROC ProcOne
AS
CREATE TABLE #TempTable
(
ID INT NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Job VARCHAR(20) NOT NULL
)
EXEC ProcTwo
SELECT Job, Name, ID
FROM #TempTable
GO
EXEC ProcTwo
EXEC ProcOne
Executing ProcTwo will show the values in ID, Name, Job order because the #TempTable does not exist. Executing ProcOne will allow ProcOne to use the values, but show them in Job, Name, ID order because #TempTable does exist.
If I understand what you are asking how to do, ProcOne would add records to #TempTable and ProcTwo would use those values in its processing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply