Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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

> 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.
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc