Help with declare

  • 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

  • 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)

  • 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

  • You have @dbname inside of your dynamic script.

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

    _____________
    Code for TallyGenerator

  • 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

  • Your right.. I'm learning..

    Any tips?? Or to many to mention?

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

  • 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

  • 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)

  • 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,


    Regards,

    Bob Monahon

  • 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' )

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

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