CODEWITHSUNDEEP
stored-proceduresreporting-services
GertDeWilde
GertDeWilde

Reputation: 351

How to use a numeric parameter in a stored procedure?

Good morning,

I'm having troubles to integrate a working query into a stored procedure.
My main issue, is that I'm using a WHILE loop with an integer, and that the stored procedure is having troubles with it.

My working query/code is the following:

CREATE TABLE #tempScaffStandingByTime
(
    TotalStanding INT,
    MonthsAgo INTEGER
) 

DECLARE @StartDate DATETIME = null
DECLARE @months INTEGER = 12

use [Safetrak-BradyTechUK]
WHILE @months >= 0
BEGIN
   SET @StartDate = DATEADD(mm, -12 + @months, DATEADD(mm, 0, DATEADD(mm, 
   DATEDIFF(mm,0,GETDATE()-1), 1)))
   INSERT INTO #tempScaffStandingByTime
   select TOP 1 COUNT(*) OVER () AS TotalRecords, @months
   from asset a
   join wshhistory h on h.assetid = a.uid
   where a.deleted = 0 and h.assetstate <> 6 
   AND (dbo.getdecommissiondate(a.uid) > @StartDate)
   group by a.uid
   SET @months -= 3
END

SELECT * FROM #tempScaffStandingByTime

DROP TABLE #tempScaffStandingByTime

This results in the input which I want:

enter image description here

I then tried to import this code into my stored procedure

        DECLARE @Query  varchar (8000)
              , @Account varchar (100) = 'BradyTechUK'

        SET @Account = 'USE [Safetrak-' + @Account + ']'

/************************************************************************************/

/********** Create Table to hold data ***********************************************/

            CREATE TABLE #tempScaffStandingByTime
            (
                TotalStanding INT,
                MonthsAgo INTEGER
            ) 

/************************************************************************************/

/********** Populate temp table with data *******************************************/

    DECLARE @StartDate DATETIME = null
    DECLARE @months INTEGER = 12

    SET @Query= +@Account+ ' 
            WHILE '+@months+' >= 0
                BEGIN
                    SET '+@StartDate+' = DATEADD(mm, -12 + ('+@months+', DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm,0,GETDATE()-1), 1)))
                    INSERT INTO #tempScaffStandingByTime
                    select TOP 1 COUNT(*) OVER () AS TotalRecords, '+@months+'
                    from asset a
                    join wshhistory h on h.assetid = a.uid
                    where a.deleted = 0 and h.assetstate <> 6 
                    AND (dbo.getdecommissiondate(a.uid) > '+@StartDate+')
                    group by a.uid
                    SET '+@months+' -= 3
                END'
    EXEC (@Query)

/************************************************************************************/

/********** Select Statement to return data to sp ***********************************/

    Select  TotalStanding
            , MonthsAgo
        FROM #tempScaffStandingByTime

/************************************************************************************/

 DROP TABLE  #tempScaffStandingByTime

But when loading the stored procedure in SSRS I get a conversion error.
I can convert my parameter to a string, but then it won't count anymore.

enter image description here

I searched far and wide on the internet, tried various things, but can't get it to work.

Any help is appreciated, thanks!

Upvotes: 0

Views: 655

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

It looks like your problem original comes from the lack of conversion between the INT in @months and your VARCHAR in @Accounts and @Query.
You need to cast the months to a string to append it, otherwise the SQL Server thinks you're trying to do addition.

However you'll also have problems with the usage of your @StartDate variable as you try to set it via your dynamic SQL which is not going to work like that - and as with @months it'll have an incorrect datatype, but I'm also unsure why it's written as is, and what you're tying to achieve with it

I think - from what you're writing - that this might be what you're looking for?

    SET @Query= @Account + '
    DECLARE @StartDate DATETIME = null
    DECLARE @months VARCHAR(2) = ''12''

        WHILE @months >= 0
            BEGIN
                SET @StartDate = DATEADD(mm, -12 + @months, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm,0,GETDATE()-1), 1)))
                INSERT INTO #tempScaffStandingByTime
                select TOP 1 COUNT(*) OVER () AS TotalRecords, @months, @StartDate
                from asset a
                join wshhistory h on h.assetid = a.uid
                where a.deleted = 0 and h.assetstate <> 6 
                AND (dbo.getdecommissiondate(a.uid) > @StartDate)
                group by a.uid
                SET @months -= 3
            END'    
    EXEC (@Query)

Upvotes: 2

Related Questions