我有一个存储过程,该过程应该计算一个人的年龄。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[ageTest]
(
@curDate date
)
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(max)
declare @params nvarchar (1000)
declare @age date
set @params = N' @curDate date , @age date output'
set @sql = '
declare @dif float
declare @ageRound int
declare @theAge varchar (100)
set @age = ''19890406''
set @theAge =(select (datediff (mm, @age , getdate())))
set @dif = @theAge % 12
set @ageRound = cast (@theAge as float)/12
select @ageRound as Years, @dif as months
'
set @sql = replace (@sql, '19890406', @curDate)
execute sp_executesql @sql, @params, @curDate, @age output
end
execute [dbo].[ageTest] '19511214'
我想要获得的是两列:
Years Months
63 10
问题是循环。我可能应该从中删除选择@sql
并将其放在外面,然后出现声明问题。有什么想法吗?
编辑:不重复
如果您真的只是一个初学者。
首先,您应该始终在过程中使用TRY ... CATCH块。
这是您在代码中所做的快速重写:
-- =============================================
-- Procedure Name : dbo.ageTest
-- Usage Example : EXECUTE [dbo].[ageTest] '19511214';
-- =============================================
ALTER PROCEDURE [dbo].[ageTest]
(
@curDate DATE
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT DATEDIFF(MM, @curDate, CURRENT_TIMESTAMP) / 12 AS Years
, DATEDIFF(MM, @curDate, CURRENT_TIMESTAMP) % 12 AS Months;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
END
它显示了您应该如何在SP中使用参数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句