CODEWITHSUNDEEP
sql-servert-sql
Tomb_Raider_Legend
Tomb_Raider_Legend

Reputation: 451

SQL Server - Must declare the scalar variable

I am trying to declare these values and use execute them to get a specific values of an attribute in a table. However everytime I am time trying to set @test and @columnPeriod it gives me the error Must declare the scalar variable "@test" or Must declare the scalar variable "@columnPeriod"

DECLARE @columnPeriod VARCHAR(MAX), @test INT;
SET @test = 2015;
SET @columnPeriod = 'SELECT Period FROM Courses WHERE year = ' + @test + '';

Upvotes: 1

Views: 6926

Answers (2)

EoinS
EoinS

Reputation: 5482

You can't combine Strings and INTs

Try 

SET @columnPeriod = 'SELECT Period FROM Courses WHERE year = ' + CONVERT(VARCHAR(12),@test) + ' Limit 1';

This will also limit your selection to ensure it is scalar

Upvotes: 2

xQbert
xQbert

Reputation: 35323

DECLARE @columnPeriod INT, @test varchar(4);
SET @test = '2015';
SET @columnPeriod = SELECT distinct Period FROM Courses WHERE year = ' + @test + '';

Assumptions being:

  • period only has ONE record with year 2015 if you have more than one but all the same "period" then distinct may be needed. If you have multiple, you can't do this as the record set returned has more than one row...
  • Year is a varchar datatype

Upvotes: 2

Related Questions