Stored procedures are compiled in to the database. What I usually do is.... I open SQL Server Management Studio. Set the database (using the dropdown) write my procedure, and then execute it. When you execute this, you are actually executing the 'Create procedure'. You are not executing the code within the procedure. To execute the code within the procedure, you need to call it specifically.
For example:
[tt][blue]
Create Procedure dbo.NameOfProcedure
As
SET NOCOUNT ON
Select Blah From Blah
[/blue][/tt]
When you execute the code above, you will NOT receive data from the table. Instead, the execute will CREATE the stored procedure. This stored procedure will be saved in the database and is available to be called any time you want.
If you want to test the procedure from a query window, you would do this:
[tt][blue]
Exec dbo.NameOfProcedure
[/blue][/tt]
When you execute the code above, the stored procedure will run, and you will get the results of the procedure to show in a grid.
When I create a new stored procedure, I usually start with identifying the parameters, and write code like this:
Code:
Declare @EyeColor VarChar(20)
Declare @ShoeSize Decimal(3,1)
Select @EyeColor = 'Brown', @ShoeSize = 9.5
Then I press F5 to make sure I didn't make any mistakes (like spelling).
Then, I start writing my query, something like:
Code:
Declare @EyeColor VarChar(20)
Declare @ShoeSize Decimal(3,1)
Select @EyeColor = 'Brown', @ShoeSize = 9.5
[red]
Select *
From SomeTable
Where EyeColor = @EyeColor
And ShoeSize = @ShoeSize
[/red]
I press F5 again to make sure I didn't make any mistakes and that the query returns the correct data. If everything works the way I expect it to, I then make the procedure, like this:
Code:
[red]Create Procedure dbo.GetDataForEyeColorAndShoeSize
@EyeColor VarChar(20),
@ShoeSize Decimal(3,1)
As[/red]
Select *
From SomeTable
Where EyeColor = @EyeColor
And ShoeSize = @ShoeSize
This time, pressing F5 does appear to do anything, but it actually creates the procedure. The last step for me is to test the procedure, like this:
Code:
[red]Alter[/red] Procedure dbo.GetDataForEyeColorAndShoeSize
@EyeColor VarChar(20),
@ShoeSize Decimal(3,1)
As
Select *
From SomeTable
Where EyeColor = @EyeColor
And ShoeSize = @ShoeSize
[red]
Go
Exec dbo.GetDataForEyeColorAndShoeSize 'Brown',9.5
[/red]
Notice I changed the create to ALTER. I also put GO at the end and added my exec call (to call the procedure). GO is considered a "batch separator". This is unique to SQL Server Management Studio (and the old Query Analyzer). GO will separate blocks of code. For example, you could have a SQL script that creates a table, creates some indexes, populates some data, and creates a couple stored procedures. You cannot run all these commands at the same time, but you can put them in the same query window, separated by GO's, and they will each execute separately.
Lastly, it's considered best practice to also SAVE the script. (Here's another source of confusion.) When you click File -> Save, what actually happens is... the contents of the query window are saved to an ASCII file. It does NOT affect the database at all. Best practice states that you should save all the scripts and put them in to a version control system (like SourceSafe). Then, every time you want to modify a procedure, you check out the script file, open in SSMS, modify it, execute it (to compile it in to the database) and then re-save it, and check it back in to source control.
You mentioned earlier that you were just beginning to learn about joins. There is a method within SQL Server that I think you will find helpful. Let me explain:
Open SQL Server Management Studio
In the Object Explorer (on the left) drill down to your database.
You will see a category list of objects (Diagrams, Tables, Views, Synonyms, etc...) Right click Views, then click New View
Select the tables you want to join. (use CTRL-click to select multiple tables).
Click ADD. Click Close.
You will now see a window that shows your multiple tables at the top, and some other info.
You can click on the columns to add that column to the output. But.... you can
drag a column from one table to a column from the other table. This will draw a line from one table to another. By default, this will make an Inner Join for you. You can right click the line and change it to a left join or right join.
You can also set sort orders and filter conditions.
SQL Server will build the SQL statement for you. You can copy/paste this query and do anything you want with it.
Long term, you will NOT want to rely on the view builder tool because there are some serious limitations. As a learning tool.... it's pretty good.
-
George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
![[lookaround]](/data/assets/smilies/lookaround.gif "[lookaround] [lookaround]")
"you cain't fix 'stupid'...
