passing count to variable in dynamic sql

[richl13]

I am trying to create a stored proc with a dynamic sql statement. What I am trying to accomplish is passing the count(*) to a integer variable. Below is my sql. The @iPul is returning NULL. How should I change this so I can get the value in @iPul?

SET @SQLFromWhere = ' FROM P LEFT Join S on P.HosID = S.site'
	+ ' WHERE P.HosID = ' + @Site
	+ ' AND P.Emailed = 1'
	+ ' AND datepart(mm, P.EmailSent) <= ' + convert(varchar, @Month)
	+ ' AND datepart(yyyy, P.EmailSent) = ' + convert(varchar, @Year)
	+ ' AND P.ChartReviewCompleted = 1'

SET @command = 'SELECT ' + @sPul + ' = count(*)' 
	+ @SQLFromWhere  --" + @iPul + " =
	+ ' AND PulOx = 1'
EXEC (@command)

SET @iPul = convert(int, @sPul)

 

[nice95gle]

Can you post the whole procedure so we can get a better understanding of what you are trying to accomplish.

Well Done is better than well said
- Ben Franklin

 

[richl13]

Attached is one of the procedures I am trying to change. I haven't put the code I posted in, but you should be able to tell what I am trying to do.

ALTER PROCEDURE RapidReport_YTD_By_Site(@Month int, @Year int, @Site varchar(10)) as
SET NOCOUNT ON

DECLARE @sSite_Num varchar(10)
DECLARE @sSite_Code varchar(10)
DECLARE @sSite_Name varchar(80)
DECLARE @iNumPatientsTotal int
DECLARE @iPulse int
DECLARE @iOxygen int
DECLARE @iBetaAgonist int
DECLARE @iSteroidsED int
DECLARE @iSteroidsDC int
DECLARE @iLHCExam int

DECLARE @Table1 table
	(Site_Num varchar(10),
	Site_Code varchar(10),
	Site_Name varchar(80),
	NumPatients int,
	Pulse int,
	PulsePct decimal (8,2),
	Oxygen int,
	OxygenPct decimal(8,2),
	BetaAgonist int,
	BetaAgonistPct decimal (8,2),
	SteroidsED int,
	SteroidsEDPct decimal(8,2),
	SteroidsDC int,
	SteroidsDCPct decimal(8,2),
	LHCExam int,
	LHCExamPct decimal(8,2))
BEGIN
BEGIN

SELECT @sSite_Num = A.HospitalID, @sSite_Code = S.facility_code, @sSite_Name = S.facility_name, 
	@iNumPatientsTotal = count(*)	
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1  ---Has been emailed to the practitioner already
AND A.ChartReviewCompleted = 1  --The chart is marked as completed
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
GROUP BY A.HospitalID, S.facility_code, S.facility_name

SELECT @iPulse = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.PulseOximetry = '1'

SELECT @iOxygen = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.OxygenGiven = '1'

SELECT @iBetaAgonist = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.BetaAgonist = '1'

SELECT @iSteroidsED = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.SysSteroidsED = '1'

SELECT @iSteroidsDC = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1 
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND ((A.SysSteroidsDC = '1')  --Yes
OR (A.SysSteroidsDC='2' AND A.Disposition in ('01','02','03','04','50','51','52','70')))

SELECT @iLHCExam = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1 
AND A.ChartReviewCompleted=1
AND A.HospitalID = @Site 
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.LHCExam = 1


INSERT INTO @Table1 VALUES (@sSite_Num, @sSite_Code, @sSite_Name, @iNumPatientsTotal,
	@iPulse,
	dbo.PercentDoc(@iPulse, @iNumPatientsTotal),
	@iOxygen,
	dbo.PercentDoc(@iOxygen, @iNumPatientsTotal),
	@iBetaAgonist, 
	dbo.PercentDoc(@iBetaAgonist, @iNumPatientsTotal),
	@iSteroidsED, 
	dbo.PercentDoc(@iSteroidsED, @iNumPatientsTotal),
	@iSteroidsDC, 
	dbo.PercentDoc(@iSteroidsDC, @iNumPatientsTotal),
	@iLHCExam,
	dbo.PercentDoc(@iLHCExam, @iNumPatientsTotal)
	)
END

BEGIN

set @sSite_Num = ''
set @sSite_Code = ''
set @sSite_Name = 'All Sites'
set @iNumPatientsTotal = 0
set @iPulse = 0 
set @iOxygen = 0
set @iBetaAgonist = 0
set @iSteroidsED = 0
set @iSteroidsDC = 0
set @iLHCExam = 0

SELECT @iNumPatientsTotal = count(*)	
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1  ---Has been emailed to the practitioner already
AND A.ChartReviewCompleted = 1  --The chart is marked as completed
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year

SELECT @iPulse = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.PulseOximetry = '1'

SELECT @iOxygen = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.OxygenGiven = '1'

SELECT @iBetaAgonist = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.BetaAgonist = '1'

SELECT @iSteroidsED = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.SysSteroidsED = '1'

SELECT @iSteroidsDC = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1 
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND ((A.SysSteroidsDC = '1')  --Yes
OR (A.SysSteroidsDC='2' AND A.Disposition in ('01','02','03','04','50','51','52','70')))

SELECT @iLHCExam = count(*)
FROM Asthma A LEFT Join SiteMgmt.dbo.SiteAddress S on A.HospitalID = S.facility_site_num
WHERE A.Emailed = 1 
AND A.ChartReviewCompleted=1
AND datepart(mm, A.EmailSent) <= @Month
AND datepart(yyyy, A.EmailSent) = @Year
AND A.LHCExam = 1


INSERT INTO @Table1 VALUES (@sSite_Num, @sSite_Code, @sSite_Name, @iNumPatientsTotal,
	@iPulse,
	dbo.PercentDoc(@iPulse, @iNumPatientsTotal),
	@iOxygen,
	dbo.PercentDoc(@iOxygen, @iNumPatientsTotal),
	@iBetaAgonist, 
	dbo.PercentDoc(@iBetaAgonist, @iNumPatientsTotal),
	@iSteroidsED, 
	dbo.PercentDoc(@iSteroidsED, @iNumPatientsTotal),
	@iSteroidsDC, 
	dbo.PercentDoc(@iSteroidsDC, @iNumPatientsTotal),
	@iLHCExam,
	dbo.PercentDoc(@iLHCExam, @iNumPatientsTotal)
	)
END

SELECT * FROM @Table1

END
GO

 

[mrdenny]

You can't pass variables back from dynamic SQL like that. There are two ways to do it.

1. Create a temporary table before the dynamic SQL, and have the dynamic SQL write the value to the temp table before it's execution is complete.

2. Use sp_executesql to pass the data back and forth via an output variable.

I don't see any dynamic SQL within your procedure. Where is the dynamic SQL part of the question comming from?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[richl13]

I was trying to use the dynamic sql in my original post instead of having a dozen sql statements with the only difference being the variable and one line in the WHERE.

Since I couldn't get it to work I went ahead and copied the SELECT and made the change.