[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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

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

0
Mike Eghtebas
Asked:
Mike Eghtebas
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need to have dynamic sql


      declare @nsql varchar(1000)
        SELECT @nsql = 'SELECT t.LastYearCount  as ['+@LY+'], t.CurrYearCount as ['+@YYYY+', (t.IncreaseOverLastYear/100) as [%]  
      FROM... '
EXEC (@nSQL)
0
 
Scott PletcherSenior DBACommented:
You'd have to use dynamic SQL to do that, something like this:

DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT t.LastYearCount  as [' + @LY + '], t.CurrYearCount as [' + @YYYY + '], t.IncreaseOverLastYear/100) as [%] FROM...'
EXEC(@sql)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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 @.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Mike EghtebasDatabase and Application DeveloperAuthor 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

0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you,

 Mike
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks,

MIke
0
 
PortletPaulCommented:
>>"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'
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you for the explanations.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now