Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hassle free parameters in dynamic sql. Expand / Collapse
Author
Message
Posted Thursday, April 24, 2014 3:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:19 AM
Points: 100, Visits: 563
Comments posted to this topic are about the item Hassle free parameters in dynamic sql.
Post #1564903
Posted Saturday, April 26, 2014 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2014 11:21 PM
Points: 1, Visits: 25
I need some help
I have a table in SQL which has following columns
tablename columnname
aaaa cccc1
aaaa cccc2
aaaa cccc4
bbbb dddd1
bbbb dddd2
bbbb dddd3

dynamically I would like to build update statement and execute in T-SQL. I can build a script that update the table one column ata a time

can you suggest some logic that can generate script which can update all colunms in one single statement

similar to the below statement
update aaaa set cccc1 = '<some value>, cccc2 =<some value>, cccc3 = <some value>

Thanks
Arun
Post #1565303
Posted Saturday, April 26, 2014 9:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 1,723, Visits: 4,542
IMHO this is one way of generating a dynamic sql query but I have some doubts. It will certainly not produce reusable plans nor benefit from the usage of sp_executesql. It is also more of a hassle than simply write
DECLARE @SQL_STR NVARCHAR(MAX) = N'
SELECT
column1
,column2
,column3
FROM #t
WHERE column3 = @COL_VALUE';
DECLARE @SQL_PARAM NVARCHAR(MAX) = N'@COL_VALUE INT';
EXEC SP_EXECUTESQL @SQL_STR,@SQL_PARAM,6;

Post #1565333
Posted Thursday, May 15, 2014 4:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
This script may be ok to use on some occasions but if the entry of the parameters is from an external input, e.g. a web page, then it could be vulnerable to SQL injection - something that is not possible using parameters.
Post #1571228
Posted Thursday, May 15, 2014 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:19 AM
Points: 100, Visits: 563
Jonathan AC Roberts (5/15/2014)
...but if the entry of the parameters is from an external input, e.g. a web page, then it could be vulnerable to SQL injection - something that is not possible using parameters.


Thanks for the comment, but calling this from a web page, as you said, would be really scary...
Post #1571408
Posted Thursday, May 15, 2014 3:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
This is a very clever way to table-drive a dynamic query.

R Glen Cooper
Post #1571538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse