Using Max function to select from a variable

[albitzt]

The code is already written and at this point I'm not reflecting on it and I was wondering what I was missing, or if you simply cannot do this. In the below example, what I was looking to do is this:

SELECT @PKVAL=max(@PK_COL_NAME) from pcattype

Thanks in advance.



-------------------------------------------------------------
DECLARE @NEXTKEY_COL_NAME char(50)
DECLARE @PK_COL_NAME char(50)
DECLARE @NKVAL varchar(50)
DECLARE @PKVAL varchar(50)


SET @NEXTKEY_COL_NAME = 'pcattype_proj_catg_type_id'
SET @PK_COL_NAME = 'proj_catg_type_id'


SELECT @NKVAL=key_seq_num from nextkey where key_name = @NEXTKEY_COL_NAME
SELECT @PKVAL=max(proj_catg_type_id) from pcattype
PRINT @NEXTKEY_COL_NAME
IF @NKVAL > @PKVAL
PRINT 'OKAY'
ELSE
PRINT 'FAIL'
PRINT 'NKVAL: ' + @NKVAL
PRINT 'PKVAL: ' + @PKVAL
PRINT ' '
go

 

[markros]

You need to use dynamic SQL if you want to query a dynamic column.

So, your code will be

declare @SQL nvarchar(max)

set @SQL = N'select @PkVal = MAX(' + quotename(@Pk_Col_Name) + ') from dbo.pcAtype'

execute sp_ExecuteSQL @SQL, N'@PkVal int OUTPUT', @PkVal OUTPUT

PluralSight Learning Library

 

[albitzt]

Makes sesnse..Thanks for the response.