Solved

Dynamic SQL

Posted on 2011-03-19
2
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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