Help with declare

Question

Post reply

Help with declare

Susan-322874

SSCrazy

Points: 2300
More actions
August 13, 2006 at 1:45 pm

#153133

I keep getting the message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'declare'.

What am I doing wrong?

declare @dbname varchar(8000),
declare
@countyname varchar (200) ,
declare
@sql varchar(8000)

declare
county_name cursor for
select distinct county from Zipcodes
open
county_name
fetch
next from county_name
into
@countyname

declare
dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open
dbname_name
fetch
next from dbname_name
into
@dbname

WHILE
@@FETCH_STATUS = 0
BEGIN

set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'
+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end

set
@sql = ''
fetch
next from dbname_name into @dbname
fetch
next from county_name into @countyname

CLOSE
county_name
DEALLOCATE
county_name
CLOSE
dbname_name
DEALLOCATE
dbname_name

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Remove the commas....

declare @dbname varchar(8000)

declare @countyname varchar (200)

declare @sql varchar(8000)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Susan-322874 SSCrazy Points: 2300 More actions · Answer 2

I tried that and receive these errors

Msg 137, Level 15, State 2, Line 3

Must declare the scalar variable "@dbname".

Msg 156, Level 15, State 1, Line 3

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You have @dbname inside of your dynamic script.

I don't see where you have it declared in this script.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

And I better kep silence about the rest of your code.

I can only guarantee that you could not pass even entry level test on any programming course presenting this kind of code.

Programming language does not matter.

_____________
Code for TallyGenerator

Susan-322874 SSCrazy Points: 2300 More actions · Answer 5

Your right.. I'm learning..

Any tips?? Or to many to mention?

I modified the code, but still get the same error.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Yes, too many to mention.

Start from the flow. Where do you enter the loop, what will get you out of there?

Then why do you need 2 cursors?

Status of which one do you check with @@FETCH_STATUS ?

More to follow.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

SG,

Normally, a couple of us would answer the question of "What am I doing wrong?" with "You're using a cursor".

Rather than trying to salvage this code, I think it would be beneficial to you if you posted "what" you are trying to do without indicating "how". And, you need to post a little info about your environment... a table schema here and there... a sprinkling of data from each... and information like did they really put the screws to you by putting this information in more than one database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 8

Hello SG,

To correct your specific error: Must declare the scalar variable "@dbname",

change your code as shown below.

------------------------------------------------

set @sql =

'select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'''+@dbname+

''' ,(select @@servername) as servername

, ''' + @countyname + ''' AS county

from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'

exec (@sql)

------------------------------------------------

However, as the other responders have said, this looks like the long way around whatever it is you're trying really trying to do! I took the liberty of formatting your SQL to make it easier for me to read:

================================================

declare @dbname varchar(8000)

declare @countyname varchar (200)

declare @sql varchar(8000)

declare county_name cursor for

select distinct county from Zipcodes

open county_name

fetch next from county_name

into @countyname

declare dbname_name cursor for

select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'

open dbname_name

fetch next from dbname_name

into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql =

'select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'+@dbname+

' ,(select @@servername) as servername, county'+@countyname+

'from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'

exec (@sql)

end

set @sql = ''

fetch next from dbname_name into @dbname

fetch next from county_name into @countyname

CLOSE county_name

DEALLOCATE county_name

CLOSE dbname_name

DEALLOCATE dbname_name

================================================

The two nested cursors look like they run through for every combination of

- distinct county from the zipcode table,

- and dbname from sys.databases where name like 'Property%' and name <> 'PropertyCenter'

You can get that in one "cartesian join" like this:

Bob's Sub-query #1:

===================

SELECT zz.county, dd.name

FROM (SELECT distinct county from zipcodes) AS zz

, sys.databases AS dd

WHERE dd.name like 'Property%' and dd.name <> 'PropertyCenter'

Now, the dynamic SQL looks like you want to run it for each row in the result set from sub-query #1. You might be able to do it like this:

SELECT p.sa_property_id

, z.zipcode as sa_site_zip

, z.state as sa_site_state

, z.city as sa_site_city

, z.county as sa_site_county

, subq.name

,(select @@servername) as servername

, z.county

from (zipcodes z

join (

SELECT zz.county, dd.name

FROM (SELECT distinct county from zipcodes) AS zz

, sys.databases AS dd

WHERE dd.name like 'Property%' and dd.name <> 'PropertyCenter'

) as subq ON z.county = subq.county)

join tbl_reply_assr_final p

on z.zipcode = p.sa_site_zip

Anyway, that's a shot at it. Good luck...

Regards,

Bob Monahon

Bose C S SSC Rookie Points: 45 More actions · Answer 9

Try to do this as follows

Exec(' select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'''+@dbname +''','''+@@servername +''' as servername, county'''+@countyname+''' from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip' )