Defining an SQL string and assigning it to a procedure variable

• blom0344

  SSCrazy

  Points: 2073

  More actions

  November 9, 2007 at 3:47 am

  #180133

  I have an T-SQL script that reads some 660 lines with many occurences of a certain date calculation:

  CONVERT(DATETIME, CONVERT(VARCHAR(23), rdb.period_year * 10000 + rdb.period_month * 100 + 1), 112)

  [/code]

  Where rdb is the table alias used furtheron in the script.

  Can I define a variable like:

  @date_from_year_month varchar(200)

  set @date_from_year_month = 'CONVERT(DATETIME, CONVERT(VARCHAR(23), rdb.period_year * 10000 + rdb.period_month * 100 + 1), 112)'

  [/code]

  And the use the declared variable in the sql part of the procedure in the next steps?

  Ties Blom

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 9, 2007 at 4:11 am

  #749782

  Ties

  No, but you can create a user-defined function instead. Look in Books Online for CREATE FUNCTION.

  John

• blom0344

  SSCrazy

  Points: 2073

  More actions

  November 9, 2007 at 5:21 am

  #749793

  Well, I was trying to prevent creating UDF's, cause this is just 1 example from many. Since this is allowed in VB, I hoped it would work in T-SQL.

  But thanks for your answer..