September 20, 2011 at 2:22 pm
hi
i want to stored one sp result into another stored procedure in variable ,how to do that?
September 20, 2011 at 2:26 pm
create table #tmp (output list from sp)
INSERT into #tmp (list of columns)
exec dbo.spname
September 20, 2011 at 2:26 pm
you have to create a table that mirros teh output of the proc you want to capture...
here's a basic example, just grabbing sp_who2 results:
CREATE PROCEDURE PR_CAPTURESP_WHO
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WHORESULTS') AND xtype in (N'U'))
CREATE TABLE WHORESULTS (
[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL
)
--table exists, insert some data
INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM WHORESULTS WHERE SPIDINT < 50
END
Lowell
September 20, 2011 at 3:23 pm
And don't add an accidental space into Lowells naming convention. (WHORE SULTS). :w00t:
Which when I first read it i transposed the U and L in the second word....sheesh!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2011 at 3:25 pm
Sean Lange (9/20/2011)
And don't add an accidental space into Lowells naming convention. (WHORE SULTS). :w00t:Which when I first read it i transposed the U and L in the second word....sheesh!!!
Same here :-D.
September 20, 2011 at 7:37 pm
Ninja's_RGR'us (9/20/2011)
Sean Lange (9/20/2011)
And don't add an accidental space into Lowells naming convention. (WHORE SULTS). :w00t:Which when I first read it i transposed the U and L in the second word....sheesh!!!
Same here :-D.
sheesh, mind in the gutters...i know what sites are in your browser histories now for both you two!
Lowell
September 20, 2011 at 7:40 pm
You may also want to look up table valued parameters
http://msdn.microsoft.com/en-us/library/bb510489.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 21, 2011 at 3:44 am
/* create table script*/
CREATE TABLE [dbo].[Products]
(
[ProductID] [tinyint] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](20) NULL,
[RecommendedPrice] [money] NULL,
[Category] [varchar](10) NULL,
)
/*creating the procedure*/
Create procedure sp_get_products
as
BEGIn
Select * from products
END
/*creating the variable and storing the sp related output in the table variable*/
Create procedure sp_get_productname
as
BEGIN
Declare @tab1 table
(
ProductID int
,ProductName varchar(100)
,RecommendedPrice money
,Category varchar(100)
)
insert into @tab1
Exec sp_get_products
Select ProductName from @tab1
END
/*execute the stored procedure*/
Exec sp_get_productname
September 22, 2011 at 4:28 am
if the result is just a scalar value (or a few scalar values) you can pass values back to a calling proc by declaring parameters as OUTPUT (note that the parameter needs to be declared as an OUTPUT parameter in both the proc definition and the call to that proc.
create proc #scalar_result (@a int OUTPUT)
as
begin;
select @a = 1;
end;
go
create proc #use_result
as
begin ;
declare @b-2 int;
exec #scalar_result @b-2 OUTPUT;
select @b-2;
end;
GO
exec #use_result;
September 22, 2011 at 3:35 pm
I'd typically use a temp table to pass a bunch of results to another sp, the way Ninja's_RGR'us suggested. But if you're stuck on passing the data in as a parameter to the second procedure (I assume that's what you mean by "variable"), you could pass the entire result set into the second sp using XML, though that's kind of a pain.
September 22, 2011 at 3:38 pm
notoriousdba (9/22/2011)
I'd typically use a temp table to pass a bunch of results to another sp, the way Ninja's_RGR'us suggested. But if you're stuck on passing the data in as a parameter to the second procedure (I assume that's what you mean by "variable"), you could pass the entire result set into the second sp using XML, though that's kind of a pain.
and it is more costly performance wise than using a TVP for instance...
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply