February 23, 2012 at 2:36 pm
Here is a piece of code I am trying to run. I am not sure why its failing..I tried all the tricks I know of. Even after printing the string, I dont see any syntactical error. Please help.
DECLARE @string NVARCHAR(MAX) = '
CREATE PROC [dbo].[Trend]
@slsID NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON
DECLARE @BeginningRange varchar(20),
@EndingRange varchar(20)
SET @EndingRange = ''12*13''
SET @BeginningRange = ''12*02''
;WITH CTE1 AS(
SELECT
dbo.Field1,dbo.Field2,dbo.Field3
FROM dbo.Table1 join dbo.Table2 where...conditions
weekNum BETWEEN (@BeginningRange) AND (@EndingRange)
)
SELECT * FROM CTE1
UNPIVOT
( numbers for type in (Field1, Field2, Field3, Field4)
) as p PIVOT
(
Sum(numbers) for
WeekNum in ([12*02],[12*03],[12*04],[12*05],[12*06],[12*07],[12*08],[12*09],[12*10], [12*11],[12*12],[12*13])
) as q
END
'
EXECUTE SP_EXECUTESQL @STRING
February 23, 2012 at 2:58 pm
What's the error you're getting?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2012 at 3:13 pm
My bad..I didnt even mention that.
It says "Incorrect syntax near ')' ".
I am using Print statements to weed out what's going wrong.
February 23, 2012 at 3:16 pm
You didn't close the statement for q and you've got an extra comma in your delimited list. It looks like the end of that line is just trimmed off.
Ignore that, it's just spaced oddly. Still looking.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2012 at 3:21 pm
I can't find anything wrong with it visually. It seems fine. I can't test your statement in any way because it's been heavily modified (see WHERE...conditions).
Due to that, I can't really help you syntactically. Check everywhere you swapped something out for privacy's sake, it's in one of those.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2012 at 3:27 pm
I got the solution. While creating the code, I had thought that I would be replacing the single quotes as well with a set statement. But that was not the case. The misplaced quotes were throwing off the entire procedure off track.
Finally I got the solution.
Many thanks for the help! 🙂
February 23, 2012 at 3:28 pm
Like Craig said, it is nearly impossible with the underlying tables to figure this out.
I would suggest taking it out of the dynamic sql and see if the actual code you are trying to execute dynamically is valid. My guess is the actual script has an error. If so, it will be roughly 1920395730293675 times easier to debug outside dynamic sql.
--edit-- you posted that you figured it out as I was typing. 😛
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply