Simple dynamic sql question

Question

Simple dynamic sql question

kensai

Old Hand

Points: 352
More actions
October 27, 2002 at 12:06 pm

#357638

I coded the following stored procedure and it's giving me "Must declare the variable '@query'." error. Can you please take a look at it and let me know what I'm doing wrong?
create procedure proc_SearchGetTopRep
@state int='',@query varchar(200)=''
as
set nocount on
if @state<>''
begin
selectt.AdvertiserID,(select Name from dbo.[States] where ID=t.State) as StateName,t.Slot,
a.FullName,a.City
fromTopReplacement t inner join Advertisers a
on t.AdvertiserID = a.ID
wheret.State = @state
order by t.Slot
end
go
if @query<>''
begin
declare @sql nvarchar(3000)
set @sql='selectt.AdvertiserID,(select Name from dbo.[States] where ID=t.State) as StateName,t.Slot,
a.FullName,a.City
from TopReplacement t inner join Advertisers a
on t.AdvertiserID = a.ID
inner join States s on t.State=s.ID
where a.FullName='''+@query+''' OR
a.City like ''%'+@query+'%'' OR
a.Zipcode='''+@query+''' OR
s.Name like ''%'+@query+'%''
order by t.Slot'
exec sp_executesql @sql
end

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

The problem is that you have a GO statement at about line 14.

That mean that anything beyone that line isn't treated a part of the procedure, and is instead treated as a completely new batch of SQL.

Just remove that line.

Another point, you are declaring @state as an integer, and then comparing it to a string. You should really default it to null and test for null by using if @state is null...

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

You should also be passing your variable into the dyanmic SQL i.e

set @sql='select t.AdvertiserID,(select Name from dbo.[States] where ID=t.State) as StateName,t.Slot,

a.FullName,a.City

from TopReplacement t inner join Advertisers a

on t.AdvertiserID = a.ID

inner join States s on t.State=s.ID

where a.FullName=@query OR

a.City like ''%''+@query +''%'' OR

a.Zipcode=@query OR

s.Name like ''%'' +@query+ ''%''

order by t.Slot'

exec sp_executesql @sql, N'@query varchar(200)', @Query

also using OR in that way removes any use of indexes , and using LIKE with a preceeding % also remove the use of an index.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons