Solved

Dynamic SQL

Posted on 2011-03-19
2
271 Views
Last Modified: 2012-05-11
I am having trouble with dynamic sql statements.  Here is what I need:

I will have a table variable, a variable for machine name and a variable for manufacturing order (@MO).  If the value in the table variable is ‘PREMIER’, the table name is PREMIER_SO_MO and the MO is MO-12345, I want the statement to say:

'SELECT * INTO PREMIER_SO_MO FROM PCT_SNUGZ_FILTER WHERE MACHINE = ‘PREMIER’ AND LEFT([WORK ORDER NUMBER], 9) = 'MO-12345’

If I test by using this code:

DECLARE @MACHINE VARCHAR(31), @TBLNAME1 VARCHAR(50), @MACHINE VARCHAR(50), @COMMAND VARCHAR(1000)

SELECT @MACHINE = ‘PREMIER’
SELECT @TBLNAME1 = ‘PREMIER_SO_MO’
SELECT @MO = ‘MO-12345’

SELECT @COMMAND = 'SELECT * INTO ' + @TBLNAME1 + ' FROM PCT_SNUGZ_FILTER WHERE MACHINE = ''' + @MACHINE1 + ''' AND LEFT([WORK ORDER NUMBER], 9) = ''' + @MO + ''''
      print @machine
      print @tblname1
      print @command

I get a value printed for @machine,and @tblname1, but @command contains a NULL value and hence does not print.

However, if I test using this code:

SELECT @COMMAND = 'SELECT * INTO ' + @TBLNAME1 + ' FROM PCT_SNUGZ_FILTER WHERE MACHINE = ''' + @MACHINE1 + '''’
      print @machine
      print @tblname1
      print @command

I do get a value printed for @command:

SELECT * INTO premier_SO_MO FROM PCT_SNUGZ_FILTER WHERE MACHINE = 'premier'

So, the problem seems to be the part where I try to put in the @MO variable.  Can anyone tell me how to structure this dynamic SQL statement so the value in the @COMMAND variable ends up being:

'SELECT * INTO PREMIER_SO_MO FROM PCT_SNUGZ_FILTER WHERE MACHINE = ‘PREMIER’ AND LEFT([WORK ORDER NUMBER], 9) = 'MO-12345’

Thanks,

T
0
Comment
Question by:thoecherl
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35173896

This is only 8 characters
@MO = ‘MO-12345'

And you are using LEFT of 9 characters
LEFT([WORK ORDER NUMBER], 9) = 'MO-12345'
0
 

Author Closing Comment

by:thoecherl
ID: 35173927
How utterly embarrasing.  Thanks for catching my gaffe so quickly.

T
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question