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, Oct 02 '15 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, Oct 02 '15 at 13:39
I removed flag. But if it's really your code, then why you have `execute [dbo].[ageTest] '19511214'` at the end? If you're compiling your code like this and executing SP again, it seems that it might try and call itself again and again. — Evaldas Buinauskas, Oct 02 '15 at 13:42
You are calling the procedure from inside itself because you don't have batch separator after your procedure. The default is GO. — Sean Lange, Oct 02 '15 at 13:42
@CM2K To answer the question you asked in the title - you can't access a variable that you defined in a string outside of it. You really shouldn't be using dynamic sql for this at all, it's just over-complicating it. — Siyual, Oct 02 '15 at 13:42
aaaa, i forgot to add go. damn it. Usually i just copy my procedure definition from one I've made. This time I forgot. And to answer your question. I use it at the end so it will be saved like that on the server and to be able to have a run example without checking the procedure's definition. — CM2K, Oct 02 '15 at 13:43
Ok, if I shouldn't use dynamic sql. How would you do it? but using a stored procedure. Otherwise it's too simple — CM2K, Oct 02 '15 at 13:46
@CM2K You can use straight SQL in stored procedures without having to shove it into a `@sql` variable - just saying. Simple is certainly better. It's easier to read, easier to maintain, and less prone to weirdness. — Siyual, Oct 02 '15 at 13:49
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, Oct 02 '15 at 13:49
I see, thank you. Unfortunately, I'm a beginner with stored procedures. This is the only way i (just) learned to pass a parameter in a variable. — CM2K, Oct 02 '15 at 13:51
off-topic: someone set this link as an answer to my question: http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days . I'm now to SO, how did that guy get upvotes? Wasn't he supposed to show what he tried? expected results and everything? — CM2K, Oct 02 '15 at 13:57
That's a 7 years old question, perhaps requirements for questions haven't been that strict back then. He should've shown what he has tried, but expected results? It's quite straightforward. — Evaldas Buinauskas, Oct 02 '15 at 14:01

score 3 · Accepted Answer · answered Oct 02 '15 at 14:00

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.

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. — CM2K, Oct 02 '15 at 14:05

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

1 Answers1