SQL best practices

[kreplech]

Hello All,

I have a question regarding SQL industry best practices. Specifically calling stored procedures from server side scripting (in this case C#). My current sproc and C# pseudo-coded below - question to follow.

SQL:

create putData

{
@recID
@actionFlag
@field1
@field2
@field3
...
@fieldx
}

AS

-- actionFlag = 0 indicates that this is an insert statement
if @actionFlag=0
BEGIN TRANSACTION
insert into someTable (allValues) values (allValues)
COMMIT

if @actionFlag=1
BEGIN TRANSACTION
if @field1 NOT LIKE ''
update someTable set field1 = @field1
if @field2 NOT LIKE ''
update someTable set field2 = @field2

-- and so on until every param has been tested
COMMIT

GO


C# (well really just pseudo-code)

protected void callSproc(int actionFlag, int RecID, string field1, string field2, ... etc)
{

open database
call putData // sproc name
add params
execute

}

protected void onSomeAction_UpdateField1(object sender, EventArgs e)
{

callSproc(1,1234,"Field1 Data","","", ... etc)

}


OK - lot's of nonsense there, but here's the question(s)

There's just got to be a better way... whether it's some fancy SQL or OOP that's currently beyond me... specifically because, [sometable] could be (and is) huge. The sp and C# have to be updated everytime the table changes or i decide i want to update/insert a previously unused field in the table.

Also, I know I shouldn't have to test for NOT LIKE "" in the sproc. It's just plain dirty. I can't pass NULLS by virtue of C# sending blank params and sql complaining... but once again, there's just got to be a better way.

I don't expect anybody to do all this work for me - but if you could point me in the right direction that'd be just great.

I appreciate any help at all!!!

Thanks,
M

 

[gmmastros]

Let me get this straight.

You can't pass in NULLs, so if the data is an empty string, you want the column value to be null. If data is NOT an empty string, then insert that data.

Perhaps you're looking for the NULLIF function???


Ex:
[tt]
Create Procedure Blah
@recID int,
@actionFlag varchar(10),
@field1 varchar(10),
@field2 varchar(10),
@field3 varchar(10)
As
Select @actionFlag = NULLIF(@ActionFlag, ''),
@field1 = NULLIF(@field1, ''),
@field2 = NULLIF(@field2, ''),
@field3 = NULLIF(@field3, '')

Insert into SomeTable(collist...)
Values(@actionFlag, @field1, etc....)
[/tt]

The NULLIF Function Returns NULL if the first parameter is the same as the second parameter.

Ex:

Select NullIf(3,3)
Select NullIf(3,2)

The first will return NULL because the parameters are the same. The second returns a 3 because it is not the same as the second parameter.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kreplech]

OK - That's a step in the right direction. Thanks. But I'm thinking that having to call an update sproc that expects 15+ params is a problem. In other words, why call thisSproc(f1,f2,f3,f4,f5,f6,f7,...,fx) if I only want to update one field? So maybe the answer is to create a sproc that only expects the recordID,fieldName, and fieldValue (update table set @fieldName = @fieldValue where recnum = @recordID) then script something like

openDB

callProc(recordID,"field1","value1")
callProc(recordID,"field7","value7")
callProc(recordID,"fieldx","valuex")

closeDB

i know its really kinda simplistic, but maybe more efficient? what do you think?

 

[gmmastros]

>> what do you think?

I think this is a bad idea. In order to get that to work, you would need to use [google]dynamic sql[/google]. There are a variety of problems with that approach.

And seriously... 15 columns isn't all that much.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kreplech]

>>> And seriously... 15 columns isn't all that much.

You're right - I under-exaggerated... it's actually over 50. I suppose the best way to do this is to only use the fields I need as I go... meaning, of the 50 fields, I'm really only updating/inserting 15 or so for the majority of the time. I'll add to the sproc when necessary, or use additional sprocs to group the fields in some sensible way down the road.

thanks again for the help

M

 

[SQLSister]

If you create default values for the parameters, then you only need to pass in the ones with values and the defaults will be used. Look in BOL concerning executing a stored proc and parameters to see how to do this. Null can be a default value.

"NOTHING is more important in a database than integrity." ESquared

 

[kreplech]

Awesome - that's the exact sort of thing i was looking for. Thanks. I'll look into how to do this.

Thanks again,
M

 

[kreplech]

Found the following example of default sproc values at

http://www.daveandal.net/books/6744/dataaccess/sprocdefaults.aspx

Is this the right idea - or are there improved/advanced ways yet?

---------------
Using Default Values in a Stored Procedure
Executing the SQL Server stored procedure:

CREATE PROCEDURE ParamDefaultProc
@OrderID int, @CustomerID nchar(5),
@OrderDate datetime = NULL, @RequiredDate datetime = NULL,
@ShippedDate datetime = NULL, @ShipVia int = 1,
@Freight money = 25, @ShipName nvarchar(40) = NULL,
@ShipAddress nvarchar(60) = NULL, @ShipCity nvarchar(15) = NULL,
@ShipPostalCode nvarchar(10) = NULL, @ShipCountry nvarchar(15) = NULL
AS
IF @OrderDate IS NULL
BEGIN
SET @OrderDate = GETDATE()
END
IF @RequiredDate IS NULL
BEGIN
RAISERROR('Procedure ParamDefaultProc: you must
provide a value for the RequiredDate',
1, 1) WITH LOG
RETURN
END
IF @ShipName IS NULL
BEGIN
SELECT @ShipName = CompanyName, @ShipAddress = Address,
@ShipCity = City, @ShipPostalCode = PostalCode,
@ShipCountry = Country
FROM Customers
WHERE CustomerID = @CustomerID
END
UPDATE Orders SET
OrderDate = @OrderDate, RequiredDate = @RequiredDate,
ShippedDate = @ShippedDate, ShipVia = @ShipVia,
Freight = @Freight, ShipName = @ShipName,
ShipAddress = @ShipAddress, ShipCity = @ShipCity,
ShipPostalCode = @ShipPostalCode, ShipCountry = @ShipCountry
WHERE
OrderID = @OrderID

 

[kreplech]

A better example: (from

http://devpinoy.org/blogs/bonskijr/archive/2006/02/16/1708.aspx)

An interesting application

I have used stored procs extensibly in my application, but I was kind of frustrated when a situation arises that not all parameters will be used for either an INSERT or UPDATE procedure. I use only a single stored proc to update for example any modification to our MedicalReports table. An update might contain just updating the last modified column or update both last modified column and approvedby column. I have to resort to client side updates to handle both scenario and find it cumbersome to maintain. Then I was toying the idea of default values, but instead of having actual values, I would instead default all the values to NULL.
PROC dbo.pr_UpdateMedRep(@RepId INT, @LastModBy VARCHAR(10)=NULL, @ApprovedBy VARCHAR(10)=NULL)

The body of our stored proc will be like this:

UPDATE MedicalReportsTable SET
LastModBy = ISNULL(@LastModBy, LastModBy),
ApprovedBy = ISNULL(@ApprovedBy, ApprovedBy)
WHERE MedicalReportsTable.RepId = @RepId


Now if I want to update only the LastModify column:
pr_UpDateMedRep 1, 'User1'

Updating only ApprovedBy column:
pr_UpDateMedRep 1, NULL, 'User2'

Without worrying that either column will be updated when it shouldn't be and I'm still using stored procedure to do so.
Published Thursday, February 16, 2006 3:21 AM by bonskijr
Filed under: T-Sql