May 10, 2012 at 5:43 am
Hi I hope someone can help
I am reletively new to SQL and i have wrote the following basic procedure to check if a field in my table is blank if it is return a select statement else run some updates
This is what i have come up with however it is niot returning anything
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[USP_Check_For_Blanks]
@Name varchar (50)
As
Begin
If @Name = ''
begin
Select Extension, Name
From dbo.DailyBchDataInitialInbound
Where Name = ''
End
Else
set dateformat DMY
Update dbo.DailyBchDataInitialInbound Set BchCode = Substring(Name,5,3) WHERE Name Like '%bch%' Or Name Like '%Com%'
Update dbo.DailyBchDataInitialInbound Set BchCode = Null WHERE BchCode Like '%omb%' Or Name Like '%erc%'
Update dbo.DailyBchDataInitialInbound Set CallDate = convert(date,(left(CallTimeDate,8)))
Update dbo.DailyBchDataInitialInbound Set CallTime = convert(time,(RIGHT(CallTimeDate,9)))
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) In ('2','3','4','5','6') And CallTime Between '09:00:00 Am' and '17:30:00 Pm'
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) = '1' And CallTime Between '09:00:00' and '13:00:00'
END
Go
-----------------------------------------------------------------------------------------------
USP_Check_For_Blanks 'name'
------------------------------------------------------------------------------------------
Although if i run just the selct statement it does return data , would anyone know where im going wrong
Many thanks
May 10, 2012 at 5:52 am
-----------------------------------------------------------------------------------------------
USP_Check_For_Blanks 'name'
------------------------------------------------------------------------------------------
Are you using this script to call your SP?
If "Yes", then you are passing a non-blank parameter and the updates will be executed and no results will be displayed
Try the below script
USP_Check_For_Blanks ''
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 10, 2012 at 11:41 pm
I think Kingston has nailed your question. You say these are your UPDATEs (formatted for readability):
Update dbo.DailyBchDataInitialInbound
Set BchCode = Substring (Name,5,3)
WHERE Name Like '%bch%' Or Name Like '%Com%'
Update dbo.DailyBchDataInitialInbound
Set BchCode = Null
WHERE BchCode Like '%omb%' Or Name Like '%erc%'
Update dbo.DailyBchDataInitialInbound
Set CallDate = convert(date,(left(CallTimeDate,8)))
Update dbo.DailyBchDataInitialInbound
Set CallTime = convert(time,(RIGHT(CallTimeDate,9)))
Update dbo.DailyBchDataInitialInbound
Set BchHrsFlag = 1
where datePart(dw,CallDate) In ('2','3','4','5','6') And CallTime Between '09:00:00 Am' and '17:30:00 Pm'
Update dbo.DailyBchDataInitialInbound
Set BchHrsFlag = 1
where datePart(dw,CallDate) = '1' And CallTime Between '09:00:00' and '13:00:00'
Now for my questions:
1. Why are you running some of these UPDATEs without a WHERE clause? Your intention is to update all rows in the table?
2. Why are you running 6 UPDATEs when 1 will do?
UPDATE s
SET BchCode = CASE WHEN Name LIKE '%bch%' Or Name Like '%Com%' THEN SUBSTRING(Name,5,3)
WHEN NBchCode Like '%omb%' Or Name Like '%erc%' THEN SUBSTRING(Name,5,3)
ELSE name END
,CallDate = convert(date,(left(CallTimeDate,8)))
,CallTime = convert(time,(RIGHT(CallTimeDate,9)))
,BchHrsFlag = CASE WHEN (datePart(dw,CallDate) In ('2','3','4','5','6') And CallTime Between '09:00:00 Am' and '17:30:00 Pm')
OR (datePart(dw,CallDate) = '1' And CallTime Between '09:00:00' and '13:00:00')
THEN 1 ELSE BchHrsFlag END
FROM dbo.DailyBchDataInitialInbound s
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 8:51 am
mark.doyle (5/10/2012)
Hi I hope someone can helpI am reletively new to SQL and i have wrote the following basic procedure to check if a field in my table is blank if it is return a select statement else run some updates
This is what i have come up with however it is niot returning anything
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[USP_Check_For_Blanks]
@Name varchar (50)
As
Begin
If @Name = ''
begin
Select Extension, Name
From dbo.DailyBchDataInitialInbound
Where Name = ''
End
Else
set dateformat DMY
Update dbo.DailyBchDataInitialInbound Set BchCode = Substring(Name,5,3) WHERE Name Like '%bch%' Or Name Like '%Com%'
Update dbo.DailyBchDataInitialInbound Set BchCode = Null WHERE BchCode Like '%omb%' Or Name Like '%erc%'
Update dbo.DailyBchDataInitialInbound Set CallDate = convert(date,(left(CallTimeDate,8)))
Update dbo.DailyBchDataInitialInbound Set CallTime = convert(time,(RIGHT(CallTimeDate,9)))
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) In ('2','3','4','5','6') And CallTime Between '09:00:00 Am' and '17:30:00 Pm'
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) = '1' And CallTime Between '09:00:00' and '13:00:00'
END
Go
-----------------------------------------------------------------------------------------------
USP_Check_For_Blanks 'name'
------------------------------------------------------------------------------------------
Although if i run just the selct statement it does return data , would anyone know where im going wrong
Many thanks
You are missing BEGIN and END inside your else. This will never return anything because the last statement is always an update.
ALTER PROCEDURE [dbo].[USP_Check_For_Blanks]
@Name varchar (50)
As
Begin
If @Name = ''
begin
Select Extension, Name
From dbo.DailyBchDataInitialInbound
Where Name = ''
End
Else
BEGIN
set dateformat DMY
Update dbo.DailyBchDataInitialInbound Set BchCode = Substring(Name,5,3) WHERE Name Like '%bch%' Or Name Like '%Com%'
Update dbo.DailyBchDataInitialInbound Set BchCode = Null WHERE BchCode Like '%omb%' Or Name Like '%erc%'
Update dbo.DailyBchDataInitialInbound Set CallDate = convert(date,(left(CallTimeDate,8)))
Update dbo.DailyBchDataInitialInbound Set CallTime = convert(time,(RIGHT(CallTimeDate,9)))
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) In ('2','3','4','5','6') And CallTime Between '09:00:00 Am' and '17:30:00 Pm'
Update dbo.DailyBchDataInitialInbound Set BchHrsFlag = 1 where datePart(dw,CallDate) = '1' And CallTime Between '09:00:00' and '13:00:00'
END
END
Go
Also as Dwain said, your updates have no where clause which means you will be updating the entire table. Additionally there is no need to run all these updates as individual statements. Combine them into a single update.
update table
set cola = vala, colb = valb, etc...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 9:05 am
Thank you all for your help , as i am new to sql i was not sure on how to run the updates as one update rather than six indivisual ones i will amend my code to include the one update
many thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply