Please help me figure out why this is wrong

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help me figure out why this is wrong

Author

Topic

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 17:58:48

Can anyone tell me why this syntax won't work?

declare @FirstDayOfMonth varchar(10)
declare @charmo int
declare @mo int
set @mo = 1
while @mo <= 12
begin
if @mo <= 9
set @charmo = '0' + cast(@mo as char(1))
else
set @charmo = cast(@mo as char(2))
set @FirstDayOfMonth = @charmo + '-01' + cast(year(getdate())
as char(4))
declare @results table
insert @results values (@FirstDayOfMonth)
set @mo = @mo + 1
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 18:03:53

You haven't specified the table layout for @results.

DECLARE @results table (...) -- perhaps FirstDayOfMonth varchar(10)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 18:07:07

I'm not sure how to do a table layout. This is the error I am recieving:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'insert'.

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 18:10:15

Putting @FirstDayOfMonth varchar(10) didn't do any good.
Anything else I should try?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 18:11:10

I showed it in my last post. Just replace ... with what you want to name the column and your data type. I even showed an example after the --.

declare @results table (firstdayofmonth varchar(10))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 18:13:25

Then it gives me this error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@FirstDayOfMonth'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 18:21:02

I'm not sure what you did, but what I posted does not error.

Could you post your updated code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 18:27:44

Okay, I was putting the @ symbol into the layout. That is why it would not work. Now, though, can you show me how to print this information? here is the final code:

declare @FirstDayOfMonth varchar(10)
declare @charmo int
declare @mo int
set @mo = 1
while @mo <= 12
begin
if @mo <= 9
set @charmo = '0' + cast(@mo as char(1))
else
set @charmo = cast(@mo as char(2))
set @FirstDayOfMonth = @charmo + '-01' + cast(year(getdate())
as char(4))
declare @results table (FirstDayOfMonth varchar(10))
insert @results values (@FirstDayOfMonth)
set @mo = @mo + 1
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-15 : 18:29:15

SELECT * FROM @results

Put it after the loop, so it's the last line of your code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ischenk
Starting Member

15 Posts

Posted - 2008-07-15 : 18:39:47

Thank you so much. It worked perfectly. I am still in the stages where scripting confuses me. This was a huge help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources