SQL stored procedure variable not being set

HexElffilter · 29 Jun 2010 at 12:47

Hi folks,

Hope someone can help me with this.

I need to get monthly figures out of our database and create a report.
I have created a stored procedure to get this information, but when debugging in visual studio I find that one of the variables isn't being popuated.

Here is the procedure:

Code:

BEGIN
	DECLARE @extract_dates nvarchar(64), @month nvarchar(64), @extract_date nvarchar(64)

    -- Insert statements for procedure here
	DECLARE app_cursor CURSOR FOR
		SELECT DISTINCT TOP (100) PERCENT { fn MONTHNAME(EntryDate) } AS Month, MAX(EntryDate) AS ExtractDate
		FROM         dbo.Applications
		WHERE     (sqlAcYear = '2010')
		GROUP BY { fn MONTHNAME(EntryDate) }
		ORDER BY ExtractDate

	OPEN app_cursor
	FETCH NEXT FROM app_cursor INTO @month, @extract_date
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @extract_dates = ', CONVERT(DATETIME, ''' + @extract_date + ''', 102)' + @extract_dates

		FETCH NEXT FROM app_cursor INTO @month, @extract_date
	END

	CLOSE app_cursor

	DEALLOCATE app_cursor
END

	SET @extract_dates = SUBSTRING(@extract_dates, 1, LEN(@extract_dates)-1)

BEGIN
	SELECT DISTINCT TOP (100) PERCENT { fn MONTHNAME(EntryDate) } AS Month, EntryDate, 
		SUM(DISTINCT sqlp + sqlack + sqlao + sqlrw + sqlrd + sqlapa + sqliq + sqldna + sqlintv + sqlna + sqlowl + sqlwdrw + sqliap + sqlconfa + sqlconfb) AS Applications
	FROM dbo.Applications
	WHERE (sqlAcYear = '2010')
	GROUP BY EntryDate, { fn MONTHNAME(EntryDate) }
	HAVING EntryDate IN (@extract_dates)
	ORDER BY EntryDate
END

The variable in question is @extract_dates within the cursor.

Pho · 29 Jun 2010 at 13:19

Cursors are evil, I imagine that could be rewritten without them.

Does it work if you just use say:
SET @extract_dates = 'testtesttesttest' in the cursor? Is the loop evening happening (try printing something inside it and see if it shows up in the messages).

~J~ · 29 Jun 2010 at 13:27

Totally agree with Pho, get rid of the cursors and use a Loop:

Set @Index=1
While @Index<100
Select @Index
Set @Index=@Index+1
End

Should be a lot easier to debug. (and a hell of a lot quicker)

HexElffilter · 29 Jun 2010 at 14:01

Pho said:
Does it work if you just use say:
SET @extract_dates = 'testtesttesttest' in the cursor? Is the loop evening happening (try printing something inside it and see if it shows up in the messages).

Yes, the cursor is returning values.
I've discovered that it is because I can't set the variable value using itself.

I can't do:

SET @extract_dates = ', CONVERT(DATETIME, ''' + @extract_date + ''', 102)' + @extract_dates

But I can do:

SET @extract_dates = ', CONVERT(DATETIME, ''' + @extract_date + ''', 102)'

I just need to figure out a way of building a string

Pho · 29 Jun 2010 at 14:13

Might be because of the null initialisation.

Try:

PHP:

DECLARE @extract_dates nvarchar(64) = '', @month nvarchar(64), @extract_date nvarchar(64)

HexElffilter · 29 Jun 2010 at 14:50

If I do that I then get the following error:

Cannot assign a default value to a local variable.
Must declare the scalar variable @month.
Must declare the scalar variable @extract_date.
Must declare the scalar variable @month
Must declare the scalar variable @extract_dates.
Must declare the scalar variable @extract_dates.

HexElffilter · 29 Jun 2010 at 14:54

Managed to get it working.

Just added another line under the declare to set the variable as an empty string.

Thanks

Pho · 29 Jun 2010 at 14:54

OK how about this instead:

PHP:

SET @extract_dates = ', CONVERT(DATETIME, ''' + @extract_date + ''', 102)' + isnull(@extract_dates,'')