Set Variable in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set Variable in Stored Procedure

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2010-07-11 : 02:40:53

I am running the below stored procedure I would like to concatenate the value on the grouping change onto the end of the string. See the first break for the example. Not sure how to set the variable value in this. mahalo, Brew

ALTER Procedure [dbo].[uspIvrRollUp]

as

Begin

Declare @YYYYMM varchar (10)

Select SQ.*, (SQ.NS/SQ.TA) as Percentage From (Select
case
When Grouping(YYYYMM) = 1 then 'All for ' 'Want to add value here'
Else YYYYMM
End as MyYearMonth,
case
When Grouping(Location) = 1 then 'All Location'
Else Location
End as MyLocations,
case
When Grouping(Department) = 1 then 'All Department'
Else Department
End as MyDepartment,
case
When Grouping(VisitType) = 1 then 'All Visit Types'
Else VisitType
End as MyVisitType,
sum(NoShows) as NS, sum(TotalAppts) as TA
From dbo.IVR
Group by Location, Department, VisitType, YYYYMM with Rollup) SQ
End

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-11 : 02:59:55

you should do a subquery in order to get the value of grouping first. and based on this returned value, you can use grouping value for concatenate purposes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources