Syntx Error Combining columns

[soulpumpkin]

I'm getting a syntax error when running a stored procedure. I'm trying to extract values from one table and store them in a temp table. The below code is from stored procedure:

execute('INSERT INTO tblTemp SELECT Brand, Measure, Market, 
                                    Week, NULL, NULL, NULL, 
                                    Value, Brand+' + '.' + '+Measure 
                                    FROM tblValues_' +  @ProjectID)

Here is the code that gives me the correct results from the SQL Query Analyzer:

SELECT Brand,Measure,Week,NULL,NULL,NULL,Brand+'.'+Measure as code FROM tblValues_23

I'm sure it is simple, but after staring at it and trying a few different things I'm starting to get frustrated and blinding myself.

Any help would be appreciated.
Thanks,

Soul Pumpkin

 

[jbenson001]

Are you trying to combine the values in the columns brand and Measure?

 

[SQLDenis]

replace print with execute

declare @ProjectID int
select @ProjectID = 23
print('INSERT INTO tblTemp SELECT Brand, Measure, Market, 
                                    Week, NULL, NULL, NULL, 
                                    Value, Brand+' + '''.''' + '+Measure 
                                    FROM tblValues_' +  convert(varchar,@ProjectID))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[soulpumpkin]

yes, the output should look liks this:

AAA	5EQU	111	NULL	NULL	NULL	AAA.5EQU
AAA	5SUN	111	NULL	NULL	NULL	AAA.5SUN
AAA	5EQU	111	NULL	NULL	NULL	AAA.5EQU
AAA	5SUN	111	NULL	NULL	NULL	AAA.5SUN
AAA	4EQU	111	NULL	NULL	NULL	AAA.4EQU
AAA	4SUN	111	NULL	NULL	NULL	AAA.4SUN
AAA	5EQU	111	NULL	NULL	NULL	AAA.5EQU
AAA	5SUN	111	NULL	NULL	NULL	AAA.5SUN
AAA	4EQU	111	NULL	NULL	NULL	AAA.4EQU
AAA	4SUN	111	NULL	NULL	NULL	AAA.4SUN
AAA	5EQU	111	NULL	NULL	NULL	AAA.5EQU
AAA	5SUN	111	NULL	NULL	NULL	AAA.5SUN
AAA	4EQU	111	NULL	NULL	NULL	AAA.4EQU
AAA	4SUN	111	NULL	NULL	NULL	AAA.4SUN
AAA	EQU1	111	NULL	NULL	NULL	AAA.EQU1
AAA	EQU1	111	NULL	NULL	NULL	AAA.EQU1
AAA	SUN1	111	NULL	NULL	NULL	AAA.SUN1
AAA	SUN1	111	NULL	NULL	NULL	AAA.SUN1
AAA	5EQU	111	NULL	NULL	NULL	AAA.5EQU
AAA	5SUN	111	NULL	NULL	NULL	AAA.5SUN
AAA	4EQU	111	NULL	NULL	NULL	AAA.4EQU
AAA	4SUN	111	NULL	NULL	NULL	AAA.4SUN

Soul Pumpkin

 

[SQLBill]

After you add the "PRINT", let us know what it returns. Also, what is the error that you are getting? You say it's a syntax error, but what is the actual error message?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

the problems are the quotes Brand+' + '.' + '+Measure you have to tripple them
and also if @ProjectID is an int then you need to convert to varchar, the code I supplied should work

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Take the Brand and Measure columns out of the quotes. Right now you have them inside and it will just print the words "Brand" and "Measure" not the values.

execute('INSERT INTO tblTemp SELECT Brand, Measure,Market, 
Week, NULL, NULL,NULL,Value,' + Brand + '.' + Measure + ' 
FROM tblValues_' +  @ProjectID)

Jim

 

[soulpumpkin]

the actual error is:

Line 1:Incorrect syntax near +.

I'll try changing the execute to print, and let you know.

Thanks,

Soul Pumpkin

 

[soulpumpkin]

the print statement let the procedure run without a syntax error, but didn't insert the values into the temp table.

Soul Pumpkin

 

[SQLSister]

The purpose of the print statement is to see what SQL is being created onthe fly for debugging purposes not to insert the values. That's why Dennis wanted to see what result you got from it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[soulpumpkin]

understood, it didnot produce anything, but it didnot error out either.

Soul Pumpkin

 

[soulpumpkin]

SQLSister, I'm sorry - had a burp in the system that didn't let me see the results. It is fixed, I ran it again and saw the problem.

Dennis - you the man. The problem was an extra space in the code that shouldn't have been there. I'm not used to debugging stored procedures, thanks for your help. Star coming at 'cha.

Thanks again,

Soul Pumpkin