June 14, 2006 at 2:57 pm
I have a stored procedure and it selects data from a table by some conditions.
CREATE PROCEDURE ttt_get_names
@condition varchar(10)
AS
SET NOCOUNT ON
IF @condition='test'
BEGIN
SELECT * FROM emp WHERE dept_id=30
END
ELSE IF @condition='test1'
BEGIN
SELECT * FROM emp WHERE dept_id=40
END
ELSE
BEGIN
SELECT * FROM emp
END
GO
I wanted to select the result set into a temp table at the end of the procedure. How can I do that?
Thanks.
June 14, 2006 at 3:24 pm
Do you mean by calling the procedure? Then this will work:
drop PROCEDURE ttt_get_names
go
CREATE PROCEDURE ttt_get_names
@condition varchar(10)
AS
SET NOCOUNT ON
IF @condition='test'
BEGIN
SELECT name, id, xtype
FROM sysobjects WHERE id=4
END
ELSE IF @condition='test1'
BEGIN
SELECT name, id, xtype
FROM sysobjects WHERE id=5
END
ELSE
BEGIN
SELECT name, id, xtype
FROM sysobjects
END
GO
create table #A(col1 sysname, col2 int, col3 char(2))
insert into #A exec ttt_get_names NULL
select * from #A
go
drop table #A
go
June 14, 2006 at 3:26 pm
Or do you mean within the procedure?
drop PROCEDURE ttt_get_names
go
CREATE PROCEDURE ttt_get_names
@condition varchar(10)
AS
SET NOCOUNT ON
create table #A(col1 sysname, col2 int, col3 char(2))
IF @condition='test'
BEGIN
INSERT #A
SELECT name, id, xtype
FROM sysobjects WHERE id=4
END
ELSE IF @condition='test1'
BEGIN
INSERT #A
SELECT name, id, xtype
FROM sysobjects WHERE id=5
END
ELSE
BEGIN
INSERT #A
SELECT name, id, xtype
FROM sysobjects
END
SELECT * FROM #A
GO
exec ttt_get_names NULL
go
June 14, 2006 at 5:58 pm
Must be set up in database:
Create Table dbo.Condition (
Condition nvarchar (250),
EmpId int
)
INSERT INTO dbo.Condition (Condition, EmpId)
SELECT 'test', 30
UNION
SELECT 'test1', 40
SP:
IF NULLIF(@condition, '') IS NULL
SET @condition = '%'
SELECT E.*
INTO #Emp
FROM emp E
INNER JOIN dbo.Condition C ON E.Id = C.EmpId
WHERE Condition like @condition
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply