T-SQL (SS2K8) :: Update With Case Statement Not Working?

May 29, 2014

I have a situation where I want to update a column if and only if it is null.

UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID

But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.

So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Using CASE Statement Within A PIVOT

Jun 17, 2014

I am using a PIVOT function to obtain the Invoice Values, but they appear in different currencies so need to perform a case function.

But am struggling with the syntax;

This fails a syntax check with
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'Case'.

[Code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Statement Using Case And Join Together

Jan 29, 2015

I am trying to use this logic into a query:

Select P.S,E.S,E.R
from Pack P(nolock)
join Exp E on P.Id=E.O
on E.R is null
case when E.R is not null then ''
else ''
end
where P.s='PLT000044'

I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.

View 2 Replies View Related

T-SQL (SS2K8) :: Case Statement In The Where Clause?

Jun 5, 2015

I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.

I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.

A small bit of the code:

declare @get_date datetime = convert(char(10),getdate(),101)
select top 10 pt.pt_id, pt.last_name
fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id
and a.pt_id = pt.pt_id
and a.from_file_ind = pt.from_file_ind

[Code] ....

Seems I should be able to not test the last name after the turnover date, but I can't figure out how.

View 2 Replies View Related

T-SQL (SS2K8) :: Using Case Statement In Pivot?

Jun 23, 2015

Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.

select (
Column1
,Column2
,Column3
,Column4
,coloumn5
from Mytable
) x
pivot
(
case when Column1 = 6 then sum(Column3) else max(Column4) End
for coloumn5 in (' + @COLS + ')
)p

View 2 Replies View Related

T-SQL (SS2K8) :: How To Execute SP In Select Case Statement

Mar 20, 2014

I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.

Anyways, I have a table which is dba.dbhakyedek.

Columns are

dbname, username, class_desc, object_name, permission_name, state_desc

I have statement

select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'
end
from dba.dbhakyedek
where username='MC'

This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.

View 6 Replies View Related

T-SQL (SS2K8) :: Dividing Two Rows Within CASE Statement

Jul 28, 2014

I was given the task to come up with a result set based on certain criteria:

Please add one row for each offer code

1) Opt-in rate by offer code: This can be calculated by dividing XXX Inventory with lead / XXX Inventory (A)

The report should read something like below:

Example result set:

Log Date: OfferLetter OfferCode DailyCount

2014-07-20 A XXX Inventory (A) 108
2014-07-20 A XXX Inventory with lead 54
2014-07-20 A XXX Inventory Opt-in Rate: 50%

There are 12 different groupings and OfferLetter A is just one of them.

Below is the code that is written to date:

DECLARE @Start datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 8), 0)
DECLARE @End datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1) + 1, 0)
DECLARE @Today datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1), 0);

SELECTDT.OfferCode, DT.OfferCodeDesc
INTO#tempOfferCodes

[Code] ....

The issue I'm having is that the values I need to divide by are in fact, a result set from the CASE statement. It's been a long time since I've done anything like this.

View 2 Replies View Related

T-SQL (SS2K8) :: Using A Date Comparison In A Case Statement?

Mar 19, 2015

I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:

Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'

[Code] ....

When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.

Here is the full piece that the Case resides in:

Insert _Test
SELECT
EmpNo,
PersonIdNo,
REPLACE(PersonTaxIdNo,'-',''),
LastName,
FirstName,

[code]......

View 3 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

CASE In Update Statement

Oct 13, 1999

Is it possible to use CASE in update statement? What is the syntax? I need to pass parameter @week, when @week = 0, 2, 4, then field = @Status and so on
Thanks.

View 2 Replies View Related

Update, Case Statement And Sum

Jul 20, 2005

I would like to update a decimal column in a temporary table based ona set of Glcodes from another table. I search for a set of codes andthen want to sum the value for each row matching the Glcodes. Theproblem is I keep getting multiple rows returned errors."Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated."This is correct as there can be many rows matching the Glcodes foreach iteration of the case statement and I need to catch them all.I have posted some of the code below and would appreciate any help asI'm scratching my head over this one. It's all very much work inprogress again.Regards,DECLARE@CostCentreNVARCHAR(3)DECLARE@COIDNVARCHAR(3)DECLARE@TheYearNVARCHAR(5)DECLARE@PlorBSNVARCHAR(2)DECLARE@BusinessUnitNVARCHAR(50)DECLARE@BranchNVARCHAR(3)SET@CostCentre= 'xxx'SET@COID= 'inc'SET@TheYear= '2004'SET@PlorBS= 'x2'SET@BusinessUnit= 'PBUS'SET@Branch= ‘usa'CREATE TABLE #SummaryTempTable ([GLD_ACCTNG_PER] int,[Order Num] decimal(9,2),[Summary Description] varchar(50),[Summary Amount] decimal(9,2))INSERT INTO #SummaryTempTable VALUES(199999, 1.1, 'Tot Ext Sales',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 1.2, 'Tot Int Sales',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 1.3, 'Inter Mark Up',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.1, 'Tot Ext Costs',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.2, 'Tot Int Costs',0.0)INSERT INTO #SummaryTempTable VALUES(199999, 2.3, 'Inter Mark UpCharges', 0.0)UPDATE #SummaryTempTableSET [Summary Amount] = (SELECT sum(CASEWHEN ((ACT_GL_NO between '4000' and '4059') or (ACT_GL_NO between'4065' and '4999') or (ACT_GL_NO IN ('4062','4063'))) THEN GLD_TotalWHEN (ACT_GL_NO IN ('4060','4064')) THEN GLD_TotalWHEN (ACT_GL_NO = '4061') THEN GLD_TotalWHEN ((ACT_GL_NO between '5000' and '5059') or (ACT_GL_NO between'5065' and '5401') or (ACT_GL_NO IN ('5805','5806','5062','5063')))THEN GLD_TotalWHEN (ACT_GL_NO IN ('5060','5064')) THEN GLD_TotalWHEN (ACT_GL_NO = '5061') THEN GLD_TotalELSE 0END)FROM howco_dw_test.dbo.cubeFinancePeriodWHERE ([coid] = @COID) AND (GLD_SSN_BRH = @Branch) AND(GLD_ACCTNG_PER like @TheYear) AND ACT_GL_NO BETWEEN 4000 AND 9999AND GLD_CST_CTR IN ('008','021','031','041')GROUP BY ACT_GL_NO, GLD_ACCTNG_PER)

View 1 Replies View Related

HELP--UPDATE STATEMENT NOT WORKING

Mar 21, 2000

Hi,

I am trying to write an update query to update rows in one table.

Structure of Table1:

SocialSecurityNumber Varchar(9) -- Primary Key
Name Varchar(30)

Structure of Table2 :

SocialSecurityNumber Varchar(9)
Name Varchar(30)

Table 1 contains:

Row1: "123456789" Sally
Row2: "999999999" Bill
Row3: "333333333" Alex

Table 2 contains:

Row1: "123456789" <NULL>
Row2: "123456789" <NULL>

Basically I want to update the name column in Table 2 (based on the SocialSecurityNumber column) so that after
the update Table 2 will contain:

Row1: "123456789" Sally
Row2: "123456789" Sally
------------------------------------------------------------
First I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Then I tried:

UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name

Again this works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Finally I tried:

UPDATE Table2, Table1
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber

This also did not work.
------------------------------------------------------------
Is there any way to write this update statement without cursors?

Thanks.

View 1 Replies View Related

Update Statement Not Working

Oct 2, 2007

I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?


UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]

SET [LaborBudget_USD] = Week1

FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN

[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON

Labor.Location_Code = Budget.Location_Code

JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON

Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND

Date.FiscalPeriodOfYear = Labor.FiscalPeriodOfYear

WHERE Labor.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,1,4) AS NVARCHAR(50)) AND

Budget.FiscalYear = CAST(SUBSTRING(DATE.Date_Code,4,1) AS NVARCHAR(50)) AND

Date.FiscalWeekofYear = 1

View 6 Replies View Related

Update Statement Not Working

Apr 11, 2006

I've got the following update statement:

UPDATE ISSUE_ACTIONS
SET BAE_FLAG = 2
WHERE IA_ISSUE_NO = 399
AND IA_SEQUENCE = 20

The fields BAE_FLAG, IA_ISSUE_NO, and IA_SEQUENCE are all of the type int.

When I run this code inside of my windows app (C#),

cmd3.CommandText = "UPDATE ISSUE_ACTIONS " +
"SET BAE_FLAG = 2 " +
"WHERE IA_ISSUE_NO = 437 " +
"AND IA_SEQUENCE = 13";

try
{
cmd3.ExecuteNonQuery();
}
catch (Exception e)
{
throw (e);
}

I get a timeout error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

But when I run the SAME statement from Query Analyzer, it executes without a problem.

Has anyone run into this issue before? How do I get around this?

The CommandTimeout property of cmd3 is set to the default because this is not a complex query and should not take more than .5 seconds to execute.

View 5 Replies View Related

Using CASE With INSERT,UPDATE Statement

Mar 6, 2008

Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do
i have a table like this
Field1 Field2 Field3  FieldType1 FieldType2 FieldType3
1-when there is no data for a given combination of  Field2 and  Field3,i need to do "insert".i.e the very first time and only time
2-Second time,when there is already a row created for a given combination of Field2 and  Field3,i would do the update onwards from there on.
At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update
I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure
CREATE PROCEDURE dbo.StoredProcedure
 ( @intField1 int, @intField2 int, @intField3 int,  @intFieldValue int , @evalFieldName varchar(4)  )So i am trying something like
CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue)
END
similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing  stored procedure to insert/update for each individual fields..thanks a lot
 

View 8 Replies View Related

Update Set Select Case When Statement

Nov 15, 2013

Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.

How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..

VFP9.0 via MySQL 5.0

View 5 Replies View Related

Update Table With Case Statement

Aug 31, 2007

can plz anyone tell me how to fix the following update script. thanks

Update table
set rating =
case
when rating in
(select code from tbl_Codes
where code = '0')
then Rating = '0'

when Rating in
(select code from tbl_Codes
where code = '1')
then InternalRating = '1'
else rating
end

View 5 Replies View Related

Transact SQL :: Update Statement With Case

Nov 11, 2015

I am trying to run the below but I get an error of 'Incorrect syntax ')''  --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.

UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'

[code]....

View 5 Replies View Related

(URgent) My Update Statement Is Not Working..

Dec 17, 2007

Hi..
I have inserted couple of data in a particular table which is as follows..
Portfolio Table
PortId PlanId  PortfolioName                           PorfolioDescription  ClientPortolioId




771
17838
BALPORT                                           
NULL
NULL

772
17838
HIGHGROW                                          
NULL
NULL

773
17838
MODGROW                                           
NULL
NULLMy FundDBF is as follows
 RowNumber  FUND_ID            f.ASSETDESC                    Import




20
BALPORT        
              
Balanced                                      
True

21
MODGROW        
              
Moderate Growth                               
True

22
HIGHGROW       
              
High Growth                                   
True
and this is my Update statement UPDATE
Statements..PlanPortfolio
SET
PlanId = pm.PlanId,
PortfolioName = pm.FUND_ID,
PortfolioDescription = pm.ASSETDESC
FROM
Statements..PlanPortfolio p
Join (
SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNER JOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNER JOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_ID IS NULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_ID NOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)
) pm
on p.PlanId = pm.PlanId
 
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..
Any help will be appreciated..
Regards
Karen

View 17 Replies View Related

Complex Update Statement Not Working

Nov 6, 2006

hi.

can somebody explain to me why the below
update fails to update one row and updates
the entire table?


Code:


UPDATE addlist
SET add_s = 1
WHERE EXISTS
(SELECT a.add_s, a.email_address, e.public_name
FROM add a, edit e
WHERE a.email_address = e.email_address
and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');



and, what is the solution? thank you.

View 8 Replies View Related

Transact SQL :: Case Statement In Update Clause

Jun 4, 2015

I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type  = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
 
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC'  THEN  (c.volume)
 WHEN c.TYPE = 'DEF'  THEN  (c.volume)
 WHEN c.Type = 'GHI'  THEN  (c.volume)
 Else 0
 END),
 CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
 WHEN c.Type = 'ABC' THEN '123.1'
 WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
 from table1 a join table2 b
 on a.Cust = b.Customer
 join table3 c
 on b.account = c.account and b.channel =c.channel

Why its not working properly? But if i use Select statement instead of update query its working properly.

View 18 Replies View Related

Transact SQL :: Using Case Statement With Update Query

Jun 22, 2015

I have a table A  and lookup table B.

table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
|  1  | orange | cabbage     |  no   |
|  1  | apple  | lettuce        |  yes   |
|  1  | kiwi     | broccoli      |  no   |
|  1  | pear    | kale           |  yes   |

table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1  | apple  |  lettuce       |
| 2  | pear    |   kale         |

If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.

This is what I have so far.

update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C

View 6 Replies View Related

Update Multiple Columns In One Table With Case Statement

Nov 15, 2013

I want to update multiple column in one table using with case statement. i need query pls..

stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL

View 2 Replies View Related

T-SQL (SS2K8) :: Generate Dynamic Update Statement

Aug 14, 2014

I'm trying to generate an update statement based off select statement results. A very basic example,

SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456

How can I take those results and turn them into WHERE criteria for UPDATE statement?

Generated UPDATE statement should look like this.

UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)

I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.

CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate

View 6 Replies View Related

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

Aug 11, 2007



here is my code:


Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View 5 Replies View Related

Stored Procedure To Update A Table Using Parameterized CASE Statement - Erroring Out

May 2, 2008

I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.


CREATE PROCEDURE OfficeMove

-- Add the parameters for the stored procedure here

@UserName nvarchar(10),

@NewLocation nchar(5),

@NewCity nvarchar(250)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Execute as user = '***'

DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments

WHERE User_Name = @UserName

INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments

SET User_Name = @UserName,

Room_ID = @NewLocation

UPDATE [SQLSZD].[SZDDB].dbo.Employee_Locations

SET Office_ID =

CASE

WHEN @NewCity = 'Columbus' THEN 1

WHEN @NewCity = 'Cleveland' THEN 2

WHEN @NewCity = 'Cincinnati' THEN 4

WHEN @NewCity = 'Raleigh' THEN 5

WHEN @NewCity = 'Carrollwood' THEN 6

WHEN @NewCity = 'Orlando' THEN 7

END

WHERE User_Name = @UserName

END

GO

View 4 Replies View Related

SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic

Oct 20, 2014

I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):

UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'

[Code] ....

The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.

The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.

What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.

View 9 Replies View Related

T-SQL (SS2K8) :: Change Set Clause Of Update Statement Dynamically Based On Some Condition

May 27, 2015

I want to change Set clause of Update Statement dynamically based on some condition.

Basically i have 2 Update statments having same FROM clause and same JOIN clause.

Only diff is SET clause and 1 Where condition.

So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.

Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId

[Code] ....

But CASE statement is not working...

View 7 Replies View Related

T-SQL (SS2K8) :: Adding N Prefix In Update Statement In Order To Store The Text Correctly?

Sep 4, 2014

The FirstName and LastName values being passed in are in cyrillic text.

How do I add the N prefix in the update statement in order to store the text correctly.

I've tried FirstName = N@Firstname

or FirstName = '''N''' + @FirstName + '''

[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),

[code].....

View 8 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved