Sql Server Programming :: Problems with concatenation in cursor


	Problems with concatenation in cursor I've got a sProc that gets two fields (employeeID and email), based on certain criteria. Then, I create a cursor, in order to send emails to the returned email addresses, but I need to send them to specific web pages, based on their EmployeeID - - The problem apparently has something to do with the way I'm concatenating the web url - - "Server: Msg 245, Level 16, State 1, Procedure prMlg_SendEmailNotification, Line 52 Syntax error converting the varchar value 'You have submissions which are older than 48 hours. Please go to http://myServer/myWebPage.aspx?id=' to a column of data type int." here's a little of the code: FETCH NEXT FROM mCursor INTO @EEID, @Email END CLOSE mCursor DEALLOCATE mCursor DECLARE @q varchar(100) DECLARE @To varchar(2000) DECLARE @msg varchar(3000) SELECT @To=@Email SELECT @q='You have Submissions needing attention (TEST).' SET @msg= 'You have submissions which are older than 48 hours. Please go to http://myServer/myWebPage.aspx?id=' SET @msg=@msg + @EEID SET @msg=@msg + ' and Approve, Deny or Cancel each submission.' any ideas on how I need to change this so it will work? Thanks in advance @EEID is most likely some numeric datatype, so you have to use CAST round it to cast it to a string datatype. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi - Hide quoted text - "Elmo Watson" <e@whereveryouare.com> wrote in message news:uZx48CgoHHA.3660@TK2MSFTNGP04.phx.gbl... > I've got a sProc that gets two fields (employeeID and email), based on certain criteria. > Then, I create a cursor, in order to send emails to the returned email addresses, but I need to > send them to specific web pages, based on their EmployeeID - - > The problem apparently has something to do with the way I'm concatenating the web url - - > "Server: Msg 245, Level 16, State 1, Procedure prMlg_SendEmailNotification, Line 52 > Syntax error converting the varchar value 'You have submissions which are older than 48 hours. > Please go to http://myServer/myWebPage.aspx?id=' to a column of data type int." > here's a little of the code: > FETCH NEXT FROM mCursor INTO @EEID, @Email > END > CLOSE mCursor > DEALLOCATE mCursor > DECLARE @q varchar(100) > DECLARE @To varchar(2000) > DECLARE @msg varchar(3000) > SELECT @To=@Email > SELECT @q='You have Submissions needing attention (TEST).' > SET @msg= 'You have submissions which are older than 48 hours. Please go to > http://myServer/myWebPage.aspx?id=' > SET @msg=@msg + @EEID > SET @msg=@msg + ' and Approve, Deny or Cancel each submission.' > any ideas on how I need to change this so it will work? > Thanks in advance > SET @msg=@msg + @EEID SET @msg = @msg + RTRIM(@EEID); or SET @msg = @msg + CONVERT(VARCHAR(32), @EEID); If @EEID can be NULL then you may want to do: SET @msg = @msg + COALESCE(RTRIM(@EEID), ''); or SET @msg = @msg + COALESCE(CONVERT(VARCHAR(32), @EEID), ''); You should follow the same practice for all variables that are numeric (or declare the variable as a string in the first place, and apply the conversion when assigning the value). -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006 -----------------------------------------------Reply----------------------------------------------- >> I've got a sProc [not a mere Procedure, but an sProc?] that gets two fields [sic] (employeeID and email), based on certain criteria. << Why are you still using camelCase? It is the worst way to format data. >> Then, I create a cursor, in order to send emails to the returned email addresses, but I need to send them to specific web pages, based on their EmployeeID << You should write no more than five cursors in your entire career. You are confusing the database side of the problem with the application side. Let the application handle the emails, not the database. You are using SQL as a file system, tightly coupled to the app. -----------------------------------------------Reply----------------------------------------------- On May 29, 12:14 pm, --CELKO-- <jcelko@earthlink.net> wrote: > >> I've got a sProc [not a mere Procedure, but an sProc?] that gets two fields [sic] (employeeID and email), based on certain criteria. << > Why are you still using camelCase? It is the worst way to format > data. Why are you talking about camelCase when the question is about concatenation? > >> Then, I create a cursor, in order to send emails to the returned email addresses, but I need to send them to specific web pages, based on their EmployeeID << > You should write no more than five cursors in your entire career. Five, huh? Where did you get that number, exactly? I recall a while back you saying it was two... where'd the three new ones come from? In any event, how many cursors you should write in your career kinda depends on your career. If you're saying they should be avoided when a set solution is available, then we agree. But without knowing the specifics of his need, it's just silly and pointless to jab at him for using one. > You are confusing the database side of the problem with the application > side. Let the application handle the emails, not the database. Which application? > You are using SQL as a file system, tightly coupled to the app. Looked more like he was using it to store a list of employees.