CODEWITHSUNDEEP
sql-servert-sql
Spark323
Spark323

Reputation: 1585

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'.

Upvotes: 26

Views: 134892

Answers (9)

Fausto Odilon
Fausto Odilon

Reputation: 49

Every answer are good, but I strongly recommend aways to use the schema identifier to create any SQL object, for example:

CREATE PROCEDURE dbo.spu_MyProcedure (@Param 1 INT, @Param2 INT OUTPUT)

In the example, "dbo".

And also in every command like UPDATE, INSERT, etc. Remember in SQL Server we can have multiple objects with the same name in different schemas.

The reason: If scripts are used in some kind of automation pipeline, as liquibase, for example, if you don't put the schema, the tool will put itself schema, which will break the solution. Another reason: If there is a solution in database where there are more than 1 schema, so we can have same names in different schemas, each one being a complete different procedure (or table etc). In this case, SQL will not be able to know which schema you are using. It may exist a default, but I don't recommend to relie 100% in defaults.

By the way, the "[]" in names are an exclusive SQL resource, not part of names. It allows names with spaces and some other characters normally not allowed. I Personally don't recommend, if not strictly necessary.

Another point, variables and parameters must aways start with "@" character, followed by the type.

One more tip:

Create at the begining of the script a verification, if the procedure does not exists yet. If exists you can drop to recreate:

IF OBJECT_ID('dbo.spu_MyProcedure') IS NOT NULL BEGIN DROP PROCEDURE dbo.spu_MyProcedure END GO

CREATE PROCEDURE....

Observation: I avoid ALTER PROCEDURE because I've had problems some times, I don't know exactly what happens, but some procedures crashed when I used ALTER, and using DROP / CREATE I never had problems.

Remember you need the GO after the Verification, because de CREATE PROCEDURE must be the first statement in a batch.

Upvotes: 0

Vikas Lalwani
Vikas Lalwani

Reputation: 1061

To Create SQL server Store procedure in SQL server management studio

  • Expand your database
  • Expand programmatically
  • Right-click on Stored-procedure and Select "new Stored Procedure"

Now, Write your Store procedure, for example, it can be something like below

USE DatabaseName;  
GO  
CREATE PROCEDURE ProcedureName 
 @LastName nvarchar(50),   
 @FirstName nvarchar(50)   
AS   

SET NOCOUNT ON;  
 
//Your SQL query here, like
Select  FirstName, LastName, Department  
FROM HumanResources.vEmployeeDepartmentHistory  
WHERE FirstName = @FirstName AND LastName = @LastName  
GO

Where, DatabaseName = name of your database
ProcedureName = name of SP
InputValue = your input parameter value (@LastName and @FirstName) and type = parameter type example nvarchar(50) etc.

Source: Stored procedure in sql server (With Example)

To Execute the above stored procedure you can use sample query as below

EXECUTE ProcedureName @FirstName = N'Pilar', @LastName = N'Ackerman';

Upvotes: 0

N. Srinivasan
N. Srinivasan

Reputation: 11

CREATE PROCEDURE [dbo].[USP_StudentInformation]
@S_Name VARCHAR(50)
,@S_Address VARCHAR(500)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Date VARCHAR(50)

SET @Date = GETDATE()


IF EXISTS (
        SELECT *
        FROM TB_StdFunction
        WHERE S_Name = @S_Name
            AND S_Address = @S_Address
        )
BEGIN
    UPDATE TB_StdFunction
    SET S_Name = @S_Name
        ,S_Address = @S_Address
        ,ModifiedDate = @Date
    WHERE S_Name = @S_Name
        AND S_Address = @S_Address

    SELECT *
    FROM TB_StdFunction
END
ELSE
BEGIN
    INSERT INTO TB_StdFunction (
        S_Name
        ,S_Address
        ,CreatedDate
        )
    VALUES (
        @S_Name
        ,@S_Address
        ,@date          
        )

    SELECT *
    FROM TB_StdFunction
END
END

Table Name :   TB_StdFunction


S_No  INT PRIMARY KEY AUTO_INCREMENT
S_Name nvarchar(50)
S_Address nvarchar(500)
CreatedDate nvarchar(50)
ModifiedDate nvarchar(50)

Upvotes: 1

Pachi
Pachi

Reputation: 41

I think it can help you:

CREATE PROCEDURE DEPT_COUNT
(
    @DEPT_NAME VARCHAR(20), -- Input parameter
    @D_COUNT INT OUTPUT     -- Output parameter
    -- Remember parameters begin with "@"
)
AS -- You miss this word in your example
BEGIN
    SELECT COUNT(*) 
    INTO #D_COUNT -- Into a Temp Table (prefix "#")
    FROM INSTRUCTOR
    WHERE INSTRUCTOR.DEPT_NAME = DEPT_COUNT.DEPT_NAME
END

Then, you can call the SP like this way, for example:

DECLARE @COUNTER INT
EXEC DEPT_COUNT 'DeptName', @COUNTER OUTPUT
SELECT @COUNTER

Upvotes: 4

Deepshikha
Deepshikha

Reputation: 10264

In T-SQL stored procedures for input parameters explicit 'in' keyword is not required and for output parameters an explicit 'Output' keyword is required. The query in question can be written as:

CREATE PROCEDURE dept_count 
    (
    -- Add input and output parameters for the stored procedure here
    @dept_name varchar(20), --Input parameter 
    @d_count int OUTPUT     -- Output parameter declared with the help of OUTPUT/OUT keyword
    ) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

     -- Statements for procedure here
    SELECT @d_count = count(*)
    from instructor
      where instructor.dept_name=@dept_name

END
GO

and to execute above procedure we can write as:

Declare @dept_name varchar(20), -- Declaring the variable to collect the dept_name
        @d_count int            -- Declaring the variable to collect the d_count 
SET @dept_name = 'Test'

Execute  dept_count @dept_name,@d_count output
SELECT   @d_count               -- "Select" Statement is used to show the output

Upvotes: 12

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Try this: 

 create procedure dept_count(@dept_name varchar(20),@d_count int)
       begin
         set @d_count=(select count(*)
                       from instructor
                        where instructor.dept_name=dept_count.dept_name)
         Select @d_count as count
       end

Or

create procedure dept_count(@dept_name varchar(20))
           begin
            select count(*)
                           from instructor
                            where instructor.dept_name=dept_count.dept_name
           end

Upvotes: 1

SamDeveloper
SamDeveloper

Reputation: 536

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

Upvotes: 37

Dgan
Dgan

Reputation: 10285

try this:

create procedure dept_count( @dept_name varchar(20), @d_count INTEGER out)

   AS
   begin
     select count(*) into d_count
     from instructor
     where instructor.dept_name=dept_count.dept_name
   end

Upvotes: 0

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

Create this way.

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

Upvotes: 0

Related Questions