Here is an example of a stored procedure and how to execute it:
CREATE PROCEDURE MySP @par1 int,
@par2 nvarchar(40) AS
SELECT col1, col2, col3
FROM sometable
WHERE keycol1 = @par1
AND keycol2 = @par2
go
EXEC MySP 36, 'xyz'
Beware that CREATE PROCEDURE must be a batch of its own. There can be no statements directly before CREATE PROCEDURE, but there must be a go
separator first. And you also need a go
at the end of the procedure to delimit from statements that follows.
(Keep in mind that go is not an SQL statement. That's an instruction to the query tool to split up the script in batches and send one batch at a time to SQL Server.)