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.
| Author |
Topic |
|
Tapalotapus
Starting Member
22 Posts |
 Posted - 2006-10-23 : 20:16:29
|
| Is this a good case to use Dynamic SQL or is there a better option? I cut it down a bit it did have 50 fields. ThanksOverview:Basically the app is checking to see if the value changed if it has it sends the updated value if not the value is ''. This procedure builds the update statement for the fields that have a value.CREATE PROCEDURE dbo.upProcedure@SID bigint, @FName varchar(20), @LName varchar(20),@MI char(1), @Status char(1), @NewSID bigint OUTPUT, @ErrorCode int OUTPUTAS--Variable declarationsDECLARE @SQL varchar(8000), @LENStart intSET @ErrorCode = 0--Start SQL statementSET @SQL = 'UPDATE tblStudent SET 'SET @LENStart = LEN(@SQL)--Add fields to updateIF @FName <> '' SET @SQL = @SQL + 'FName = ''' + REPLACE(@FName, '''', '''''') + ''', 'IF @LName <> '' SET @SQL = @SQL + 'LName = ''' + REPLACE(@LName, '''', '''''') + ''', 'IF @MI <> '' SET @SQL = @SQL + 'MI = ''' + REPLACE(@MI, '''', '''''') + ''', 'IF @Status <> '' SET @SQL = @SQL + 'Status = ''' + REPLACE(@Status, '''', '''''') + ''', '--Is there anything to update?IF LEN(@SQL) = @LENStart BEGIN SET @NewSID = @SID RETURN ENDELSE --LastChangeDate SET @SQL = @SQL + 'LastChangeDate = GETDATE(), '--Cut off last commaSET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ' '--Add WHERE clauseSET @SQL = @SQL + 'WHERE (SID = ' + CAST(@SID AS varchar(20)) + ')'--Execute SQL statementEXEC(@SQL)SET @NewSID = @SID |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-23 : 20:18:17
|
| Please explain in words what the code is doing. Most of us don't have the time to figure out what something is doing on a free site such as this.Tara Kizer |
 |
|
|
Tapalotapus
Starting Member
22 Posts |
Posted - 2006-10-23 : 20:32:38
|
| This has beeen updated, sorry. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-24 : 04:01:50
|
| "Is this a good case to use Dynamic SQL or is there a better option?"1) I reckon what you have shown can be done in non-Dynamic SQL2) If you use dynamic SQL you have to provide direct table permissions to the user - which they can then use with Access, Excel, or any other Tom-Dick-and-Harry tool that they can get connected with.We are pretty stiff on (2) - we don't want no-stinking-user connecting direct to the database and messing around, so everything goes through Sprocs and no dynamic SQL. I water-down that rule as necessary, but Boy does everyone here have a hard time getting a Business Case for Dynamic SQL past me!Kristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-24 : 10:13:16
|
| Tapalotapus,before you consider using dynamic sql for anything, I strongly suggest you read and understand this page:http://www.sommarskog.se/dynamic_sql.htmlSqlSpec - a fast, cheap, and comprehensive data dictionary generator for SQL Server 2000 and 2005 - http://www.elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-24 : 10:23:28
|
| Haven't looked at www.sommarskog.se in a while, but nice to see the extensive additions to the text with regard to SQL2005.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Tapalotapus
Starting Member
22 Posts |
Posted - 2006-10-24 : 12:20:40
|
| I see now, actually I had started reading that article yesterday :)So this is a better way to do it.UPDATE tblStudent SET FName = CASE @FName WHEN <> '' THEN @FName ELSE FName END, LName = CASE @LName WHEN <> '' THEN @LName ELSE LName END, MI = CASE @MI WHEN <> '' THEN @MI ELSE MI END, Status = CASE @Status WHEN <> '' THEN @Status ELSE Status ENDThanks everyone |
|
|
|
|
|
|
|
|
Better option than dynamic sql?