December 2, 2008 at 4:23 pm
Hello everyone,
I know that this sounds like a really lame question but my background is more in the area of BI. Therefore I'm not as strong with SQL Server as I am with SSRS and SSAS. Without getting into a long story I need to make an object from a query that will be ultimately be used with a client user interface.
The problem that I have is that I had to develope a query using parameters (ie Declared and Set variables). Normally I don't like taking this route mainly because I am within my comfort zone. The query works perfectly but my experience is more with developing your standard view (You know, nothing fancy). Views in SQL Server do not like declared variables unfortunately. Therefore I dont know what my next step can or should be. Since this query will need to link to other normalized tables, I would like to have this query data in a view if possible. Can I please get some advice on this? Do I develop a Stored Procedure, a function?
Below is my statement so please feel free to provide any advise.
Thanks
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE( @cols + ',[' + cast(MonthTime as varchar) + ']',
'[' + cast(MonthTime as varchar)+ ']')
FROM dbo.Pivot_Time_Index_View
DECLARE @query NVARCHAR(4000)
Set @query = N'Select Program_Code, FunctionCode, SkillCode, ' + @cols + '
FROM (SELECT Program_Code, FunctionCode, SkillCode, ISNULL(Hours,0) As Hours, MonthTime
FROM dbo.Pivot_Time_Index_view LEFT
OUTER JOIN dbo.Program_Requirements_Hours_View ON dbo.Pivot_Time_Index_view.MonthTime = dbo.Program_Requirements_Hours_View.Period WHERE (NOT (Program_Code IS NULL))) p
PIVOT(
SUM(Hours) For MonthTime IN ('+@Cols+')
)As Pvt;'
Execute (@query)
December 2, 2008 at 4:37 pm
That's going to have to be a procedure, since you have dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 9:41 pm
Gail,
Thanks for replying. Since I am not very familiar with procedures could you please be more specific? Is there any possible way for a view to represent a stored procedure?
December 3, 2008 at 1:06 am
I'm afraid not. Procedures must be called with the EXEC statement and a view may only contain a single SELECT.
If you need to join the output, you'll have to insert the result of the proc into a temp table (which must exist already) and then join that. So..
CREATE TABLE #PivotResults (
...
Table must match the output of the proc
..
)
INSERT INTO #PivotResults
EXEC DoPivot ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply