RIAS
asked on
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.
And
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
And
ALTER PROCEDURE [dbo].[MEDICAL_FindMaxSPRSpPayReq]
AS
BEGIN
SELECT MAX(SPR#)+1
FROM SIAL_PAT_REQT
END
That was answered in your previous question.
ASKER
Can I use the same code as the query is a bit different
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.
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.
ASKER
ok
ASKER
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool!