dynamic SQL : use variable declared in a string, outside of the string

Question

I have a stored procedure which should calculate one's age.

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'

What I want to obtain is two columns:

Years     Months
63         10

Right now it looks like this:

The problem is it loops. I should probably remove the select from @sql and put it outside, then I have the declaration problem. Thoughts?

Edit: not a duplicate

just for fun, I'm trying to learn more on my own. I just want to be able to execute it and pass a date. I'm trying to find new challanges for myself. This is what i'm trying to do today. So, why not? How else would you do it? As a stored procedure in which you pass the date? — CM2K
– CM2K, Commented Oct 2, 2015 at 13:38
It's not a duplicate, I just wrote this procedure 30 min ago without looking at any website. Ok, had to google the cast, but that was it\ — CM2K
– CM2K, Commented Oct 2, 2015 at 13:39
You are calling the procedure from inside itself because you don't have batch separator after your procedure. The default is GO. — Sean Lange
– Sean Lange, Commented Oct 2, 2015 at 13:42
I would pull everything out of dynamic sql. And I would NOT use a float. The float datatype is an approximate, you should instead use numeric. And stored procedures should be simple. — Sean Lange
– Sean Lange, Commented Oct 2, 2015 at 13:49

Evaldas Buinauskas · Accepted Answer · 2015-10-02 14:00:09Z

3

IF you're really only a beginner.

First of all, you should always be using TRY...CATCH block in your procedures.

Here's a quick rewrite of what you've done in your code:

-- =============================================
-- 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

It shows how you should be using parameters in SP.

answered Oct 2, 2015 at 14:00

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CM2K Over a year ago

Oh, thank yolu! I started sql 4 months ago from 0. Just got into stored procedures and didn't try much. I was happy that i could even get that thing to run in a short time.

Collectives™ on Stack Overflow

dynamic SQL : use variable declared in a string, outside of the string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related