May 3, 2013 at 5:36 pm
HI,
I have small confusion send the "update" query in execute statement.
My requirement is:
Without using either variable or set quoted_identifier off
how can i sent the query through procedure.
my usage as below. its not working for two queries below.
Exec proce_Name 'update table set column='TEST' where id=1'
or
Exec proce_Name "update table set column='TEST' where id=1"
-------Giri
May 4, 2013 at 3:30 am
What does the procedure proce_Name do?
btw, I strongly recommend against any architecture or design that has pieces of queries being passed around as parameters, it gets hugely complex, there are almost always security vulnerabilities as a result and it's a pain to work with them,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2013 at 6:45 am
The procedre limit the records based on sending query.
Suppose if we need to update 10 records ,we need to pass the update query and limit value so it will be updated 10 records...and here query is not only update and also work for. SELECT AND INSERT etc ...IS
There any default server level configuration to allow double quotes for string values
May 4, 2013 at 9:16 am
This works
Exec proce_Name 'update table set column=''TEST'' where id=1'
Those aren't double quotes, they're escaped single quotes.
I still strongly recommend against any design that requires the passing of queries or parts of queries around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2013 at 1:30 pm
Thanks lot
May 4, 2013 at 2:28 pm
I agree with Gail here. Passing TSQL through as input is just asking for trouble.
Better would be to move the code into a procedure something like this:
OLD METHOD
Exec proce_Name 'update table set column=''TEST'' where id=1'
NEW METHOD
EXEC dbo.SampleFoo 'MyTable','MyColumn',1,'TEST','UPDATE'
CREATE PROCEDURE dbo.SampleFoo
@TableName SYSNAME
,@ColumnName SYSNAME
,@ID INT
,@NewValue VARCHAR(50)
,@ActionType VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @strSQL NVARCHAR(4000)
IF @ActionType = 'UPDATE'
BEGIN
SET @strSQL =
@ActionType + ' '
+ @TableName
+ ' SET '
+ @ColumnName + ' = ''' + @NewValue + ''
+ 'WHERE ID = ' + @ID
EXEC sp_executeSQL @strSQL
END
ELSE IF @ActionType = 'INSERT'
BEGIN
SET @strSQL =
@ActionType + ' INTO ' + @Tablename +
+ '(' + @ColumnName + ')'
+ VALUES +
+ '(' + @NewValue + ')'
EXEC sp_executeSQL @strSQL
SELECT @ID = SCOPE_IDENTITY() --this gets the new ID after insertion
END
May 4, 2013 at 3:33 pm
To be honest, I wouldn't recommend that either. It's vulnerable to SQL injection and it completely violates the software engineering principal of single responsibility. In front end development no one would consider writing a function that can update properties of an employee object, vehicle object, movie object or accounting collection depending on parameter values, so why do it in a stored procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply