Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

passing count to variable in dynamic sql

Status
Not open for further replies.
richl13

richl13

Programmer
Joined
May 1, 2007
Messages
16
Location
US
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?
Code: 
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)
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
Code: 
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
 
Upvote 0 Downvote
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)
[noevil]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
884
Chriss Miller
C
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
883
Chris Miller
Chris Miller
V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
1K
Volvere
V
gbaughma
  • Locked
  • Question Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
Andrzejek
  • Locked
  • Question Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
879
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top