SQL best practices - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL best practices

Author

Topic

kreplech
Starting Member

10 Posts

Posted - 2007-11-16 : 17:42:31

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-16 : 19:31:12

My head almost exploded there are so many things to discuss. :) Unfortunately, I don’t have time for examples and there are many ways to do this but here are some basics:

1. You do not need to use transaction like your example sproc. An INSERT or an UPDATE are atomic by definition, so they are already in a "transaction." However, if you need to do multiple steps then you may want to wrap all the steps with a transaction.

2. There are Dynamic SQL wrappers that will handle a lot of this stuff for you. Weather they are good or not is still being debated, but you should now how this stuff works before you obfuscate this stuff away with someone else’s generator.

3. In general, you would create an INSERT and an UPDATE stored procedure (Or possible one UPSERT). Then you call the appropriate stored procedure based upon what you want to do. Also in general you would pass all the parameters for that table (or operation or whatever) and Insert or Update all the columns (yes there are exceptions).

4. You can pass NULL values to your stored procedure. I don't remember what it is off the top of my head any more but something like DBNull.Value.

Use a search engine and look at how other people are doing data access. Microsoft has some junk that may be helpful. Look for their Application Block patterns, specifically, the Data one.

Cheers!

kreplech
Starting Member

10 Posts

Posted - 2007-11-19 : 12:45:57

quote:
Originally posted by Lamprey
1. You do not need to use transaction like your example sproc. An INSERT or an UPDATE are atomic by definition, so they are already in a "transaction." However, if you need to do multiple steps then you may want to wrap all the steps with a transaction.

K-Thanks.

quote:
Originally posted by Lamprey
2. There are Dynamic SQL wrappers that will handle a lot of this stuff for you. Weather they are good or not is still being debated, but you should now how this stuff works before you obfuscate this stuff away with someone else’s generator.

Ah-ha! That's what I've been saying - but folks are constantly "dissing" dynamic sql. I need to read up more on the do's and dont's of dSQL... hey, what are you referring to: "someone else’s generator"?

quote:
Originally posted by Lamprey
3. In general, you would create an INSERT and an UPDATE stored procedure (Or possible one UPSERT). Then you call the appropriate stored procedure based upon what you want to do.

Can you elaborate on UPSERT techniques? Would it be similar to how I've used @actionFlag to determine whether the UPDATE or INSERT command will execute?

quote:
Originally posted by Lamprey
Also in general you would pass all the parameters for that table (or operation or whatever) and Insert or Update all the columns (yes there are exceptions).

Exceptions? Such as?

quote:
Originally posted by Lamprey
4. You can pass NULL values to your stored procedure. I don't remember what it is off the top of my head any more but something like DBNull.Value.

I'll look into this. Thanks.

Any suggestions on where to find resources for more advanced SQL topics? It seems like most info out there is very basic / beginner tutorials.

Thanks again,
M

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-19 : 16:46:44

For upserts there are several ways to do it, here is a sample of two:
If you are using an ID based system then you could use an ID as your "flag." If you have control then you should know whether or not the record exists before you do anything.

CREATE UpsertLogon
(
	@FirstName NVARCHAR(50),
	@LastName NVARCHAR(50),
	@LogonID INT NULL OUTPUT
)
AS

SET NOCOUNT ON

IF @LogonID > 0
BEGIN
	UPDATE 
		Logon
	SET 
		FirstName = @FirstName,
		LastName = @LastName
	WHERE
		LogonID = @LogonID
END
ELSE
BEGIN
	INSERT Logon
	(
		FirstName,
		LastName
	)
	VALUES
	(
		@FirstName,
		@LastName
	)

	SET @LogonID = SCOPE_IDENTITY()
END

Or if you are using a different method you might try something like this:

CREATE UpsertLogon
(
	@Email VARCHAR(255),
	@FirstName NVARCHAR(50),
	@LastName NVARCHAR(50),
)
AS

SET NOCOUNT ON


UPDATE 
	Logon
SET 
	FirstName = @FirstName,
	LastName = @LastName
WHERE
	Email = @Email

IF @@ROWCOUNT = 0
BEGIN

	INSERT Logon
	(
		Email,
		FirstName,
		LastName
	)
	VALUES
	(
		@Email
		@FirstName,
		@LastName
	)

	SET @LogonID = SCOPE_IDENTITY()
END

Exceptions to Updating every field might be something like if you have an indexed view on some large tables and updating data that did not change would hurt performance via the view re-indexing or something along those lines.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources