none
setup variable in SQL command

    Question

  • Hello,

    I have a variable which will get a value from a table. I then have a SQL command will do update based on the variable I have to replicate zeros, like update A set col = replicate('0', ?-len(col)) + col where len(col) < ?

    My question is if I map parameter set, it has to setup 2 parameters with the same name and value? I cannot think of any other ways to do it. Can anybody give me suggestions?

    Thanks.

    Thursday, July 08, 2010 1:44 AM

Answers

  • My expression -

    "update A set col = replicate('0', " + (DT_WSTR,10)@[User::Len] + "-len(col)) + col where len(col) < " + (DT_WSTR, 10)@[User::Len]

    The Result -

    update A set col = replicate('0', 555-len(col)) + col where len(col) < 555

     

    I should explain, just like T-SQL you cannot concatenate a string and an integer. Hence the cast syntax - (DT_WSTR, 10)@[User::Len]

     

     


    http://www.sqlis.com | http://www.konesans.com
    Thursday, July 08, 2010 2:00 PM

All replies

  • Hello,

    Write a stored procedure, and pass only one parameter and then inside you can use it twice or depends on your requirement.

    Thanks

    Thursday, July 08, 2010 3:16 AM
  • You can use expressions to construct your SQL statement - either the property expression of the Execute SQL Task's SqlCommand property, or by setting an expression on a variable, and using that variable in the Execute SQL Task.
    Todd McDermid's Blog Talk to me now on
    Thursday, July 08, 2010 4:13 AM
  • Thanks for all the replies!

    Since we don't want to add more stored procedure to the existing package, we decided to use expressions. I tried several times to setup, but failed. I will have one Execute SQL Task to have one result set to hold the variable which will get it from a table and this variable will be integer (I set it as int32). I then setup another variable (from property I set EvaluateAsExpression to TRUE) which will hold my update statement as below:

    "update A set col = replicate('0', " + @[User::Len] + "-len(col)) + col where len(col) < " + @[User::Len]

    but when I click Evaluate Expression, I got error as: The datatype DT_WSTR and DT_I4 are imcompatible for binary operator. What should I do next? I tried to search for some examples, but not much there. I used this link to help me. http://www.rafael-salas.com/2007/11/ssis-mapping-parameter-inside-of.html

    Thanks again.

    Thursday, July 08, 2010 1:51 PM
  • My expression -

    "update A set col = replicate('0', " + (DT_WSTR,10)@[User::Len] + "-len(col)) + col where len(col) < " + (DT_WSTR, 10)@[User::Len]

    The Result -

    update A set col = replicate('0', 555-len(col)) + col where len(col) < 555

     

    I should explain, just like T-SQL you cannot concatenate a string and an integer. Hence the cast syntax - (DT_WSTR, 10)@[User::Len]

     

     


    http://www.sqlis.com | http://www.konesans.com
    Thursday, July 08, 2010 2:00 PM
  • Hello,

    "update A set col=replicate('0',"+(DT_STR,10,1252) @[User::Len] + "-len(col))  where len(col) < " +(DT_STR,10,1252) @[User::Len]

    You need to check your query, maybe I missed something

    Thanks

    Thursday, July 08, 2010 2:12 PM
  • I tried to use your suggestion and it will not give me any imcompatible error message. But it didn't update as I planned either since update expression used default 0 for variable User::Len, not the one I selected from the table. As it didn't give me any error message, I even don't know where to look at.

    Please advice.

    Thursday, July 08, 2010 4:17 PM
  • Is zero the design-time value for User::Len?  When is User::Len set to something other than zero, and by what?  Are there precedence constraints before the Execute SQL Task that uses User::Len?
    Todd McDermid's Blog Talk to me now on
    Thursday, July 08, 2010 4:24 PM
  • I have another Execute SQL Task before, and will have SQL direct input to select col1 from table A where col2 = 3. Also from this Execute SQL Task, I set result as single row, so I have set Result Name as 0, variable name as User::Len. But at design-time, I used Int32 as data type and default value as 0 for variable User::Len.

    But the actual value should be 9 instead of 0, and it seems not passed to the update SQL.

    Thursday, July 08, 2010 4:29 PM
  • Doesn't seem like you should have a problem, but try setting the DelayValidation property of the SQL statement variable to True, and possibly on the second Execute SQL Task as well...
    Todd McDermid's Blog Talk to me now on
    Thursday, July 08, 2010 4:33 PM
  • It suddenly works with/without to set DelayValidation. Amazing.

    Thanks everybody!

    Thursday, July 08, 2010 4:50 PM
  • Sorry to bother everyone!

    I tested on test table, when I modifed package and tried to udpate the proper destination table, I got error as:

    Violation of PRIMARY KEY constraint 'PKA'. Cannot insert duplicate key in object A.

    I don't think I used insert anywhere, but why I got this error?

    Thursday, July 08, 2010 7:19 PM
  • Can anybody give me some advices?

    Thanks.

    Thursday, July 08, 2010 8:22 PM
  • Sorry, my problem with the table.
    Thursday, July 08, 2010 8:31 PM
  • Are you using an OLE DB Destination in a Data FLow, or just an UPDATE statement in an Execute SQL Task?  That message is direct from SQL Server - I'm not sure if it reports an "insert" problem if you try to update a column with a value that would violate a primary key constraint or not.

    Bottom line - you're modifying your table with instructions that would cause duplicate primary keys to exist, and your constraints inside SQL Server are preventing that.

    I'd suggest using SQL Profiler to examine the commands you're sending to the server.  Find the one that causes the error, then work with it in SSMS to find out what action(s) are causing SQL to reject the command due to the constraint violation.


    Todd McDermid's Blog Talk to me now on
    Thursday, July 08, 2010 9:15 PM