How to concatenate variables into SQL strings

Question

I need to concatenate a variable table name into my SQL query such as the following     ALTER FUNCTION fn myfunction  KeyValue text  BEGIN      INSERT INTO  tmpTbl1      SELECT  KeyValue AS fld1      FROM tbl    KeyValue   KeyValue fld1   I also attempted the following but it told me I had to declare the table variable   ALTER FUNCTION fn myfunction  KeyValue text   KeyTable text       FROM  KeyTable fld1

User · Answer

You could make use of Prepared Stements like this   set  query   concat   select name from      set  query   concat   table name     where condition        prepare stmt from  like q  execute stmt

User · Answer

You can accomplish this  if I understand what you are trying to do  using dynamic SQL    The trick is that you need to create a string containing the SQL statement  That s because the tablename has to specified in the actual SQL text  when you execute the statement  The table references and column references can t be supplied as parameters  those have to appear in the SQL text   So you can use something like this approach   SET  stmt    INSERT INTO  tmpTbl1 SELECT      KeyValue          AS fld1 FROM tbl     KeyValue  EXEC   stmt    First  we create a SQL statement as a string  Given a  KeyValue of  Foo   that would create a string containing    INSERT INTO  tmpTbl1 SELECT Foo AS fld1 FROM tblFoo    At this point  it s just a string  But we can execute the contents of the string  as a dynamic SQL statement  using EXECUTE  or EXEC for short      The old-school sp executesql procedure is an alternative to EXEC  another way to execute dymamic SQL  which also allows you to pass parameters  rather than specifying all values as literals in the text of the statement     FOLLOWUP  EBarr points out  correctly and importantly  that this approach is susceptible to SQL Injection   Consider what would happen if  KeyValue contained the string    1 AS foo  DROP TABLE students  --     The string we would produce as a SQL statement would be    INSERT INTO  tmpTbl1 SELECT 1 AS foo  DROP TABLE students  -- AS fld1        When we EXECUTE that string as a SQL statement   INSERT INTO  tmpTbl1 SELECT 1 AS foo  DROP TABLE students  -- AS fld1 FROM tbl1 AS foo  DROP       And it s not just a DROP TABLE that could be injected  Any SQL could be injected  and it might be much more subtle and even more nefarious   The first attacks can be attempts to retreive information about tables and columns  followed by attempts to retrieve data  email addresses  account numbers  etc    One way to address this vulnerability is to validate the contents of  KeyValue  say it should contain only alphabetic and numeric characters  e g  check for any characters not in those ranges using LIKE     A-Za-z0-9     If an illegal character is found  then reject the value  and exit without executing any SQL

[sql] How to concatenate variables into SQL strings

Examples related to sql

Examples related to sql-server-2008