record count of the query

[yan19454]

Any way to do get the record count of the following query.

SELECT distinct h.[Full Name],h.DOB
FROM annual_edu_2006.dbo.HREMP h INNER JOIN(SELECT [Employee Name], DOB
FROM [Module Log]
UNION ALL
SELECT [Employee Name], DOB
FROM [Practice Log]
WHERE [Practice Name] LIKE 'Soarian Clinicals Hands-On Exercises') x ON x.[Employee Name] = h.[FULL NAME] AND x.DOB = h.DOB where h.cc='SMS_APP'

 

[SQLDenis]

lookup @@rowcount

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[yan19454]

the problem is I need to return value from the row count to my web application. Thx.

 

[SQLDenis]

>>the problem is I need to return value from the row count to my web application. Thx.

use a return parameter or an output parameter

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[yan19454]

Thx. Right now I use dataset and table count. I will check that out return parameter or an output parameter .

 

[yan19454]

ALTER PROCEDURE [dbo].[Find_SClinicals_Training_Total]
@DateFrom datetime='09/04/05',
@DateToIn datetime='03/04/06',
@mName varchar(50),
@ccName varchar(20),
@tCount int output

AS
SET NOCOUNT ON
begin
declare @DateTo datetime
select @DateTo = @DateToIn+1
declare @moduleName varchar(50)
--need to add date range and get the total count for the employee get the selected training
if @ccName='All' and @mName='All'
begin
--module log
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log] where [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo
UNION ALL
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
/** WHERE (h.CC = 'A11')**/
set @tCount=@@rowcount
PRINT '1 all all @Cc = ' + @ccName + ' @mName = ' +@mName
end

if (@ccName='All' and @mName<>'All' )
begin
if @mName='Soarian Clinicals Hands-On Exercises'
begin
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
set @tCount=@@rowcount
end
else
begin
SELECT distinct h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Module Log] WHERE [Module Name] like @mName and [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]

set @tCount=@@rowcount
end
PRINT '2all cc @mName = ' + @ccName + ' @mName = ' +@mName
end
if ( @ccName<>'All' and @mName='All' )
begin
--module log
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log] where [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo
UNION ALL
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
WHERE h.CC = @ccName

set @tCount=@@rowcount

PRINT '3CC not ALL @mName=All'+ @ccName +'Module Name' +@mName
end


if (@ccName<>'All' and @mName<>'All')

begin
--module log
if @mName='Soarian Clinicals Hands-On Exercises'
begin
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(
SELECT [Employee Name],DOB
FROM [Practice Log] WHERE [Practice Name] like 'Soarian Clinicals Hands-On Exercises' and [Access Practice Time] >= @DateFrom and [Access Practice Time]<@DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
where h.CC=@ccName

set @tCount=@@rowcount
end
else
begin
SELECT h.[FULL NAME], h.[DOB]
FROM annual_edu_2006.dbo.HREMP h inner JOIN
(SELECT [Employee Name],DOB
FROM [Module Log]
WHERE [Module Name] like @mName and [Access Module Time] >= @DateFrom and [Access Module Time] <= @DateTo ) x ON x.[Employee Name] = h.[Full Name] and x.[DOB] = h.[DOB]
WHERE h.CC = @ccName
GROUP BY h.[Full Name],h.[DOB]
ORDER BY h.[full Name]
set @tCount=@@rowcount
end


PRINT '4CC not ALL @mName not All'+ @ccName +'Module Name' +@mName

end



end














result:
Stored Procedure: Clinical_Edu_2006.dbo.Find_SClinicals_Training_Total
Return Code = 0
Output Parameter(s):
@tCount = 27


asp.net code:
Dim myConn As New SqlConnection(ConfigurationSettings.AppSettings("ConString"))

myConn.Open()

Dim selectCom As New SqlCommand("Find_SClinicals_Training_Total", myConn)

selectCom.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dtB
selectCom.Parameters.Add("@DateToIn", SqlDbType.DateTime).Value = dtE
selectCom.Parameters.Add("@mName", SqlDbType.VarChar).Value = Request.QueryString("PKey")
selectCom.Parameters.Add("@ccName", SqlDbType.VarChar).Value = Request.QueryString("CC")
Dim tCountParamater As New SqlParameter("@tCount", SqlDbType.SmallInt)
tCountParamater.Direction = ParameterDirection.Output

selectCom.Parameters.Add(tCountParamater)

selectCom.CommandType = CommandType.StoredProcedure
Dim reader As SqlDataReader = selectCom.ExecuteReader()

Dim TCount As Int32 = tCountParamater.Value

TCount is nothing here in asp.net
I tested it with reader.RecordsAffected=-1.

Do not know why ? Thx.

 

[Crowley16]

normally, I would do something like:

dim cm
dim cn

set cm = createobject("ADODB.Command")
set cn = createobject("ADODB.Connection")

cm.activeconnection = cn
cm.commandType = adcmdStoredProc
cm.commandText = "S_ProcName"

cm.parameters("@param1") = someval
...

cm.execute

result1 = cm.parameters("@output1") 
result2 = cm.parameters("@output2") 
...

code is direct from memory though, so don't copy/paste...

--------------------
Procrastinate Now!

 

[yan19454]

right now I use
Dim myConn As New SqlConnection(ConfigurationSettings.AppSettings("ConString"))

myConn.Open()

Dim selectCom As New SqlCommand("Find_SClinicals_Training_Total", myConn)

selectCom.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dtB
selectCom.Parameters.Add("@DateToIn", SqlDbType.DateTime).Value = dtE
selectCom.Parameters.Add("@mName", SqlDbType.VarChar).Value = Request.QueryString("PKey")
selectCom.Parameters.Add("@ccName", SqlDbType.VarChar).Value = Request.QueryString("CC")
Dim tCountParamater As New SqlParameter("@tCount", SqlDbType.SmallInt)
tCountParamater.Direction = ParameterDirection.Output

selectCom.Parameters.Add(tCountParamater)

selectCom.CommandType = CommandType.StoredProcedure
selectCom.ExecuteNonQuery()

Dim TCount As Int32
TCount = tCountParamater.Value
myConn.Close()
Return TCount.ToString

it worked . thx.