August 18, 2014 at 4:44 pm
DECLARE @SQL nvarchar(max)
SET @SQL = N'SELECT TeamID'
SELECT @SQL = @SQL + ',MAX(CASE WHEN rownum='+CAST(a.rownum AS CHAR(5))+' THEN PersonId ELSE '''' END) AS [PersonId+CAST(a.rownum AS CHAR(5))+']'
FROM dbo.vTeamCoaches a GROUP BY a.rownum
ORDER BY a.rownum
SET @SQL = @SQL + N'
FROM vTeamCoaches
GROUP BY Teamid
ORDER BY Teamid'
PRINT @SQL
EXEC sp_executesql @SQL
[/code]
How do I get the results the SP is producing in a SQL View?
I need it to run reports on the results.
I saved the stored procedure but can't seem to call it out? In fact I don't see in under Programmability | Stored Procedures?
August 18, 2014 at 5:44 pm
Are you sure that you created the SP and not just saved the script?
You're missing the CREATE PROCEDURE statement.
August 18, 2014 at 5:50 pm
yes, it's saved as a .sql (script)?
not sure really how to do much of this in SQL
Newbie :unsure:
August 18, 2014 at 6:18 pm
I'm sorry, I didn't realize that.
Let me start with the basics.
A script is a file that contains code. This is true for any programming language.
A stored procedure is a database object stored in the server. Is a piece of compiled code and is the equivalent of a module in other programming languages.
A stored procedure needs certain structure that can be found in here: http://msdn.microsoft.com/en-us/library/ms187926(v=sql.105).aspx
Basically, you're missing the procedure header (and a quote).
CREATE PROCEDURE dbo.myProcedureName
AS
DECLARE @SQL nvarchar(max)
SET @SQL = N'SELECT TeamID'
SELECT @SQL = @SQL + ',MAX(CASE WHEN rownum='+CAST(a.rownum AS CHAR(5))+' THEN PersonId ELSE '''' END) AS [PersonId'+CAST(a.rownum AS CHAR(5))+']'
FROM dbo.vTeamCoaches a
GROUP BY a.rownum
ORDER BY a.rownum
SET @SQL = @SQL + N'
FROM vTeamCoaches
GROUP BY Teamid
ORDER BY Teamid'
PRINT @SQL
EXEC sp_executesql @SQL
If you run the script as this, you'll create the procedure (you should change the name.) It won't receive any parameters, you need to add more code if you need them. Once created, you can refresh your object explorer and you'll see it in the procedures' folder.
Once you've created it, you can run it with the word EXECUTE (or EXEC).
EXECUTE dbo.myProcedureName
--or
EXEC dbo.myProcedureName
August 18, 2014 at 6:51 pm
Thank you very much!
that worked!
I see the stored procedure now.
spTeamCoachPivot
I did do the
exec dbo.spTeamCoachPivot and am able to see the results in the SQL View.
However I can't save the View to be used for other views/reports?
I am getting:
Incorrect syntax near the keyword 'Exec'.
After trying to save the View (vTeamCoachesPivot)
Need to save the view to use the results to do an INNER JOIN in another view (pull the names linked to the PersonId columns
End goal is to get all of the Coach Names (based on PersonId per Team (TeamId))
then link that view with these results to the Competitions the Teams and Coaches were/are involved in for the Event's manager.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply