|
Piece of cake dude
Once you have the diagram:
Select all your tables (in the diagram
Right click over one table
Select Table View
Choose Standard or custom (You can also modify custom and define the info you want to show)
Alexei Rodriguez
|
|
|
|
|
Also, i cant beleive you just finished designing a big DB and you dont know how to create a diagram :p
Alexei Rodriguez
|
|
|
|
|
Next question will be "Why can't I save the diagram, I get a foriengn key violation" 
And then what is a clustered index.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Im still fairly new to stored procedures but have reached a point where i need to use a conditional statement. At present i have to code below which will not compile due to various errors.
Please can you help me understand how i can make this work. Thanks;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
IF EXISTS (SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default')=1
SELECT * FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID
DELETE FROM tbl_AddressBookAddresses WHERE tbl_AddressBookAddresses.adds_ABMId = tbl_AddressBookMaster.addMaster_Key;
ELSE
'Do something else
END
Essentially what i want to do is;
1)Check if a record exists based on the input @tmp_custID(bigint) value.
2)If a record exists check a second table for records where the 'addMaster_UserID' column matches the 'addMaster_Key' of the initial table queried. Delete this records.
3)If the condition in item 1 isnt met, do something else.
Any help is correcting the above statement would be really appreciated.
Thanks.
|
|
|
|
|
Ok dude, let's have a shot.
in step 1, I presume you're checking for a unique record, in which case:
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
will get the key if ones exists. It will either contain your key or will be null, so to see if a record did match we can just do
if @key is not null
to do the delete is straightforward
delete from tbl_AddressBookAddresses where Key = @key
so, all together you'd have something like this
declare @key [datatype of key]
select @key = [key field] from tbl_addressBookMaster where .....
if @key is not null
delete from tbl_AddressBookAddresses where Key = @key
else
-- do your other thing here
Make any sense?
Regards,
Rob Philpott.
|
|
|
|
|
Hey Rob,
Looks like that should be what im after.
It'll probably be morning before i test it though.
Ill let you know how i get on.
Thanks!!!!
|
|
|
|
|
Rob,
Ive adapted my SP to the following but it wont compile as it says there is a syntax error near the 'else' bit;
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing here
|
|
|
|
|
Ok,
SO ive updated at, and it seems to be working ok although not fully tested. Does this look correct?
CREATE PROCEDURE web.InsertManualAddressBook
(
@tmp_custID bigint
)
AS
BEGIN
declare @key bigint
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'
IF @key IS NOT null
DELETE FROM tbl_AddressBookAddresses WHERE adds_ABMId = @key;
ELSE
-- do your other thing here
SELECT @@ROWCOUNT
END
Thanks!!!!!!!!
|
|
|
|
|
Looks good! Not sure the point of the @@rowcount, but I'm sure these is one.
Regards,
Rob Philpott.
|
|
|
|
|
@@rowcount was purely to show that the 'Else' condition was being fired.
Thanks for all the help, really appreciate it.
|
|
|
|
|
Rob,
Ive started to develop things a bit more now towards where i need to be going.
However im now having trouble with something a bit more complex, please take a look at the new thread:
<a href="http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx">
http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx
[^]
Thanks!
|
|
|
|
|
Good day All
I have a Challenge. I have the Following StoredProcedure that is doing the Following
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp]'))
drop table [temp]
--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
into temp FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID
All this takes less than a second with (17672 row(s) affected)
and its Cool and it Bring records like this
ID DESCR CYCLETEMPLATE
===============================
7620 Campbell P Dr 26
7620 Campbell P Dr 27
7620 Campbell P Dr 28
7620 Campbell P Dr 29
7620 Campbell P Dr 31
7621 Jones D Dr 23
7621 Jones D Dr 24
7621 Jones D Dr 26
7621 Jones D Dr 28
7621 Jones D Dr 29
7621 Jones D Dr 33
7621 Jones D Dr 34
This is Cool, So now i want to Have one[B] Campbell P Dr[/B] wilth all the [B]CycleTemplate [/B] Feld on one line and not Duplicated and sepated by a "," So in Simple it Should be like this
ID DESCR CYCLETEMPLATE
===============================
7620 Campbell P Dr 26,2728,29,31
7621 Jones D Dr 23,24,26,28,29,33,34
So to do this i created a user defined function to Remove the Duplicates in a Table Level, the Function looks like this
ALTER FUNCTION [dbo].[DistinctList]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX)
DECLARE @Pos INT
DECLARE @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList
VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
And the above function remove the first above mentioned problem and place the cycletemplate like this. Now
ID DESCR CYCLETEMPLATE
===============================
7620 Campbell P Dr 26,27,28,29,31,26,26,,28,28
7621 Jones D Dr 23,24,26,28,29,33,34,34,34,34,34
Now as you can see the Duplicates on the Row level are removed but not the Field level are Still there. So i created the Following User Defined Function that Removes the Duplicates in a Field Level like this
ALTER FUNCTION [dbo].[GetCycle_Timetable] (@Descr Varchar(50))
RETURNS Varchar(500)
AS
BEGIN
Declare @RetStr as varchar(500)
DECLARE @Cycle Int --<-- Assuming Cycle field is of Type Integer
--Creating a Cursor--
Declare TmpCur Cursor For
select CyCleTEMPLATE From temp Where Descr = @Descr
Open TmpCur --open the cursor
Set @RetStr='' --initialize the string to nothing
Fetch Next
From TmpCur Into @Cycle --take the cycles into the cursor variable
While @@Fetch_status=0
Begin
Set @RetStr = @RetStr +
Case when @RetStr=''
then
'' else
' ' End
+ Cast(@Cycle as varchar)
Fetch Next From TmpCur Into @Cycle
End
Close TmpCur
Deallocate TmpCur
return (@RetStr)
END
and my sp i conbine this and Call it like this
Select DISTINCT Descr AS [Staff],[B]dbo.DistinctList[/B](.dbo.[[B]GetCycle_Timetable[/B]](Descr),'') As [Cycles]
into Temp2 From temp
and it worked Perfectly and brought desired Results as i shown in the Beginning. Now My Problem with this it Runs for 3 Minutes and in an ASP.net page it times out.
Is there another way that i could have dont this ?
Please Help me with your Example Code by Changing the statement in your way.
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Depending on how mad you are, you could create an aggregate function which comma appends the list of 'cycletemplate's together. You'd need to do this as a managed stored procedure written in a .NET language though. You could then use this function with a GROUP BY clause.
I wouldn't fancy doing it though.
Regards,
Rob Philpott.
|
|
|
|
|
One area where you are paying dearly is the cursor (they are EVIL) in the function, I use the following to concat string lists.
SET @List = ''
SELECT @List = @List + CASE WHEN @List = '' THEN '[' + AttrType + ']' ELSE ', [' + AttrType + ']' end
FROM @TblAttr
You need to convert the numerics and remove the [] brackets. You can see it in use in this article[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi.. to all
I want to schedule a job(want to execute a query to delete a table)which is execute after a sometime like a repeated task.
Can any one give Step by Step solution how can i do it with Server Agent in SQL Server 2005.
Thanks
|
|
|
|
|
Open your SQL Server Management Studio
Expand SQL Server Agent, Right click on Job and click new job and follow the on screen instructions
|
|
|
|
|
And we don't want to let Mister T-SQL feel left out...
Execute sp_add_job to create a job.
Execute sp_add_jobstep to create one or more job steps.
Execute sp_add_schedule to create a schedule.
Execute sp_attach_schedule to attach a schedule to the job.
Execute sp_add_jobserver to specify the target servers on which the job is to run.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi
Simple question, I assume. In SQL tables, what is Scalar and Navigation properties? And what is the use?
Thanks
|
|
|
|
|
|
Why you are asking straightforward question which can be available in the net.
Are you testing others?
Niladri Biswas
|
|
|
|
|
Oh well! what made you think that I didn't look around. Maybe what I read elsewhere was not easy to grasp.
It would be suffice to say that your response is entirely against the essence of this forum. Not only your's and my time got wasted but of others too who will read all this. Please refrain from such thoughts and instead provide the relevant response if you know.
Thanks anyway!
|
|
|
|
|
Hi,
I'm looking for an Open Source Automatic Database Control System - that is a system that does automatic check-ins of database objects and its data (e.g. once a day) into CVS/SVN, and thus allows keeping track of all the changes done to the database.
Do you know of such a system?
Thanks
Yuval
"The true sign of intelligence is not knowledge but imagination." - Albert Einstein
|
|
|
|
|
What you are looking for is a version managed relational database. I used to use a system called Smallworld that allowed version management of the database.
For everyone else, you create audit tables. i.e. tables that look like the ones you already have but with additional auditing columns such as change timestamp, username, etc.
Man who stand on hill with mouth open wait long time for roast duck to drop in
|
|
|
|
|
I want to do a JOIN on two tables, and return all the values from that JOIN where the SS.TIME_RECCEIVED is the MAX value. I have got the below SQL so far, but this only returns the two columns, i need them all. I tried doing SS.* but i a get
ORA-00979: not a GROUP BY expression<br />
SELECT SS.SERVICE, MAX(SS.TIME_RECEIVED)
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME
GROUP BY SS.SERVICE
ORDER BY SS.SERVICE ASC
Any ideas?
On a side note, i've tried the below, but keep getting ORA-01722: invalid number, but if a just do a SELECT MAX() on its own, it does not complain about invalid number.
SELECT *
FROM ISS_BSM_SVCSTATES_Table SS
INNER JOIN ISS_BSM_GeoLocation_Table GEO
ON SS.SERVICE = GEO.SERVICE_NAME
WHERE SS.SERVICE = (SELECT MAX(SS.TIME_RECEIVED) FROM ISS_BSM_SVCSTATES_Table WHERE SS.SERVICE = GEO.SERVICE_NAME)
Regards,
Gareth.
(FKA gareth111)
modified on Wednesday, June 10, 2009 9:12 AM
|
|
|
|
|
Problem solved:
select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geo
where ss.service = geo.service_name and ss.time_received in
(select max(ss1.time_received) from iss_bsm_svcstates_table ss1 group by ss1.service)
Regards,
Gareth.
(FKA gareth111)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
