Stored Proc Novice Help

Question

Stored Proc Novice Help

WWDMark

Hall of Fame

Points: 3157
More actions
April 29, 2013 at 9:58 am

#273379

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

WWDMark Hall of Fame Points: 3157 More actions · Answer 2

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