Simple dynamic sql question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple dynamic sql question

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-10-28 : 02:51:55

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
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
where t.State = @state
order by t.Slot
end
go

if @query<>''
begin
declare @sql nvarchar(3000)
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

end

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-28 : 03:48:56

After every "GO" statement you have to redefine the parameters...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-10-28 : 05:29:08

You can not put GO inside a stored proc.

GO is not SQL, it is just a command to query analyzer to split up batches of code.

Damian

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-28 : 07:23:52

Why not? If it's usefull...

I've seen a lot of other weird stuff in a stored proc that didn't quite compute but it worked.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-28 : 07:45:45

Was that a reply to Merkin?

A go terminates the batch so the stored proc will be compiled up to the go, anything after that will not be part of the SP but will be executed immediately which is why the error is given.

As an aside the compiler also terminates the batch if it finds go at the begining of a line in comments. If you want to confuse people put a lot of 'go's in your comments and wait for someone to update them so a go turns up at the beginning of a line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources