Stored procedures and views

1. Stored procedures and views • You can see definitions for stored procedures and views in the demo databases but you can’t change them. • For views, expand the views folder. • Right click on a view and select Design • For stored procedures, expand the programmability folder and then the stored procedures folder. • Right click on a stored procedure and select Modify

2. If you want to experiment with them and change them, you will have to generate and run scripts to recreate the definitions for tables, views, and stored procedures in your group database and then import the data from the base tables.

3. DO NOT import views using the import process described previously. It copies the views but stores the results as base tables.

4. The instructions that follow assume that you have NOT already copied or imported any data from the database you are scripting. If you have, delete all of those tables before following these instructions.

5. Generate scripts for tables, views, and stored procedures • Right click on the source database and select tasks  Generate Scripts. • A script wizard appears; click Next.

6. Click the radio button associated with Select specific database objects • Select all of the tables, views, and stored procedures ONLY! DO NOT select anything else. • Note: a checkbox for views and stored procedures will only appear if the database has views or stored procedures. • Click Next.

7. A window showing script options appears. Click on the Advanced button. • There is an option under General that reads Script USE DATABASE. Set its property to False. • Click OK. • Select the radio button associated with Save to Clipboard. • Click next

8. A summary window appears showing your selections. If they are all correct click the Next button and the Finish button. • You have now generated a script (SQL commands to create tables, views, and stored procedures) and it’s on the clipboard.

9. You now have to run this script. To do this: • Select your destination database. • Right click and select New Query. • Into the query window paste the script you previously saved on the clipboard.

10. Click the execute buttonabove the above the stored procedure tab (the red !) • This will create all the tables, views, and stored procedures that were in the source database. • Refresh your connection in the object explorer window and you should be able to see the tables, views, and stored procedures.

11. You can close the script window without saving it. You don’t need it anymore. • NOTE: the tables are created but they have no data. Populating a table with data is a different process.

12. At this point you should import data from the base tables in the source database as described in a previous powerpoint file. • Remember to import ONLY the base tables.

13. NOTE: It is difficult to predict the order in which tables will be imported. It’s possible that the wizard could try to import a table containing foreign keys first. If it does this, it is a violation of a referential integrity rule.

14. To be on the safe side you should import tables using a two step process. • Import ONLY the tables that DO NOT contain a foreign key. • Then import ONLY the tables that contain a foreign key.

15. To see a view definition, expand the views folder, right click on a view, and select Design. • You can see the results of the view by clicking on the ! In the tool bar above the explorer window.

16. Creating Stored Procedures • Expand the Programmability folder under your database. • Right click on the Stored Procedure entry in your database and select New Stored Procedure. • You will get a template for writing code. Typing ctrl shift  M allows you to enter parameters to the template.

17. Or you can just enter your code using logic similar to that in the stored procedures in the demo databases. • To execute the code that creates the stored procedure, click execute (also the red !) in your tool bar. • NOTE: This is NOT executing the stored procedure. • Your stored procedure is created

18. To modifya stored procedure, find the stored procedure name as above, right click, and select modify. • Makes changes as necessary and again click execute as before. • When you close the window you’ll be asked if you want to save changes to a listed sql file. This is not necessary if you’ve done the previous step.

19. Testing a Stored Procedure from Visual Studio 2010 • Start Visual Studio .NET 2010 • Close the Start Page if it appears. • Select View  Server Explorer • Right click on data Connection and select Add Connection.

20. You may or may not see a small window with the title Choose Data Source. If you do then select Microsoft SQL Server. • In the Data Provider drop down menu, select .NET Framework Data Provider for SQLServer • Click the Continue button.

21. You should see a window with the title Add Connection and the Data source should specify Microsoft SQL Server (SQLClient). • If it doesn’t use the Change button to access the Change Data Source window and proceed as in the previous slide. • Otherwise proceed to the next step.

22. Select ICSD for the server name. • Select the radio button Use Windows Authentication. • Select your group database name. • Click OK

23. In the Server Explorer pane expand the new connection entry and theStored Procedures folder • Double click on the stored procedure you want to run. • Its code should appear in a window.

24. Right click on the stored procedure name in the server explorer window and select Execute. • A window will appear that allows you to enter values for parameters that are passed to the stored procedure. • Enter the parameter information; click OK.

25. You can verify that it worked by going back to management studio and viewing the table

26. There is a debug ability that allows a user to step through a stored procedure. • To allow this, student logins MUST be added to the sysadmin server role. • Given the level of permissions that provides, that’s not going to happen. However, our stored procedures will all be short so it should not be a major issue.

27. Creating Triggers • Expand the folder for the table to which you want to apply the trigger. • Right click on theTriggers folder and select New Trigger. • Type in trigger code. • NOTE: There are triggers on the student (probation) and registration (overload trigger) tables in the university database. • You can view and copy the code