-2

On a Sybase database, can i do something like the following, which does not do anything but throw an error. I would like to avoid using variables. If it doesn't work without variables, is there an easy way to execute a statement for each row i get from a select?

execute ( '
select * from table
where ID = ''123456''
')
kutschkem
  • 7,826
  • 3
  • 21
  • 56

3 Answers3

0

No, you have the query enclosed in quotes, so it is not a query but a string. If it were a query the results would probably be a set of rows from a table and would not be executable as sql statements

automatic
  • 2,727
  • 3
  • 34
  • 31
  • To make your answer complete, you could tell in what cases something can be executed. I know that if i store the string in a variable and use execute on the variable it will work, but why? Will the result of a query that returns exactly one row and column with a sql-string in it be executed, for example? – kutschkem Feb 27 '14 at 11:57
0

This is called dynamic SQL and should work with the exec command.

Sjoerd
  • 74,049
  • 16
  • 131
  • 175
0

Yes you can do it, as Sjoerd said with the exec command, but it isn't advisable in the form you showed it for two main reasons:

  1. concatenating a string together with the parameters makes you vulnerable to SQL Injection
  2. By changing the parameters in the string, you also change the hash of the SQL, not allowing the SQL Server to cache the execution plans.

To overcome this problems you can execute dynamic SQL with parameters (T-SQL: How to use parameters in dynamic SQL?) with the stored procedure sp_executesql

Community
  • 1
  • 1
peter
  • 14,348
  • 9
  • 62
  • 96
  • haha, i was really going to concatenate, not user input though. I generate sets of queries that would be tedious to write by hand, store them in a table, and execute them later. Are parameters still useful in that use case? – kutschkem Feb 27 '14 at 12:00
  • SQL server needs to parse the query and create an execution plan. This takes some time and that's why the execution plans are cashed together with the hashed query. If you use parameters you can take advantage of this. Otherwise the query string will be different every time. – peter Feb 27 '14 at 13:28