Set parameter inside SELECT stored procedure

microsoft.public.sqlserver.programming

26 Aug 2005 1:29 PM

Assimalyst

Hi,

I have a stored procedure that produces a table:

SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
FROM tblPatient, tblPDT
WHERE....
ORDER BY.....

I now need to do some calculations on the genearated table. things like
count all rows, count the number of different pntCode's, sum Survival
etc.

I have been attempting to fit statements like SET @totalRows = .. in as
follows

SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
SET @totalRows = COUNT(*) AS totalRows
SET @totalCodes = COUNT(tblPatient.pntCode) AS totalCodes
FROM tblPatient, tblPDT
WHERE....
ORDER BY.....
RETURN @totalRows
RETURN @totalCodes

But i get an error incorrect syntax near the keyword AS. I've tried a
few other syntax possibilities too but keep getting errors.

Any ideas on a good solution?

Thanks

26 Aug 2005 1:49 PM

GetGoing

hi,

dont use set within a SELECT statement..

try..

declare @totalrows as int
select @totalrows = count(*) from tblPatient

keep going

Show quote

"Assimalyst" wrote:

> Hi,
>
> I have a stored procedure that produces a table:
>
> SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
> FROM tblPatient, tblPDT
> WHERE....
> ORDER BY.....
>
> I now need to do some calculations on the genearated table. things like
> count all rows, count the number of different pntCode's, sum Survival
> etc.
>
> I have been attempting to fit statements like SET @totalRows = .. in as
> follows
>
> SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
> SET @totalRows = COUNT(*) AS totalRows
> SET @totalCodes = COUNT(tblPatient.pntCode) AS totalCodes
> FROM tblPatient, tblPDT0
> WHERE....
> ORDER BY.....
> RETURN @totalRows
> RETURN @totalCodes
>
> But i get an error incorrect syntax near the keyword AS. I've tried a
> few other syntax possibilities too but keep getting errors.
>
> Any ideas on a good solution?
>
> Thanks
>
>

26 Aug 2005 1:59 PM

Alejandro Mesa

Do you want to return also the result set to the client application or just
the calculation?.

You can use a temporary table to hold the result set, do the calculation
using the temp table a them return the rows in it. You can return the
calculation using output parameters. Leave the "return" to indicate if the sp
failed or not.

@totalRows int output,
@totalCodes int output,
as
....
create table #t (c1 ...)

insert into #t
SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
FROM tblPatient, tblPDT
WHERE....

select
   @totalRows = COUNT(*),
   @totalCodes = COUNT(distinct tblPatient.pntCode)
from
   #t

select c1, ..., cn
from #t
order by ... -- better if you sort in the client side

return 0
go

AMB

Show quote

"Assimalyst" wrote:

> Hi,
>
> I have a stored procedure that produces a table:
>
> SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
> FROM tblPatient, tblPDT
> WHERE....
> ORDER BY.....
>
> I now need to do some calculations on the genearated table. things like
> count all rows, count the number of different pntCode's, sum Survival
> etc.
>
> I have been attempting to fit statements like SET @totalRows = .. in as
> follows
>
> SELECT DISTINCT tblPatient.pntCode, tblPDT.pdtDate, Survival.....
> SET @totalRows = COUNT(*) AS totalRows
> SET @totalCodes = COUNT(tblPatient.pntCode) AS totalCodes
> FROM tblPatient, tblPDT
> WHERE....
> ORDER BY.....
> RETURN @totalRows
> RETURN @totalCodes
>
> But i get an error incorrect syntax near the keyword AS. I've tried a
> few other syntax possibilities too but keep getting errors.
>
> Any ideas on a good solution?
>
> Thanks
>
>

26 Aug 2005 2:59 PM

Assimalyst

Thanks for the ideas.

Alejandro, yes i am trying to return the result set as a table and the
caculation to the client. (asp page in this case)

i've created a test stored procedure:

CREATE PROCEDURE proc_CountTest
(@totalRows int output,
@totalCodes int output
)

AS

CREATE TABLE #t (pntUnitID varchar(20), pdtDate datetime, Survival
int)

INSERT INTO #t
SELECT DISTINCT tblPatient.pntUnitID, tblPDT.pdtDate, DATEDIFF (Month ,
tblPDT.pdtDate, COALESCE(tblPatient.pntDOD, GETDATE())) as 'Survival'
FROM tblPatient, tblPDT
WHERE tblPatient.patientNo = tblPDT.patientNo

SELECT
   @totalRows = COUNT(*),
   @totalCodes = COUNT(DISTINCT #t.pntUnitID)
FROM
   #t

SELECT pntUnitID, pdtDate, Survival
FROM #t
ORDER BY #t.pntUnitID

RETURN 0
GO

And using it i am trying to load the values from @totalRows and
@totalCodes into asp labels, but get the following error:

System.NullReferenceException: Object reference not set to an instance
of an object.

When i run the debugger i find the values i am trying to input into the
labels are <undefined value>, though 4 rows are found with the select
query that i tested separately.

The c# section of code is below:

SqlParameter NoOfPDT;
NoOfPDT = cmd.Parameters.Add("@totalRows", SqlDbType.Int);
NoOfPDT.Direction = ParameterDirection.Output;
SqlParameter returnNoOfPatients;
NoOfPatients = cmd.Parameters.Add("@totalCodes", SqlDbType.Int);
NoOfPatients.Direction = ParameterDirection.Output;

// Execute reader
SqlDataReader dr = cmd.ExecuteReader();

// bind the drop down
pdtDatagrid.DataSource = dr;
pdtDatagrid.DataBind();

noOfProceduresLbl.Text = (int)returnNoOfPDT.Value;
noOfPatientsLbl.Text = (int)NoOfPatients.Value;

It errors at noOfProceduresLbl.Text = (int)returnNoOfPDT.Value;

any ideas what might be the problem?

Thanks

26 Aug 2005 3:21 PM

Alejandro Mesa

- Add "set nocount on" to the sp (first line after "as")
- sql server returns output parameters and return value in the last packet,
so you have to process and close the dr before accessing them.

See the second "sqlclient" example.

Using Stored Procedures with a Command http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

AMB

Show quote

"Assimalyst" wrote:

> Thanks for the ideas.
>
> Alejandro, yes i am trying to return the result set as a table and the
> caculation to the client. (asp page in this case)
>
> i've created a test stored procedure:
>
> CREATE PROCEDURE proc_CountTest
> (@totalRows int output,
> @totalCodes int output
> )
>
> AS
>
> CREATE TABLE #t (pntUnitID varchar(20), pdtDate datetime, Survival
> int)
>
> INSERT INTO #t
> SELECT DISTINCT tblPatient.pntUnitID, tblPDT.pdtDate, DATEDIFF (Month ,
> tblPDT.pdtDate, COALESCE(tblPatient.pntDOD, GETDATE())) as 'Survival'
> FROM tblPatient, tblPDT
> WHERE tblPatient.patientNo = tblPDT.patientNo
>
> SELECT
>    @totalRows = COUNT(*),
>    @totalCodes = COUNT(DISTINCT #t.pntUnitID)
> FROM
>    #t
>
> SELECT pntUnitID, pdtDate, Survival
> FROM #t
> ORDER BY #t.pntUnitID
>
> RETURN 0
> GO
>
> And using it i am trying to load the values from @totalRows and
> @totalCodes into asp labels, but get the following error:
>
> System.NullReferenceException: Object reference not set to an instance
> of an object.
>
> When i run the debugger i find the values i am trying to input into the
> labels are <undefined value>, though 4 rows are found with the select
> query that i tested separately.
>
> The c# section of code is below:
>
> SqlParameter NoOfPDT;
> NoOfPDT = cmd.Parameters.Add("@totalRows", SqlDbType.Int);
> NoOfPDT.Direction = ParameterDirection.Output;
> SqlParameter returnNoOfPatients;
> NoOfPatients = cmd.Parameters.Add("@totalCodes", SqlDbType.Int);
> NoOfPatients.Direction = ParameterDirection.Output;
>
> // Execute reader
> SqlDataReader dr = cmd.ExecuteReader();
>
> // bind the drop down
> pdtDatagrid.DataSource = dr;
> pdtDatagrid.DataBind();
>
> noOfProceduresLbl.Text = (int)returnNoOfPDT.Value;
> noOfPatientsLbl.Text = (int)NoOfPatients.Value;
>
> It errors at noOfProceduresLbl.Text = (int)returnNoOfPDT.Value;
>
> any ideas what might be the problem?
>
> Thanks
>
>

30 Aug 2005 12:41 PM

Assimalyst

Thank you very much. You have been a huge help. Got it working now. :)

26 Aug 2005 3:09 PM

Assimalyst

Thanks for the ideas.

Alejandro, yes i am trying to return the result set as a table and the
caculation to the client. (asp page in this case)

i've created a test stored procedure:

CREATE PROCEDURE proc_CountTest
(@totalRows int output,
@totalCodes int output
)

AS

CREATE TABLE #t (pntUnitID varchar(20), pdtDate datetime, Survival
int)

INSERT INTO #t
SELECT DISTINCT tblPatient.pntUnitID, tblPDT.pdtDate, DATEDIFF (Month ,
tblPDT.pdtDate, COALESCE(tblPatient.pntDOD, GETDATE())) as 'Survival'
FROM tblPatient, tblPDT
WHERE tblPatient.patientNo = tblPDT.patientNo

SELECT
   @totalRows = COUNT(*),
   @totalCodes = COUNT(DISTINCT #t.pntUnitID)
FROM
   #t

SELECT pntUnitID, pdtDate, Survival
FROM #t
ORDER BY #t.pntUnitID

RETURN 0
GO

And using it i am trying to load the values from @totalRows and
@totalCodes into asp labels, but get the following error:

System.NullReferenceException: Object reference not set to an instance
of an object.

When i run the debugger i find the values i am trying to input into the
labels are <undefined value>, though 4 rows are found with the select
query that i tested separately.

The c# section of code is below:
SqlParameter NoOfPDT;
NoOfPDT = cmd.Parameters.Add("@totalRows", SqlDbType.Int);
NoOfPDT.Direction = ParameterDirection.Output;
SqlParameter NoOfPatients;
NoOfPatients = cmd.Parameters.Add("@totalCodes", SqlDbType.Int);
NoOfPatients.Direction = ParameterDirection.Output;

// Execute reader
SqlDataReader dr = cmd.ExecuteReader();

// bind the drop down
pdtDatagrid.DataSource = dr;
pdtDatagrid.DataBind();

noOfProceduresLbl.Text = ((int)NoOfPDT.Value).ToString();
noOfPatientsLbl.Text = ((int)NoOfPatients.Value).ToString();

It errors at noOfProceduresLbl.Text = (int)returnNoOfPDT.Value;

any ideas what might be the problem?

Thanks