May 17, 2013 at 4:42 pm
I've been looking at dynamic SQL update statements and have been trying to tailor one specific to what I need.
I have a table that needs updating, one row for each route. There are 2 values that need updated
for each route, however, the columns that need updating are different for each record that needs updating.
I have a query which can pull the 2 column names that need updating for each route/record.
I have the part about setting the update statement to a string, (which i hope is right):
DECLARE @sql NVARCHAR(500)
SELECT @sql = 'UPDATE #Summary
SET [' + @StartTime + '] = 0
, [' + @EndTime + '] = 0
FROM #Summary
WHERE route = @route'
EXEC sp_executesql @sql
At this point, is it just a matter of setting up a loop to take the values (route, startime, endtime) from table 1 and put them in a table variable to loop through and set variables for those values, which can be used in the update statement above?
This is what i have:
DECLARE @Route INT
, @StarttimeVARCHAR(5)
, @EndTimeVARCHAR(5)
DECLARE@RouteLunchesTABLE
(pkSMALLINT Primary Key IDENTITY(1,1)
,RouteINT
,LunchStart VARCHAR(5)
,LunchEndVARCHAR(5)
)
BEGIN
INSERT@RouteLunches
SELECT route
,LunchStart
,LunchEnd
FROM [View_Route_Scoring]rs
WHERE LDate = 20130401
AND rs.ProviderID = 10
END
--***************************************************
DECLARE @counterINT
,@numrowsINT
,@maxrowsINT
SET@Counter = 1
SET@numrows = (SELECTCount(*) FROM@RouteLunches)
SET@maxrows = (SELECTMAX(pk) FROM@RouteLunches)
--***************************************************
WHILE(@counter <= @maxrows)
BEGIN
SELECT@Route =(SELECT Route FROM @RouteLunches WHERE pk = @counter)
SELECT@Starttime =(SELECT LunchStart FROM @RouteLunches WHERE pk = @counter)
SELECT@EndTime =(SELECT LunchEnd FROM @RouteLunches WHERE pk = @counter)
BEGIN
DECLARE @sql NVARCHAR(500)
SELECT @sql = 'UPDATE #Summary
SET [' + @StartTime + '] = 0
, [' + @EndTime + '] = 0
WHERE route = [' + @Route + ']'
EXEC sp_executesql @sql
END
SET@counter = @counter + 1
END
Does the syntax look about right?
I'm getting an error:
Conversion failed when converting the varchar value 'UPDATE #Summary
SET [07:30] = 0
, [07:45] = 0
WHERE route = [' to data type int.
What did I miss?
May 18, 2013 at 7:03 am
tacy.highland (5/17/2013)
Does the syntax look about right?I'm getting an error:
Conversion failed when converting the varchar value 'UPDATE #Summary
SET [07:30] = 0
, [07:45] = 0
WHERE route = [' to data type int.
What did I miss?
Are [07:30] and [07:45] the names of the columns? They seem to be your mistake.
Igor Micev,My blog: www.igormicev.com
May 18, 2013 at 9:13 am
Those are indeed the names of the columns. Why would it be trying to convert it to an INT? (It can't be an INT since it has the colon, as it's representing a time of day.)
I'm guessing it's having issues with the fact that the column name is a bunch of numbers, should I be adding brackets to the times to help?
Any other suggestions for what might fix this?
May 19, 2013 at 7:55 pm
tacy.highland (5/17/2013)
DECLARE @Route INT
, @StarttimeVARCHAR(5)
, @EndTimeVARCHAR(5)
SELECT @sql = 'UPDATE #Summary
SET [' + @StartTime + '] = 0
, [' + @EndTime + '] = 0
WHERE route = [' + @Route + ']'
The problem is where I highlighted in bold (not sure that is easy to see). @Route is defined as an INT, so you can't simply concatenate it with the rest of the string. Change it to CAST(@Route AS VARCHAR(100)).
I also have to ask why you think you need brackets [] around the route. Perhaps what you need is quotes. Unless @Route contains a column name, which seems pretty unlikely.
Now having said that, I don't see why you need dynamic SQL at all for this. Are you familiar with CASE? You could simply update each column in #Summary using a CASE to distinguish which column gets the update applied based on Route. This way you can eliminate the loop entirely.
I'd need full DDL for the #Summary table and some good sample data to demonstrate.
Edit: If you must proceed with dynamic SQL, it would probably be better to pass @Route as a parameter into the call to sp_executesql. You define the parameters to pass with the second argument to that SP. Then the parameter is passed as the 3rd argument. Consult BOL for an example of this. Note also that the @sql is more properly passed as an NVARCHAR(MAX) string.
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 20, 2013 at 5:45 pm
As a bit of a sidebar, this is really, really bad. You have times stored in your database as VARCHAR(5) and dates stored as 8 digit INTs?
Please take a look at the first link in my signature line below. While you may have a fix for your current code, there's like no reason to use a loop for this and we'll likely be able to make it much faster. We'll also likely come up with some suggestions on how to store the data much more efficiently and code friendly. Don't forget to include the definition for #Summary.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply