Dynamic SQL

Hi I built a cursor to update several fields. I tried the SQL sentence that I set as sSQL1 with a value and it worked, but when I put it into the cursor I'm getting an error that I assume is on the following part:

 ''('' + [Division] + '') - ('' + [Category] + '') - ('' + [ProductFamily] + '')''

as seems that is not formatting the field + text as needed.

When I tried the following it worked :

UPDATE table1 SET [PF - (Product A) - (Subproduct B) - (Subproduct C)] = lic
    FROM (select mssendcustomertpid, sum(lic) as lic from table2 where  '(' + Division + ') - (' + Category+ ') - (' + ProductFamily+ ')' ='PF - (Product A) - (Subproduct B) - (Subproduct C)' group by customertpid) B
    INNER JOIN table1 A ON B.customertpid = A.customertpid

This is the cursor:

DECLARE @prod nvarchar(100), @sSQL NVARCHAR(500), @sSQL1 NVARCHAR(1000)

DECLARE cr cursor local FAST_FORWARD for
SELECT DISTINCT product
FROM table3 order by 1

open cr
fetch next from cr into @prod
while @@fetch_status=0
        begin
    SET @sSQL1= 'UPDATE table1 SET [PF - ' + @prod + '] = lic
    FROM (select mssendcustomertpid, sum(lic) as lic from table2 where  ''('' + [Division] + '') - ('' + [Category] + '') - ('' + [ProductFamily] + '')'' =' + @prod + ' group by customertpid) B
    INNER JOIN table1 A ON B.customertpid = A.customertpid)'
                EXECute(@sSQL1)
        end
close cr
deallocate cr

Any help?

Thanks

Ezequiel

September 7th, 2015 3:04pm

You need to cast any numeric data types to VARCHARS... You can't concatenate numeric data to rest of the dynamic text like you're doing.

Also... Why are you doing this in a cursor? From what I can see in the code you posted, you should be able to do this in a set based solution.

 
Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 3:13pm

Thanks Jason..I'm trying to iterate the process for several products...what is your suggestion?
September 7th, 2015 4:41pm

Can you post structures of your tables? I don't understand what you're doing.
Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 4:54pm

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You failed in the worst ways possible. 

We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

>>  I built [sic: declared] a cursor to update several field [sic]s [sic]. <<

Columns are not anything like fields[sic]. Using cursors tells the world you are not yet a competent SQL programmer. You do not know the basic terms of this language and still want to write auto-coder for a 1970's tape file system. 

Why did you think that posting fragments of text would help us? We need to see the DDL. 

>> .. that is not formatting the field [sic] + text as needed. <<

Gee, we do not do formatting in the database tier. That was really bad COBOL. 

Did you know that the old Sybase UPDATE.. FROM.. does not work?? It fails to give an error message or a predictable result with a 1:M relationship! Go tell your boss that you need to audit the entire database because of this. 

Can you follow the forum rules? Can you flush this stuff and start over? You clearly have no idea what you are doing, so you are mimicking a 1960's tape file system.  


September 7th, 2015 6:15pm

Thanks for your response. Let me tell you that I think that your answer is very rude. This and every forum is to share knowledge and to learn. The expectation is that no everyone is as a wise man like you. I appreciate your answer and I'll try to find some solution on a place that people feel that are not superior and that can put their feet on the earth.

You might be a genious in SQL but, with all respect, you lack good manners.

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 9:32pm

To debug dynamic SQL, add a print statement before the exec:

PRINT @sSQL1

The printed command should be valid.

September 7th, 2015 11:16pm

you can ignore that baldhead, if had come to this forum before you would have realized this guy is more willing to be rude than be helpful.

Except this guy, the others in this forum are nice and always willing to help.
  • Edited by HoroChan 3 hours 38 minutes ago
Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 11:26pm

I'll work with the PRINT option and debug. I'm a true believer about the collaboration essence of this interaction.

Special thanks to Kalman and HoroChan for the help and kind words

Ezequiel

September 7th, 2015 11:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics