November 17, 2009 at 4:35 am
Hi,
I have created a script that will populate a table dynamically. The script will create the insert statement with values of the days of the week in french and english.
but...
There are two curious things going on.
1. The script will not work with an exec statement but will work if I print the dynamic code and then copy and paste it into a new window. If I execute the dynamic code directly after building it, I get the error "The name Set Language is invalid etc".
2. When I copy and paste the output of the dynamic code to a new window the word LANGUAGE losses it's reserved word status and I have to manually type "language" again for management studio to recognise it as a reservered word. I suspect this is just a bug in management studio, what do you think?
Ideally I would like to have an automated script, but I can settle with copying and pasting the output to a new window.... do you have any suggestions?
I've written it with two while loops, i'm sure a better way using recursive CTEs is possible but I just wanted to get a basic version working first.
drop table languages
drop table daytime
create table languages (id int, name varchar(20))
insert into languages select 1, 'english'
insert into languages select 2, 'french'
create table daytime (id int identity, daytime varchar(20))
declare
@i int,
@language varchar(20),
@sql nvarchar(2000),
@lt-2 char(2),
@x int,
@date datetime
select @sql = ''
select @lt-2 = CHAR(9) + CHAR(10)
select @i = min(id) from languages
while @i is not null
begin
select @language = name from languages where id = @i
select @sql = @sql + 'set langauge ' + @language + @lt-2 + 'GO' + @lt-2
select @x = 1
select @date = '01/01/2009'
if @language = 'english'
set language english
else if @language = 'french'
set language french
while @x <= 7
begin
select @sql = @sql
+ 'insert into daytime' + @lt-2
+ 'select datename(weekday, '''+ cast(@date as varchar) +''')' + @lt-2
+ 'GO' + @lt-2
select @date = @date + 1
select @x = @x + 1
end
select @i = min(id) from languages where id > @i
end
print @sql
exec @sql
select * from daytime
The printed statement.... which I copy into a new window and does work.
Notice the word Language is not highlighted as a reserved word. If you replace the existsing text with the same word it will become highlighted again.
drop table daytime
create table daytime (id int identity, daytime varchar(20))
set langauge english
GO
insert into daytime
select datename(weekday, 'Jan 1 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 2 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 3 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 4 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 5 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 6 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'Jan 7 2009 12:00AM')
GO
set langauge french
GO
insert into daytime
select datename(weekday, 'janv 1 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 2 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 3 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 4 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 5 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 6 2009 12:00AM')
GO
insert into daytime
select datename(weekday, 'janv 7 2009 12:00AM')
GO
Please help
Thanks,
Lewis
November 17, 2009 at 5:30 am
Somewhere you have a typo...
What you have:
set langauge english
vs. what you should have:
set language english
langauge is no reserved word. 😉
November 17, 2009 at 5:45 am
Thanks that helps....
but
I still have the issue where I can not execute the dynamic string after building it. I still have to copy and paste the content to another window to execute it.
Any ideas?
Thanks
November 17, 2009 at 7:41 am
There are two issues:
#1 get rid of the 'GO' command within your dynamic sql and
#2 use exec(@sql) instead of plain exec @sql (sometimes parenthises matter...)
November 17, 2009 at 8:07 am
check if this works for you.........changed your code a bit to make it work..
drop table languages
drop table daytime
create table languages (id int, name varchar(20))
insert into languages select 1, 'english'
insert into languages select 2, 'french'
create table daytime (id int identity, daytime varchar(20))
declare
@i int,
@language varchar(20),
@sql nvarchar(2000),
@lt-2 char(2),
@x int,
@date datetime
select @sql = ''
select @lt-2 = CHAR(9) + CHAR(10)
select @i = min(id) from languages
while @i is not null
begin
select @language = name from languages where id = @i
select @sql = @sql + 'set language ' + @language + @lt-2
select @x = 1
select @date = '01/01/2009'
if @language = 'english'
set language english
else if @language = 'french'
set language french
while @x <= 7
begin
select @sql = @sql
+ 'insert into daytime' + @lt-2
+ 'select datename(weekday, '''+ cast(@date as varchar) +''')' + @lt-2
select @date = @date + 1
select @x = @x + 1
exec(@sql)
end
select @i = min(id) from languages where id > @i
end
select * from daytime
November 17, 2009 at 8:11 am
Perfect thanks, just had to move the exec statment out of the loop and it worked perfectly.
Thanks all
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply