Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: how to create a stored procedure

I'm learning sql from a book and I'm trying to write a stored procedure but I don't believe that I'm doing it correctly. Is the following way not valid in Microsoft SQL? If not, when is it valid, if ever?

create procedure dept_count(in dept_name varchar(20), out d_count integer)    begin      select count(*) into d_count      from instructor      where instructor.dept_name=dept_count.dept_name    end 

I get the following error

Msg 156, Level 15, State 1, Procedure wine_change, Line 1 Incorrect syntax near the keyword 'in'.

like image 339
Spark323 Avatar asked Nov 13 '13 07:11

Spark323


People also ask

How do I create a stored procedure in SQL Server?

Using SQL Server Management StudioRight-click Stored Procedures, and then click New Stored Procedure. On the Query menu, click Specify Values for Template Parameters. In the Specify Values for Template Parameters dialog box, enter the following values for the parameters shown. Returns employee data.

How do I create a stored procedure in SQL Server 2016?

In SQL Server 2016, you can create a stored procedure by right-clicking on the Stored Procedures node in the Object Explorer and selecting New > Stored Procedure... or New > Natively Compiled Stored Procedure... . This will open a template that's ready to be populated with your own specific procedure.

How do I execute a stored procedure in SQL Server Management Studio?

In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.


1 Answers

T-SQL

/*  Stored Procedure GetstudentnameInOutputVariable is modified to collect the email address of the student with the help of the Alert Keyword */    CREATE  PROCEDURE GetstudentnameInOutputVariable (  @studentid INT,                   --Input parameter ,  Studentid of the student @studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name @StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email ) AS BEGIN SELECT @studentname= Firstname+' '+Lastname,      @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid END 
like image 188
SamDeveloper Avatar answered Oct 15 '22 10:10

SamDeveloper