🔍

Custom Stored Procedures - Developer Edition

Steps

  1. Open Microsoft SQL Server Management Studio, connect to the relevant server and browse to the chosen database. Click "New Query" and then copy and paste the following into the window (this is your template):
     

    SET ANSI_NULLS ON

    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[spC_Something]

    @var1 int,
    @var2 int

    AS

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select *
    from objects
    where status = @var1 and objecttypeid = @var2
    order by objectid

    SET NOCOUNT OFF;

    END

  2. Change spC_Something to a relevent name for the task it is to be used for.  Please note the naming convention is such that you should ALWAYS prefix the stored procedure with spC.
    ie spC_ListObjects or spC_ListUsersByAge, etc

  3. @var1 and @var2 represent the variables that you pass into the stored procedure.  For example, if you were listing by object type, and you wanted to use the same stored procedure for different object types, you would pass this in, giving it a relevant name (@ObjectTypeId for example).
    You are not limited in the number of variables you pass in, however they need to be declared in this section, along with their data type.
    ie:
     @ObjectTypeId int,
    @DateFrom datetime,
    @DateTo datetime,
    @Searchstring nvarchar(512)

  4. The SQL statement you are running is written between the BEGIN and END of the stored procedure.  As this is a listing stored procedure you will see it is also between SET NOCOUNT ON; and SET NOCOUNT OFF;  This is necessary and should always be included in stored procedures that return data tables.

  5. The SQL statement in this template is very simple, but shows you how you can write a select statement using the variables passed into it.

  6. To save the stored procedure simply click "Execute" or press your F5 key.  This runs the SQL, and the command here is to CREATE the procedure.  If you need to make changes be sure to change the CREATE PROCEDURE to ALTER PROCEDURE.

  7. to test this stored procedure click the "New Query" button again to open a new tab, then type out your query name followed by the variables you are passing in.  To use the template SQL as an example:
    spC_Something 1,100014
    This will list out all contact object information stored within the objects table for contacts not in the recycle bin