Access to data with stored procedures

Question

One of the “best practices” is access to data through stored procedures. I understand why this scenario is good. My motivation is to separate the database and application logic (tables can change if the behavior of stored procedures is the same), protection for SQL injections (users cannot execute "select * from some_tables", they can only call stored procedures) and security (in the stored the procedure can be “anything” that protects that the user cannot select / insert / update / delete data, which is not for them).

I do not know how to access data using dynamic filters.

I am using MSSQL 2005.

If I have a table:

CREATE TABLE tblProduct ( ProductID uniqueidentifier -- PK , IDProductType uniqueidentifier -- FK to another table , ProductName nvarchar(255) -- name of product , ProductCode nvarchar(50) -- code of product for quick search , Weight decimal(18,4) , Volume decimal(18,4) ) 

then I have to create 4 stored procedures (create / read / update / delete).

The stored procedure for creating is simple.

 CREATE PROC Insert_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN INSERT INTO tblProduct ( ProductID, IDProductType, ... etc .. ) VALUES ( @ProductID, @IDProductType, ... etc ... ) END 

The stored procedure for "delete" is also simple.

 CREATE PROC Delete_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN DELETE tblProduct WHERE ProductID = @ProductID AND IDProductType = @IDProductType AND ... etc ... END 

The stored procedure for “updating” is similar to “delete”, but I'm not sure if this is the right way to do this. I think updating all columns is inefficient.

 CREATE PROC Update_Product( @ProductID uniqueidentifier, @Original_ProductID uniqueidentifier, @IDProductType uniqueidentifier, @Original_IDProductType uniqueidentifier, ... etc ... ) AS BEGIN UPDATE tblProduct SET ProductID = @ProductID, IDProductType = @IDProductType, ... etc ... WHERE ProductID = @Original_ProductID AND IDProductType = @Original_IDProductType AND ... etc ... END 

And the last - the stored procedure for "reading" - is a little mystery to me. How to pass filter values ​​for difficult conditions? I have a few suggestions:

Using an XML parameter to pass, where is the condition:

 CREATE PROC Read_Product ( @WhereCondition XML ) AS BEGIN DECLARE @SELECT nvarchar(4000) SET @SELECT = 'SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct' DECLARE @WHERE nvarchar(4000) SET @WHERE = dbo.CreateSqlWherecondition( @WhereCondition ) --dbo.CreateSqlWherecondition is some function which returns text with WHERE condition from passed XML DECLARE @LEN_SELECT int SET @LEN_SELECT = LEN( @SELECT ) DECLARE @LEN_WHERE int SET @LEN_WHERE = LEN( @WHERE ) DECLARE @LEN_TOTAL int SET @LEN_TOTAL = @LEN_SELECT + @LEN_WHERE IF @LEN_TOTAL > 4000 BEGIN -- RAISE SOME CONCRETE ERROR, BECAUSE DYNAMIC SQL ACCEPTS MAX 4000 chars END DECLARE @SQL nvarchar(4000) SET @SQL = @SELECT + @WHERE EXEC sp_execsql @SQL END 

But I think the 4000 character limit for a single request is ugly.

The next suggestion is to use filter tables for each column. Insert the filter values ​​into the filter table, and then call the stored procedure with the filter ID:

 CREATE TABLE tblFilter ( PKID uniqueidentifier -- PK , IDFilter uniqueidentifier -- identification of filter , FilterType tinyint -- 0 = ignore, 1 = equals, 2 = not equals, 3 = greater than, etc ... , BitValue bit , TinyIntValue tinyint , SmallIntValue smallint, IntValue int , BigIntValue bigint, DecimalValue decimal(19,4), NVarCharValue nvarchar(4000) , GuidValue uniqueidentifier, etc ... ) CREATE TABLE Read_Product ( @Filter_ProductID uniqueidentifier, @Filter_IDProductType uniqueidentifier, @Filter_ProductName uniqueidentifier, ... etc ... ) AS BEGIN SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct WHERE ( @Filter_ProductID IS NULL OR ( ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 1 ) AND NOT ( ProductID IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_ProductID AND FilterType = 2 ) ) AND ( @Filter_IDProductType IS NULL OR ( ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 1 ) AND NOT ( IDProductType IN ( SELECT GuidValue FROM tblFilter WHERE IDFilter = @Filter_IDProductType AND FilterType = 2 ) ) AND ( @Filter_ProductName IS NULL OR ( ... etc ... ) ) END 

But this sentence is a little more complicated, I think.

Is there some kind of “best practice” for this type of stored procedure?

Answer 1

First: for your deletion procedure, the where clause should include only the primary key.

Second: for your upgrade procedure, do not try to optimize before you have working code. In fact, do not try to optimize until you can profile your application and see where the bottlenecks are. I can tell you that updating one column of one row and updating all columns of one row is almost the same in speed. It takes time in the DBMS: (1) find the disk block on which you will write data, and (2) block other authors so that your recording is consistent. Finally, writing the code needed to update only the columns that need to be changed is usually harder and harder to maintain. If you really want to get picky, you have to compare the speed of figuring out which columns have changed compared to just updating each column. If you update them all, you do not have to read them.

Third: I try to write one stored procedure for each search path. In your example, I do one on the primary key, one for each foreign key, and then add one for each new access path, as I needed in the application. To be mobile; do not write code that you do not need. I also agree with using views instead of stored procedures, however you can use a stored procedure to return multiple result sets (in some version of MSSQL) or to change rows in columns, which may be useful.

If you need to get, for example, 7 rows by the primary key, you have some options. You can call a stored procedure that retrieves one row with a primary key seven times. This can be fast enough if you keep the connection open between all calls. If you know that you never need more than a certain number (for example, 10) of identifiers at a time, you can write a stored procedure that includes a where clause like "and ID in (arg1, arg2, arg3 ...)", and make unused arguments null. If you decide that you need to generate dynamic SQL, I would not worry about the stored procedure, because TSQL is just as easily mistaken as any other language. In addition, you do not get any benefit from using the database to perform string manipulations - this is almost always your bottleneck, so it makes no sense to give DB more work than necessary.

Answer 2

To read data, you do not need a stored procedure for security or to highlight logic, you can use views.

Just select only the selection in the view.

You can limit the displayed entries, change the field names, combine many tables into one logical "table", etc.

Answer 3

I disagree that creating Insert / Update / Select stored procedures is "best practice." If your entire application is not written in SP, use the database layer in your application to handle these CRUD actions. Better yet, use ORM technology to process them for you.

Answer 4

My suggestion is that you are not trying to create a stored procedure that does everything that you may need or will ever need. If you need to get a row based on the primary key of a table, write a stored procedure for this. If you need to find all the lines that satisfy a set of criteria, find out what these criteria can be and write down the stored procedure for this.

If you are trying to write software that solves all possible problems, rather than a specific set of problems, you usually fail to provide anything useful.

Answer 5

your stored procedure can be performed as follows to require only one stored process, but any number of different elements in the where clause. Go into any combination of parameters and you will get ALL elements that match - so you only need one stored procedure.

 Create sp_ProductSelect ( @ProductID int = null, @IDProductType int = null, @ProductName varchar(50) = null, @ProductCode varchar(10) = null, ... @Volume int = null ) AS SELECT ProductID, IDProductType, ProductName, ProductCode, Weight, Volume FROM tblProduct' Where ((@ProductID is null) or (ProductID = @ProductID)) AND ((@ProductName is null) or (ProductName = @ProductName)) AND ... ((@Volume is null) or (Volume= @Volume)) 

Answer 6

In SQL 2005, it supports nvarchar (max), which has a 2G limitation, but practically accepts all string operations under normal nvarchar. You can check if this can fit into what you need in the first approach.