Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Stored Procedure Question

    Hi All,

    I have a stored procedure that when try to compile it gives me the following error:

    Server: Msg 170, Level 15, State 1, Procedure usp_DBGrowth1, Line 26
    Line 26: Incorrect syntax near '.'.
    Server: Msg 170, Level 15, State 1, Procedure usp_DBGrowth1, Line 31
    Line 31: Incorrect syntax near '.'.


    Here is the stored procedure:

    ALTER PROCEDURE usp_DBGrowth1

    AS

    DECLARE @var1 DEC(15,0)
    DECLARE @var2 DEC(15)
    DECLARE @var3 SYSNAME


    DECLARE dbnames_cursor CURSOR
    FOR
    SELECT name FROM sysdatabases
    ORDER BY name

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @var3

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT @var1 = SUM(CONVERT(DEC(15),size))
    FROM @var3.dbo.sysfiles
    WHERE fileid = 1


    SELECT @var2 = SUM(CONVERT(DEC(15),size))
    FROM @var3.dbo.sysfiles
    WHERE fileid = 2


    SELECT dbsize = LTRIM(STR((@var1 + @var2)*8/1024,15,2))

    FETCH NEXT FROM dbnames_cursor INTO @var3
    END

    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor


    What am I missing?

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by inka
    FROM @var3.dbo.sysfiles
    You can't use a variable as database name.

    To do something like this you will need to use dynamic SQL (See sp_executesql in BOL).

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by jezemine
    also read this
    Yep, that about sums it up... and more! A very good article!

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it's a classic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •