July 24, 2012 at 12:23 pm
Wanted to know where i am going wrong in this query
DECLARE @Table TABLE
(
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
ContactID INT
)
INSERT INTO @Table
SELECT FirstName,MiddleName,LastName,ContactID FROM Person.Contact
DECLARE @FirstName NVARCHAR(255)
SET @FirstName='kim'
IF EXISTS(SELECT * FROM Person.Contact WHERE FirstName like '%@FirstName%')
UPDATE @Table SET FirstName='Sam'
ELSE
INSERT INTO @Table
SELECT FirstName,MiddleName,LastName,ContactID FROM Person.Contact
WHERE FirstName like '%@FirstName%'
July 24, 2012 at 12:33 pm
WHERE FirstName like '%@FirstName%'
should be
WHERE FirstName like '%' + @FirstName + '%'
July 24, 2012 at 12:45 pm
AND your update statement has no where clause. I guess this is OK if you want everyone to be named 'Sam'. 😛
_______________________________________________________________
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/
July 24, 2012 at 12:51 pm
Smash125 (7/24/2012)
Wanted to know where i am going wrong in this queryDECLARE @Table TABLE
(
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
ContactID INT
)
INSERT INTO @Table
SELECT FirstName,MiddleName,LastName,ContactID FROM Person.Contact
DECLARE @FirstName NVARCHAR(255)
SET @FirstName='kim'
IF EXISTS(SELECT * FROM Person.Contact WHERE FirstName like '%@FirstName%')
UPDATE @Table SET FirstName='Sam'
ELSE
INSERT INTO @Table
SELECT FirstName,MiddleName,LastName,ContactID FROM Person.Contact
WHERE FirstName like '%@FirstName%'
OK I looked a little closer and I asking myself "what are you trying to do?".
Let me paraphrase what this is doing.
First insert every row from Person.Contact into a table variable.
Second, if there is any row in that table that contains the characters 'kim' update every first name in the table variable to 'Sam'
If there are no rows in that table that contain the characters 'kim' do nothing (why do nothing? because this is inside a conditional check to see if there are rows in the Person.Contact table. If you don't find any then insert them.)
When glancing at your code it looked there were a couple of issues, but on further analysis there is little that makes sense.
If you can explain what you are trying to do I will be happy to help you sort this out.
_______________________________________________________________
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/
July 24, 2012 at 1:07 pm
I tossed together a quick flowchart to demonstrate what this is doing. Maybe it will help. Look down below for the attachment.
_______________________________________________________________
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/
July 24, 2012 at 1:10 pm
I think that it would be best if you provided some DDL and a description of your task, we can then look and assist you 🙂 😉
***The first step is always the hardest *******
July 24, 2012 at 7:12 pm
First off, you should probably be using MERGE for this.
Second, in a high transaction database, using IF EXISTS followed by UPDATE/INSERT as you are doing is quite dangerous because some other transaction could slip in and insert the record you're planning to, causing a primary key duplicate failure on your INSERT (assuming you have a primary key, which your DDL doesn't show).
Finally, what is this?
WHERE FirstName like '%@FirstName%'
Shouldn't it be:
WHERE FirstName like '%' + @FirstName + '%'
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply