locked
Dynamic SQL RRS feed

  • Question

  • 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

    Monday, September 7, 2015 7:02 PM

All replies

  • 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.

     

    Jason Long

    Monday, September 7, 2015 7:11 PM
  • Thanks Jason..I'm trying to iterate the process for several products...what is your suggestion?
    Monday, September 7, 2015 8:38 PM
  • Can you post structures of your tables? I don't understand what you're doing.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 7, 2015 8:52 PM
  • 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.  



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 7, 2015 10:12 PM
  • 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.

    Tuesday, September 8, 2015 1:29 AM
  • To debug dynamic SQL, add a print statement before the exec:

    PRINT @sSQL1

    The printed command should be valid.





    Kalman Toth Database & OLAP Architect Artificial Intelligence
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014



    Tuesday, September 8, 2015 3:14 AM
  • 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 Tuesday, September 8, 2015 3:24 AM
    Tuesday, September 8, 2015 3:24 AM
  • 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

    Tuesday, September 8, 2015 3:31 AM