-1

I think I am trying to do something that is relatively straight forward, but keep getting the 'Must Declare Scalar Variable Error'. I have two variables:

DECLARE @CountOfThisCampaignSegment int
DECLARE @CountOfLastCampaignSegment int 

select @CountOfThisCampaignSegment = COUNT(seg1) from #thisCampaignFinal

select @CountOfLastCampaignSegment = COUNT(seg1) from #lastCampaignFinal

I read that somewhere else that it seems I need to turn them into nvarchar, but I need to compare these two variables for an IF statement down the track in my procedure - so I am not sure if that would cause problems?

If anyone could give me some advice and background on why SQL Server throws this error, it would be greatly appreciated!

Many Thanks!

JackReacher
  • 445
  • 4
  • 8
  • 19
  • Are you sure your temp tables are still there? Maybe use `##thisCampaignFinal` and `##lastCampaignFinal` instead. – David Starkey Jun 04 '13 at 23:06
  • 1
    Is that your entire script? Do you, perchance, have any `GO` statements in it? – Blorgbeard Jun 04 '13 at 23:06
  • @David no, please don't suggest ##global temp tables. This effectively limits concurrency to 1. – Aaron Bertrand Jun 04 '13 at 23:38
  • Can I just ask why I got down voted for this question? – JackReacher Jun 05 '13 at 00:22
  • @AaronBertrand If concurrency is going to become an issue you can append current DATETIME to the table name in your code. Not a big deal. – David Starkey Jun 05 '13 at 15:46
  • @DavidStarkey Still don't understand what you get by using a ##global temp table. If you use a local #temp table you don't have to append DATETIME to the table name, and it still survives GO separators... – Aaron Bertrand Jun 05 '13 at 16:02
  • @AaronBertrand If you want to make multiple calls to the table from an external application (say ASP) then a local temp table will not work. The table will be deleted after the query finishes. A global temp table will remain, allowing several pulls. For more information see this answer: http://stackoverflow.com/a/2921091/1618257 – David Starkey Jun 05 '13 at 16:07
  • @DavidStarkey is that what the user is doing? How do you know? ##global temp tables have very limited scope and usefulness, I was just stating that you shouldn't offer this as a "solution" to the wrong problem. – Aaron Bertrand Jun 05 '13 at 16:13
  • @AaronBertrand Agree, but that was not an answer, it was a comment. Seeing that there was an error relating to something not being declared made me think that perhaps multiple calls were being made and the tables would then be deleted. – David Starkey Jun 05 '13 at 16:26
  • @DavidStarkey yes, it was just a comment, but still sounded like a suggested solution. – Aaron Bertrand Jun 05 '13 at 16:38

1 Answers1

3

I suspect you actually have more than one batch in your script. Batches are separated by GO statements (by default) in SQL Server Management Studio.

Variables do not persist across batches. So this, for example, gives an error:

DECLARE @CountOfThisCampaignSegment int
DECLARE @CountOfLastCampaignSegment int 
select @CountOfThisCampaignSegment = COUNT(seg1) from #thisCampaignFinal
select @CountOfLastCampaignSegment = COUNT(seg1) from #lastCampaignFinal

-- other statements

GO

-- this line fails with "must declare scalar variable":
select @CountOfThisCampaignSegment, @CountOfLastCampaignSegment
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272