Databases
StackOverflow2013
Postsdbo
POAccessing data with stored procedures
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccessing data with stored procedures
    primarykey
    data
    text
    <p>One of the "best practice" is accessing data via stored procedures. I understand why is this scenario good. My motivation is split database and application logic ( the tables can me changed, if the behaviour of stored procedures are same ), defence for SQL injection ( users can not execute "select * from some_tables", they can only call stored procedures ), and security ( in stored procedure can be "anything" which secure, that user can not select/insert/update/delete data, which is not for them ).</p> <p>What I don't know is how to access data with dynamic filters.</p> <p>I'm using MSSQL 2005.</p> <p>If I have table:</p> <pre><code>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) ) </code></pre> <p>then I should create 4 stored procedures ( create / read / update / delete ).</p> <p>The stored procedure for "create" is easy.</p> <pre><code>CREATE PROC Insert_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN INSERT INTO tblProduct ( ProductID, IDProductType, ... etc .. ) VALUES ( @ProductID, @IDProductType, ... etc ... ) END </code></pre> <p>The stored procedure for "delete" is easy too.</p> <pre><code>CREATE PROC Delete_Product ( @ProductID uniqueidentifier, @IDProductType uniqueidentifier, ... etc ... ) AS BEGIN DELETE tblProduct WHERE ProductID = @ProductID AND IDProductType = @IDProductType AND ... etc ... END </code></pre> <p>The stored procedure for "update" is similar as for "delete", but I'm not sure this is the right way, how to do it. I think that updating all columns is not efficient.</p> <pre><code>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 </code></pre> <p>And the last - stored procedure for "read" is littlebit mystery for me. How pass filter values for complex conditions? I have a few suggestion:</p> <p>Using XML parameter for passing where condition:</p> <pre><code>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 &gt; 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 </code></pre> <p>But, I think the limitation of "4000" characters for one query is ugly.</p> <p>The next suggestion is using filter tables for every column. Insert filter values into the filter table and then call stored procedure with ID of filters:</p> <pre><code>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 </code></pre> <p>But this suggestion is littlebit complicated I think.</p> <p>Is there some "best practice" to do this type of stored procedures?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKevin Fairchild
    1. USTcKs
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAccessing data with stored procedures
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAccessing data with stored procedures
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAccessing data with stored procedures
      1. USMatthias Meid
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload