Custom Stored Procedures - Developer Edition

Linked Help

 

 

Overview 

Custom Stored Procedures - Developer Edition

These can be accessed and used as data sources for grids, allowing a developer to construct a source that would either be too complex in Modeller, or structurally impossible given the rules Modeller subscribes to.
For those who have never written one, or been asked to look at one, a stored procedure is an SQL statement that is stored within the database and can have variables passed into it.  As it is stored it is compiled, meaning it runs faster.  It's also reusable due to the different variables you can pass in.

Guidance

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

 XPOR Ltd. UK Co. Registered in England No. 10409669
 02392 738000
 02392 739000