declare variable

Question

declare variable

surma.sql

SSCommitted

Points: 1552
More actions
July 5, 2012 at 2:08 pm

#260803

hello
i have trouble to declare a variable. i try to declare string variable but it gives me null value
declare @vcDisplaySchoolName varchar
set @vcDisplaySchoolName = 'CITY CONNECTIONS- EAST'
select vcDisplaySchoolName from tblSchools
where iSchoolCode between 100 and 499
and vcDisplaySchoolName = @vcDisplaySchoolName
so please correct me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

surma.sql (7/5/2012)
hello
i have trouble to declare a variable. i try to declare string variable but it gives me null value
declare @vcDisplaySchoolName varchar
set @vcDisplaySchoolName = 'CITY CONNECTIONS- EAST'
select vcDisplaySchoolName from tblSchools
where iSchoolCode between 100 and 499
and vcDisplaySchoolName = @vcDisplaySchoolName
so please correct me

Try giving the varchar a size:

declare @vcDisplaySchoolName varchar(32); -- for example

surma.sql SSCommitted Points: 1552 More actions · Answer 2

Lynn Pettis (7/5/2012)
surma.sql (7/5/2012)
hello
i have trouble to declare a variable. i try to declare string variable but it gives me null value
declare @vcDisplaySchoolName varchar
set @vcDisplaySchoolName = 'CITY CONNECTIONS- EAST'
select vcDisplaySchoolName from tblSchools
where iSchoolCode between 100 and 499
and vcDisplaySchoolName = @vcDisplaySchoolName
so please correct me
Try giving the varchar a size:
declare @vcDisplaySchoolName varchar(32); -- for example

Thanks !!! It Works

My main problem is in SSRS. If You know, then please reply

I need to pass parameter from below query

select vcDisplaySchoolName from tblSchools

where iSchoolCode between 100 and 499

in to my main data set.

i try this in my where clause

s.vcDisplaySchoolName = '@vcDisplaySchoolName'

but i get blank values.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

surma.sql (7/5/2012)
hello
i have trouble to declare a variable. i try to declare string variable but it gives me null value
declare @vcDisplaySchoolName varchar

No length on the varchar means that's a varchar(1). Always declare the lengths of the varchar, char and similar data types when declaring or converting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

surma.sql (7/5/2012)
s.vcDisplaySchoolName = '@vcDisplaySchoolName'

By wrapping that in quotes you're asking for rows that have a school name of "@vcDisplaySchoolName", not the value of the variable. Lose the quotes

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

surma.sql SSCommitted Points: 1552 More actions · Answer 5

GilaMonster (7/5/2012)
surma.sql (7/5/2012)
s.vcDisplaySchoolName = '@vcDisplaySchoolName'
By wrapping that in quotes you're asking for rows that have a school name of "@vcDisplaySchoolName", not the value of the variable. Lose the quotes

Thanks Sir !! Thats valuable information for me.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

surma.sql (7/5/2012)
GilaMonster (7/5/2012)
surma.sql (7/5/2012)
s.vcDisplaySchoolName = '@vcDisplaySchoolName'
By wrapping that in quotes you're asking for rows that have a school name of "@vcDisplaySchoolName", not the value of the variable. Lose the quotes
Thanks Sir !! Thats valuable information for me.

GilaMonster is a she and I am a he. Just thought you should know.

dan-572483 SSCoach Points: 15870 More actions · Answer 7

I don't recall any female Jedi Knights. What's up with that Lucas? Alas, that is a debate for another forum.