stored procedure using like operator [error]

Question

0.00/5 (No votes)

See more:

Hi everyone!

I am creating a a stored procedure using like operator and i have an error in my command which is the table name is tblsummaryIMPORT.. in doing this command that will able to change the status that triggers from the promocode having its value with loan and with loan so i've use like operator so here's my code for without loan promocode:

SQL

CREATE PROCEDURE [dbo].[promocodestatus]

select * from tblsummaryIMPORT where promocode like 'without loan' 
update tblsummaryIMPORT set status = 'for payment' where set status=@status;

yet in this code i face error here so please help... thanks in advance

Posted 13-Jun-13 0:01am

Member 10033107

Updated 13-Jun-13 0:32am

_Amy

v2

Add a Solution

Comments

sanchit patne 13-Jun-13 6:07am

can you paste your complete query? It will help to solve the problem.

Mike Meinz 13-Jun-13 6:16am

It is not clear why you are doing a SELECT or why you are doing a SELECT with a LIKE clause. Do you expect that the UPDATE statement depends on data from the SELECT statement?

What error are you getting? Is it a syntax error or a run-time error? Which line gives the error?

The LIKE clause does not contains at leas one % character.

Use "Improve Question" to provide more information.

1 solution

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)

OriginalGriff · Accepted Answer · 2013-06-13T00:39:00

Solution 1

I suspect that you actually want to say something along the lines of:

SQL

UPDATE tblsummaryIMPORT SET status = 'for payment' WHERE status=@status AND promocode LIKE '%without loan%'

Having a SELECT statement before the UPDATE does not restrict the records updated to those returned by the SELECT - the two operations are independent.
In addition promocode LIKE 'for payment' is exactly the same as promocode = 'for payment' as the search text contains no wild cards. '%' is the SQL wildcard for 'any number of any character' in the same way that '*' is for windows filenames

Posted 13-Jun-13 0:39am

OriginalGriff

Comments

Member 10033107 13-Jun-13 6:47am

OriginalGriff.. youve got it im sorry if i cant post my code because my other team use the server so ill post later what happen thanks for the response guys

Member 10033107 13-Jun-13 6:51am

okay guys this is my full command in stored proc:

CREATE PROCEDURE [dbo].[promocodestat]
@promocode varchar(MAX),
@loanstatus varchar (MAX)
AS
BEGIN
SET NOCOUNT ON;
select * from tblsummaryIMPORT where promocode like 'without loan'
update tblsummaryIMPORT set status = 'for payment' where set status=@status

END

having an error message: Msg 156, Level 15, State 1, Procedure promocodestat, Line 8
Incorrect syntax near the keyword 'set'.

Member 10033107 13-Jun-13 6:54am

and when i use @originalgriff code it was successful thanks !

but i have a question how can i add this code kinda it its like using an else statement for with loan?? or should i add again the code by using 'with loan' ??

Mike Meinz 13-Jun-13 7:01am

Please ask your question with more detail so we can understand better what you are trying to do. Use IMPROVE QUESTION and include "pseudo-code" if you do not know the exact syntax to use.

Mike Meinz 13-Jun-13 6:55am

You have extra "set" in update tblsummaryIMPORT set status = 'for payment' where set status=@status

Why is the SELECT statement in the Stored Procedure? It has no effect on the UPDATE statement.

Member 10033107 13-Jun-13 7:03am

ooh i see again thanks mike meinz.. btw i have follow up question wherein i already use original griff code and this is my question: how can i add this code kinda it its like using an else statement for with loan?? or should i add again the code by using 'with loan' ??

Mike Meinz 13-Jun-13 7:05am

I do not understand what you are asking. Use IMPROVE QUESTION and include "pseudo-code" if you do not know the exact syntax to use.

Member 10033107 13-Jun-13 7:21am

this is what i meant..i already coded this :

ALTER PROCEDURE [dbo].[promocodestat]
@promocode varchar(MAX),
@loanstatus varchar (MAX)
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblsummaryIMPORT SET loanstatus = 'for payment' WHERE loanstatus=@loanstatus AND promocode LIKE '%without loan%'
UPDATE tblsummaryIMPORT SET loanstatus = 'for loan approval' WHERE loanstatus=@loanstatus AND promocode LIKE '%with loan%'

yet it cannot insert on the table the status for loan approval or else for payment

Mike Meinz 13-Jun-13 7:25am

Again, I do not understand what you are trying to do. Please provide a more detailed description.

I see that you pass in a Stored Procedure parameter named @promocode but never use it. Why?

Member 10033107 13-Jun-13 7:25am

ooh i already get whats my error..btw thanks guys :)

OriginalGriff 13-Jun-13 8:36am

You're welcome!