• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

Set the max value for a column

Hello,

The query needs to return refno:
The  refno in table should begin with 1 in a table every year.
If refno 1 is present then give the next value.

ALTER PROCEDURE  [dbo].[L_SelectLastVal_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(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]



	SET @SQL = 'SELECT TOP 1 '    
        
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'     + ' FROM  [dbo].[' + @tableName + ']  ORDER BY '  	+  '[' +  @ColumnName1 + ']'     +'DESC'	
	
	  SET @SQL = @SQL  	
	  
	  EXEC(@SQL)



		
   
	
END

Open in new window


And

ALTER PROCEDURE  [dbo].[MEDICAL_FindMaxSPRSpPayReq]
AS
BEGIN

        SELECT MAX(SPR#)+1
               FROM SIAL_PAT_REQT
		
				
END

Open in new window

0
RIAS
Asked:
RIAS
  • 4
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That was answered in your previous question.
0
 
RIASAuthor Commented:
Can I use the same code as the query is a bit different
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I guess it will work but give it a try and check.
Here you're using TOP 1 and ORDER BY and in the previous one you used MAX and GROUP BY. The latest is the better option.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
ok
0
 
RIASAuthor Commented:
Is this ok?

  DECLARE @SQL VARCHAR(1000) = null


SET @SQL = '
	
		SELECT 
		
				CASE 
						WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1 
				ELSE 
				
						TOP 1 ([' +  @ColumnName1 + ']'  + '  ) END Output'  
				
				+ ' FROM  [dbo].[' + @tableName + ']'  
			  
EXECUTE(@SQL)

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just use the solution from the previous question. Like I told you above, MAX is better option than TOP 1 and it will give you the same result but faster.
1
 
RIASAuthor Commented:
Cool!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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