Jump to content

SQL Best Practices


Recommended Posts

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 statementif @actionFlag=0 BEGIN TRANSACTION insert into someTable (allValues) values (allValues)COMMITif @actionFlag=1BEGIN 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 testedCOMMITGOC# (well really just pseudo-code)protected void callSproc(int actionFlag, int RecID, string field1, string field2, ... etc){open databasecall putData // sproc nameadd paramsexecute}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.Also, I'll be posting this identically to the C# forum because I'm not sure if it requires a fix from the SQL side or the C# side.I appreciate any help at all!!!Thanks,M

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...