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

HOW TO PASS UPDATE QUERY WITH EXECUTE COMMAND Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 5:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:00 PM
Points: 70, Visits: 240
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
Post #1449374
Posted Saturday, May 4, 2013 3:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 39,981, Visits: 36,348
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449399
Posted Saturday, May 4, 2013 6:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:00 PM
Points: 70, Visits: 240
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
Post #1449417
Posted Saturday, May 4, 2013 9:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 39,981, Visits: 36,348
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449425
Posted Saturday, May 4, 2013 1:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:00 PM
Points: 70, Visits: 240
Thanks lot
Post #1449458
Posted Saturday, May 4, 2013 2:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1449465
Posted Saturday, May 4, 2013 3:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 39,981, Visits: 36,348
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1449476
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse