Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 13-Jun-13 0:32am
v2
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

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
   
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!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900