Passing variables in URL through stored procedure

Question

1.00/5 (1 vote)

See more:

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spc_getemail]
As
Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name  varchar(255)
DECLARE @ID    int
--Declare Cursor  to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail
-- Open cursor for fetching data in email variable
open EmailCursor 
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

EXEC  [dbo].[sp_send_mail] 
'a@a.com',
'a@g.com',
' Newsletter Submission',
'<a href=''http://someurl.com?id=' + CAST(@ID As varchar(20)) + '''>Please click here to fill your missing details',
'htmlbody', 
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id  is ' + @Email
print 'Email id  is ' + @ID
End;
Close EmailCursor
Deallocate EmailCursor
End;

What I have tried:

I have tried all escape sequences in sql to resolve.

Posted 1-Jun-17 2:00am

Abhi1 Kanobi

Updated 9-Jun-17 20:01pm

User 4180254

v5

Add a Solution

Comments

PeejayAdams 1-Jun-17 8:13am

It's very hard to say without seeing your definition for IDCursor and your declaration of @ID.

Abhi1 Kanobi 1-Jun-17 8:28am

the declaration is below.

Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int

--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select email From demoemail

DECLARE NameCursor Cursor For
Select name from demoemail

DECLARE IDCursor Cursor For
Select ID from demoemail

-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @Email

open NameCursor
Fetch Next from NameCUrsor into @Name

open IDCursor
Fetch Next from IDCUrsor into @ID

Richard Deeming 1-Jun-17 8:30am

The obvious problem is that you're missing the + between the different parts of the string:

'<a href=''http://newsletter.qendidate.com?id=' + @ID + '''>Please click here to fill your missing details </a>'

Also, you don't need three separate cursors; you just need one cursor which selects three fields.

Abhi1 Kanobi 1-Jun-17 8:56am

It's still giving error as "incorrect sysntax near +"

Abhi1 Kanobi 1-Jun-17 8:57am

USE [orion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[spc_getemail]
As
Begin
-- Declare Variable To Hold The Email Address Retrived
DECLARE @Email varchar(255)
DECLARE @Name varchar(255)
DECLARE @ID int
--Declare Cursor to loop through the table data
DECLARE EmailCursor Cursor For
Select ID,email,name from demoemail
-- Open cursor for fetching data in email variable
open EmailCursor
Fetch Next From EmailCursor into @ID,@Email,@Name
while(@@FETCH_STATUS = 0)
Begin
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)

EXEC [dbo].[sp_send_mail]
'2@a.com',
'a@a.com',
'Newsletter Submission',
'<a href=''someurl.com?id='+ID'''>Please click here to fill your missing details ',
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id is ' + @Email
print 'Email id is ' + @ID
End;
Close EmailCursor
Deallocate EmailCursor
End;

Richard Deeming 1-Jun-17 8:59am

Because you've not used the correct syntax!

What I posted:

'<a href=''http://newsletter.qendidate.com?id=' + @ID + '''...

What you've used:

'<a href=''http://newsletter.qendidate.com?id=' + @ID'''...

Look closely at the two, and play "spot the difference".

Abhi1 Kanobi 1-Jun-17 9:07am

ok,
but I used it the way you specified . please see below , the error is still there

EXEC [dbo].[sp_send_mail]
'a@a.com',
'a@a.com',
'Submission',
'<a href=''http://someurl.com?id=' + @ID + '''>Please click here to fill your missing details ',
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
print 'Email id is ' + @Email
print 'Email id is ' + @ID
End;

Richard Deeming 1-Jun-17 9:08am

And the error is?

Richard Deeming 1-Jun-17 9:09am

Try:

'<a href=''http://someurl.com?id=' + CAST(@ID As varchar(20)) + '''>Please click here to fill your missing details '

Abhi1 Kanobi 1-Jun-17 9:12am

the error still exists , same with the '+'and now along with @ID also

Richard Deeming 1-Jun-17 9:14am

AND THE ERROR IS????

If you want help to fix an error, you need to tell us what the error is. We're not psychic!

Abhi1 Kanobi 1-Jun-17 9:18am

the concatenation error is same , incorrect syntax near '+'

incorrect syntax near @id , expecting '(' or select ..

incorrect syntax near 20 , expecting '(' or select ..

Richard Deeming 1-Jun-17 9:22am

If you're getting those error messages, then you're not using the code I posted.

'<a href=''http://someurl.com?id=' + CAST(@ID As varchar(20)) + '''>Please click here to fill your missing details</a>'

Abhi1 Kanobi 1-Jun-17 9:28am

hello sir,
I am using it exactly the way u have specified sir

Richard Deeming 1-Jun-17 9:31am

Well you're clearly not, because if you were, you wouldn't be getting the two errors you posted.

Don't try to type the code, since you keep making mistakes. Copy the code from my comment, and paste it into your query.

Abhi1 Kanobi 1-Jun-17 9:37am

That's exactly what I am doing sir , copying the code from commnt .
is there anyway i can show u what I see? , this error is driving me nuts .:)

Richard Deeming 1-Jun-17 10:00am

Take a screen-shot, post it on an image sharing site like Imgur[^], and post the link to the image here.

Abhi1 Kanobi 1-Jun-17 10:25am

http://imgur.com/6677LfG

Richard Deeming 1-Jun-17 10:28am

The syntax highlighting looks OK, so there must be an error somewhere else in the code.

Abhi1 Kanobi 1-Jun-17 10:29am

do u want me to share the complete code?.

Richard Deeming 1-Jun-17 10:31am

Yes; if you can't see the error, maybe someone else will be able to spot it. :)

Abhi1 Kanobi 1-Jun-17 10:33am

i guess already , uploaded the whole code

Richard Deeming 1-Jun-17 10:43am

OK, it looks like you can't use + when you're passing a string as a parameter. You need to declare a separate variable instead:

DECLARE @body varchar(max) = '<a href=''http://someurl.com?id=' + CAST(@ID As varchar(20)) + '''>Please click here to fill your missing details</a>';
EXEC dbo.sp_send_mail
    'a@a.com',
    'a@g.com',
    'Newsletter Submission',
    @body,
    'htmlbody',
    ...

You're also missing the Fetch Next From call within the loop, and you'll need a CAST to print out the ID:

    print 'Email id is ' + CAST(@ID As varchar(20))
    Fetch Next From EmailCursor into @ID,@Email,@Name
End;

Bryian Tan 2-Jun-17 0:44am

Yes. This should work. somehow the exec SP doesn't like string concatenation in the parameters.

Abhi1 Kanobi 2-Jun-17 4:31am

Hi Thanks for ur input ,
but alas , the link generated is still not redirecting to the page i want to .

It's getting redireted to - https://aydacfu.xyz/?affiliate_id=12&origin=newsletter.qendidate.

Richard Deeming 2-Jun-17 6:50am

Well that's a completely different issue. Either the link is being changed by your custom sp_send_mail procedure; or it's being changed by your mail server; or there's something wrong with your website.

Abhi1 Kanobi 2-Jun-17 4:31am

Hi Thanks for ur input ,
but alas , the link generated is still not redirecting to the page i want to .

It's getting redireted to - https://aydacfu.xyz/?affiliate_id=12&origin=newsletter.qendidate.

2 solutions

Solution 2

Though this solution may work, architecturally it is not a suitable solution, as we are invoking too much business functionalities using just a single SP. To my opinion, there should be separation of concerns in the business layer, and SRP (single responsibility principle) should be followed. If this SP is written for the sake of achieving performance, then performance factor should be improved by some other means, but not at the cost of code maintainability.

Posted 9-Jun-17 19:56pm

cjmakwana

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhi1 Kanobi · Accepted Answer · 2017-06-02T20:26:00

I updated the URL , and now the id 's are braodcasting .

Thanks everyone for the input's

DECLARE @body varchar(max) = 
'<a href=''http://someurl.com/?id=' + CAST(@ID As varchar(20))+'''>Please click here to fill your missing details</a>';

EXEC  [dbo].[sp_send_mail] 
'a@a.com',
@Email,
' Submission',
 @body,
'htmlbody',