Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL

Posted on 2011-03-19
2
Medium Priority
?
304 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:
Ephraim Wangoya earned 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

886 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