CODEWITHSUNDEEP
sql-server
satyajit
satyajit

Reputation: 2700

Changing a query to dynamic sql

I have the following query which takes the XML input and stores them into temp tables. I need to make the query dynamic or a function which will adjust to any table or column number modification dynamically.How do I do that:

DECLARE @sXML NVARCHAR(4000) 
DECLARE @hDoc INT  
SET @sXML = ' 
<Root> 
 <Item> 
  <Key>1</Key> 
  <Test1>A</Test1> 
  <Test2>A2</Test2> 
 </Item> 
 <Item> 
  <Key>2</Key> 
  <Test1>B</Test1> 
  <Test2>B3</Test2> 
 </Item> 
</Root> 
'  
CREATE TABLE #tabletest 
( 
 [Key] INT, 
 Test1 NVARCHAR(50), 
 Test2 NVARCHAR(50) 
) 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @sXML 
INSERT INTO #tabletest SELECT * FROM OPENXML (@hDoc, '/Root/Item', 2) WITH #tabletest 
EXEC sp_xml_removedocument @hDoc 

select * from #tabletest  
DROP TABLE #tabletest

Upvotes: 0

Views: 78

Answers (1)

Tom Bowen
Tom Bowen

Reputation: 8514

To create Dynamic SQL you need to build up the query as a string. Using If Statements and other logic to add your variables...etc.

Declare a text variable and use this to concatenate together your desired SQL.

You can then execute this code using the "EXEC" command

Example:

DECLARE @SQL VARCHAR(100)
DECLARE @TableOne VARCHAR(20) = 'TableOne'
DECLARE @TableTwo VARCHAR(20) = 'TableTwo'
DECLARE @SomeInt INT

SET @SQL = 'INSERT INTO '

IF (@SomeInt = 1)
    SET @SQL = @SQL + @TableOne

IF (@SomeInt = 2)
    SET @SQL = @SQL + @TableTwo

SET @SQL = @SQL + ' VALUES....etc'

EXEC (@SQL)

However, something you should really watch out for when using this method is a security problem called "SQL Injection".

You can read up on that here: http://msdn.microsoft.com/en-gb/library/ms161953(v=sql.105).aspx

One way to guard against SQL injection is to validate against it in your C# code before passing the variables to SQL-Server.

An alternative way (or probably best used in conjecture) is instead of using the "EXEC" command, use a built in stored procedure called "sp_executesql".

Details of which can be found here: http://msdn.microsoft.com/en-gb/library/ms188001.aspx How it is used is detailed here: http://msdn.microsoft.com/en-gb/library/ms175170(v=sql.105).aspx

You'll have to build your SQL slightly differently and pass your parameters to the stored procedure as well as the @SQL.

Upvotes: 1

Related Questions