|
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)
|
|
|
|
|
Still another way.
select * from iss_bsm_svcstates_table ss, iss_bsm_geolocation_table geowhere ss.service = geo.service_name and ss.time_received in
(select top 1 ss1.time_received from iss_bsm_svcstates_table ss1 where ss1.time_received = ss.time_received order by ss1.service desc)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
I am also ask this Question many time but not getting Proper Answer.
What is the Use Of database Certificate(in case of Security).
how to create and use it to in Database Security.
pls Help Me
Thank u
Dinesh Sharma
|
|
|
|
|
Dear All,
I have sql query which is
create proc sp_detail
(
@p varchar(max)
)
as
select * from tblName where code in (@p)
go
and i am calling it like following
declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes
this doesent work, while i have records in table.
i want to do like this
select * from tblName where code in ('28-2801','28-2802',....)
how can i acheieve it in procedure and how do i pass such thing to procedure?
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Good Morning Abdul
Stop Stop Stop Stop Stop
You created a SP called sp_detail
and you said you calling it in the Seconds statements, but there is no place in the second statement you are using the created sp.
i don't see the created Sp has anything to do with
declare @codes varchar(max)
select @codes=cast(quotename('28-2801','''')+','+quotename('28-2802','''') as varchar(max))
print @codes
exec [sp_getCommunityDetail] @codes
please before you post make sure you are posting the right things, it seems you posted the wrong Sp
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/
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
