Stored Proc Novice Help

  • Hi all,

    I'd be really grateful if someone could help me (probably something very basic I'm missing!)

    I'm trying to create a stored procedure (code below) but it's failing. If I execute the create table, insert into, select and drop statements as a batch the code runs perfectly but when I put it in a create proc statement it fails!! Can anyone help please?

    use tempdb

    go

    create proc dbo.DailyDBSizeCheck

    as

    create table #DatabaseSize

    (

    fileid int,

    groupid int,

    size int,

    maxsize int,

    growth int,

    status int,

    perf int,

    name varchar(50),

    filename varchar(100)

    )

    go

    insert into #DatabaseSize

    exec sp_msforeachdb @command1='select * from ?..sysfiles;'

    go

    select name as [DB File Name], filename as [DB File Path], size*8/1024 as [DB Size (MB)]

    from #DatabaseSize

    go

    drop table #DatabaseSize

    go


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • WWDMark (4/29/2013)


    Hi all,

    I'd be really grateful if someone could help me (probably something very basic I'm missing!)

    I'm trying to create a stored procedure (code below) but it's failing. If I execute the create table, insert into, select and drop statements as a batch the code runs perfectly but when I put it in a create proc statement it fails!! Can anyone help please?

    use tempdb

    go

    create proc dbo.DailyDBSizeCheck

    as

    create table #DatabaseSize

    (

    fileid int,

    groupid int,

    size int,

    maxsize int,

    growth int,

    status int,

    perf int,

    name varchar(50),

    filename varchar(100)

    )

    go

    insert into #DatabaseSize

    exec sp_msforeachdb @command1='select * from ?..sysfiles;'

    go

    select name as [DB File Name], filename as [DB File Path], size*8/1024 as [DB Size (MB)]

    from #DatabaseSize

    go

    drop table #DatabaseSize

    go

    Take out the GO statements. GO is a batch separator, not a T-SQL command.

  • Lynn Pettis (4/29/2013)Take out the GO statements. GO is a batch separator, not a T-SQL command.

    Berk!! Thanks Lynn.


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

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

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