T Hoecherl
asked on
Dynamic SQL
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
T