January 3, 2012 at 10:50 am
This is what I have so far:
DECLARE @DB_NAME AS sysname, @DB_ID AS INT
SET @DB_NAME = (SELECT name FROM sys.databases
WHERE database_id='1');
SET @DB_ID = 1
WHILE @DB_ID <= (SELECT MAX (database_id)
FROM sys.databases)
BEGIN
PRINT @DB_NAME;
SET @DB_NAME = @DB_NAME
END
This is what I need:
Write a script that performs the following tasks. The script will need to use
variables and WHILE.
a. Perform b. and c. for each database. Start with the database having database ID of 1, then
2, and continue until you reach the maximum database ID value. Use your code from
problem 1, parts a. and b. to determine the value for the maximum database ID and place
its value in a variable. Your WHILE loop should stop when your loop counter reaches the
value of that variable.
b. Determine the name of the database (and cause a variable of datatype sysname to have a
value of that name). Hint: Use the DB_NAME function.
c. Construct a character string that says 'Database with ID <value> is <database_name>'.
(See first row of the result below.) A variable should be declared before the WHILE loop,then constructed in the loop using concatenation hold this string as its value. (Hint: Your
WHILE loop counter, which is acting as the database ID value, will have to be CAST as
character data when concatenating its value into your string variable.) Use PRINT to print
the variable.
Result (Messages tab):
Database with ID 1 is master
January 3, 2012 at 10:53 am
You should try to figure this out for yourself. If we do your homework for you you will not learn anything, and subsequently you will never be in competition for a job with someone like me.
As a hint, this can easily be accomplished using a cursor based on a simple select statement ordered by dbid.
The probability of survival is inversely proportional to the angle of arrival.
January 3, 2012 at 11:05 am
Do you at least know what's wrong with the sql statement you wrote? Read up on the WHILE statement if you're not familiar with it.
January 3, 2012 at 11:06 am
on top of what sturner said about doing homeowrk, there's a logical error in there using a while loop.
it's very possible that a database does not exist for every id between the first and max id you are looping through. keep that in mind.
Lowell
January 3, 2012 at 11:13 am
Lowell (1/3/2012)
on top of what sturner said about doing homeowrk, there's a logical error in there using a while loop.it's very possible that a database does not exist for every id between the first and max id you are looping through. keep that in mind.
You could get around that by selecting the min database ID higher than the current value, instead of incrementing by 1 each time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2012 at 11:14 am
I fixed my WHILE loop. I have 14 databases.
DECLARE @DB_NAME AS sysname, @DB_ID AS INT
SET @DB_NAME = (SELECT name FROM sys.databases
WHERE database_id='1');
SET @DB_ID = 1
WHILE @DB_ID <= (SELECT MAX (database_id)
FROM sys.databases)
BEGIN
PRINT @DB_ID;
SET @DB_ID = @DB_ID+1;
END
This gives me the DB ID's from 1-14 which is the max. Problem I need help with is getting the DB Name and DB ID in the String to Print
January 3, 2012 at 11:19 am
Well you fixed the iteration problem. Outputing the text in the format you specified is actually the easy part. You already have the data you need (most of it) and its looping right and you already know how to set the @DB_NAME variable.
January 3, 2012 at 11:23 am
This doesn't comply with all the rules you have, but it should point you in the right direction. Modify this to fit your rules, and you'll be okay. But turn it in as-is and you'll fail the question.
If you have questions about it, feel free to ask. Or take it to your professor and tell him you need some help on this one, and this is what you got from someone online. Honesty goes a long ways.
DECLARE @DB_NAME AS NVARCHAR(100),
@DB_ID AS INT = (SELECT MIN(database_id)
FROM sys.databases) ;
WHILE @DB_ID <= (SELECT MAX(database_id)
FROM sys.databases)
BEGIN
SELECT @DB_NAME = N'Database with ID ' + CAST(@DB_ID AS NVARCHAR(10))
+ N' is ' + DB_NAME(@DB_ID) + N'.' ;
PRINT @DB_NAME ;
SET @DB_ID = (SELECT MIN(database_id)
FROM sys.databases
WHERE database_id > @DB_ID) ;
END ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply