June 28, 2016 at 12:20 pm
I want a simple result set from a stored procedure to display in RS.
So when my SP runs
SELECT JOB_NUM, STATE FROM JOBS WHERE STATE_CODE IN ('CA', 'NY')
The rows display as a report.
Do I need to code OUTPUT from the SP somehow ? Or assign variable names to the SP for RS to expect ??
June 28, 2016 at 12:50 pm
homebrew01 (6/28/2016)
I want a simple result set from a stored procedure to display in RS.So when my SP runs
SELECT JOB_NUM, STATE FROM JOBS WHERE STATE_CODE IN ('CA', 'NY')
The rows display as a report.
Do I need to code OUTPUT from the SP somehow ? Or assign variable names to the SP for RS to expect ??
If by resultset you mean a defined table, no a Stored Procedure cannot return a table, unless its a CLR procedure call (Im not 100% sure on that CLR statement but I do know CLR gets around several Stored Proc limitations)
If by resultset you mean return the results of a SELECT statement, yes. A Stored Proc will return a resultset for each SELECT statement within it.
The better question to ask is why would you want to do this for data you want to display in RS? Just create the query for your RS object, bind it, simple, easy, done!
June 28, 2016 at 1:26 pm
The stored procedure takes input parameters from a user,
truncates a work table,
runs 3-4 queries to re-populate the work table.
Then I want to select 3 columns of data from that work table and display to the user. Could be a few hundred rows of data.
(It's safe to assume I don't know what the heck I'm doing)
June 28, 2016 at 2:23 pm
Here's a quick (and very ugly) example... hope it helps! Here's the structure of my source table:
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[enrollOK] [bit] NULL,
[ECOG] [tinyint] NULL,
[Histology] [tinyint] NULL,
[LeaveDate] [datetime] NULL,
CONSTRAINT [PK_Enroll] PRIMARY KEY CLUSTERED
(
[enrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROC GetSomeEnrolls
@AfterDate DATE
AS
BEGIN
SET NOCOUNT ON;
-- empty table
TRUNCATE TABLE #TempData;
-- populate
SELECT EnrollmentID, e_PatientID, e_ProtocolNo, enrollDate
INTO #TempData
FROM dbo.Enroll
WHERE dbo.Enroll.enrollDate>@AfterDate;
-- return some rows (a single protocol's data)
SELECT EnrollmentID
, e_PatientID AS PatientID
, e_ProtocolNo AS Protocol
, enrollDate
FROM #TempData
WHERE e_ProtocolNo = 'LUN90';
END
INSERT INTO #TempData(EnrollmentID,e_PatientID, e_ProtocolNo, EnrollDate)
Exec GetSomeEnrolls @AfterDate = '01-Jan-2011';
SELECT * FROM #TempData;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply