We help IT Professionals succeed at work.

Select Field1 as @YYYY, ....  dynamic alias name

374 Views
Last Modified: 2014-08-26
Question: How can I make this query take @LY and @YYYY as alias names?

Thank you.
ALTER PROCEDURE [dbo].[spAuunuaCountMedCtr]
	 @StatType_ID INT = NULL
	 ,@YYYY NVARCHAR(4) = NULL
	 ,@RegionID INT = NULL
AS
BEGIN

	DECLARE @LY NVARCHAR(4)
	SET @LY = CONVERT(Varchar(4),CONVERT(INT,@YYYY)-1)
	select @LY, @YYYY
	
	SELECT t.LastYearCount  as @LY, t.CurrYearCount as @YYYY, (t.IncreaseOverLastYear/100) as [%]  
	FROM...

END

Open in new window

Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Just to 'check the box' here, it's not good programming practice to use an @ sign as the first character of an alias, as SQL will interpret that as a variable.  Best to avoid this and not use an @.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Aneesh and ScottPletcher,

The solution works but I had to use:

WHERE (((s.StatTypeID)=' + CONVERT(varchar(100),@StatType_ID) + ') AND...

Question: Why do I need to convert it to varchar when s.StatTypeID is int?

Thank you.
ALTER PROCEDURE [dbo].[spAuunuaCountMedCtr]
	 @StatType_ID INT = NULL
	 ,@YYYY VARCHAR(4) = NULL
	 ,@RegionID INT = NULL
AS
BEGIN

	DECLARE @LY VARCHAR(4)
	DECLARE @nsql VARCHAR(4000)
	SET @LY = CONVERT(VARCHAR(4),CONVERT(INT,@YYYY)-1)
		
	SELECT @nsql = 'SELECT t.LastYearCount  as [' + @LY + '], t.CurrYearCount as [' + @YYYY + '], (t.IncreaseOverLastYear/100) as [%]  
	FROM t14StatType AS s INNER JOIN (t08MedCenter AS m INNER JOIN t10YearlyCount AS t 
	ON m.MedCtrID = t.MedCtr_ID) ON s.StatTypeID = t.StatType_ID 
	WHERE (((s.StatTypeID)=' + CONVERT(varchar(100),@StatType_ID) + ') AND ((t.YYYY)=' + @YYYY + ') AND ((m.ExcludeYN)=0) 
	   AND ((t.Region_ID)=' + CONVERT(varchar(100),@RegionID) + ')) ORDER BY m.Sort;' 

    EXEC (@nsql)
END

GO

exec spAuunuaCountMedCtr 11, '2013', 1

Open in new window

Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank you,

 Mike
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>Why do I need to convert it to varchar when s.StatTypeID is int?

Since you are building a dynamic sql and assigning to a varchar /nvarchar field , you need to convert the integer to varchar, otherwise , sql server will try to convert the string before it to integer and throws an error
Mike EghtebasDatabase and Application Developer

Author

Commented:
Thanks,

MIke
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"Why do I need to convert it to varchar when s.StatTypeID is int?"

Notice you are using the + operator

select 1 + 1
will produce 2

select 'blah blah ' + 1
in TSQL this produces an error due to the different types involved, so, here, the types all need to be strings
(it doesn't know if you want to add 2 numbers, or concatenate 2 strings, so it errors instead so you can decide)

----
If using SQL Server 2012 of later, you will be able to use CONCAT()
and it will handle conversion of types for you. e.g. this would be allowed:

select CONCAT('blah blah ' , 1)
which will produce: 'blah blah 1'
Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank you for the explanations.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.