Stored procedure with a parameter

You'll need to check that column name in the sys.columns view. If exists then add the WHERE clause:

ALTER PROCEDURE  [dbo].[M_SelectMaxVal_FrmCol]
(
 @tableName varchar(100) = null,
 @ColumnName1 varchar(100) = null	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;  	
	DECLARE @SQL VARCHAR(1000) = null
	
	SET @SQL = 'SELECT CASE 
			WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1 
			ELSE MAX ([' +  @ColumnName1 + ']'  + ' +1 ) END Output'  
		+ ' FROM  [dbo].[' + @tableName + ']'

	IF EXISTS(SELECT 1
		FROM sys.tables t
			INNER JOIN sys.columns c ON t.object_id=c.object_id
		WHERE t.name = @tableName AND c.name = 'year_ref')
		-----Add this only if year_ref column is present in the table 
		SET @SQL = @Sql  +' where year_ref = 22'
			  
	EXECUTE(@SQL)
	
END

Select allOpen in new window

Thanks Vitor,

Is there a way where where year_ref take current year and checks in the look up table for the number my lookup table is like this:
table _YearLookup
Columns are  year_no and year_ref

0	0
1	1995
2	1996
3	1997
4	1998
5	1999
6	2000
7	2001
8	2002
9	2003
10	2004
11	2005
12	2006
13	2007
14	2008
15	2009
16	2010
17	2011
18	2012
19	2013
20	2014
21	2015
22	2016
23	2017
24	2018

Select allOpen in new window

So should return '22' for 2016 and so on

Cheers,

This is the query to create the table

ALTER PROCEDURE  [dbo].[SP_TblCreateYEAR_LOOKUP1]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

    --Script to create a look up table to dispaly years
	CREATE TABLE YEAR_LOOKUP
		(Year_Ref smallint NOT NULL,
		 Year_no smallint NOT NULL,
		 PRIMARY KEY (Year_Ref))
   
END

Select allOpen in new window

Query2 to add values

ALTER PROCEDURE  [dbo].[SP_TblCreateYEAR_LOOKUP1.1]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

  
    --Script to insert data in table  Year_Lookup 
		
	INSERT INTO YEAR_LOOKUP (Year_Ref, Year_no) VALUES
				 (0,0)
				,(1,1995),(2,1996),(3,1997),(4,1998)
				,(5,1999), (6,2000),(7,2001 ),(8,2002),(9,2003)
				,(10,2004),(11,2005),(12,2006),(13,2007),(14,2008)
				,(15,2009),(16,2010),(17,2011),(18,2012),(19,2013)
				,(20,2014),(21,2015),(22,2016),(23,2017),(24,2018)
END

Select allOpen in new window

Vitor, Cheers!
It worked like charm!

So you won't need to use the lookup table in the SP?