Click here to Skip to main content
14,732,945 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
CREATE PROCEDURE sp_MasterCountry (IN whereData longtext)
begin
declare sqlQuery longtext;
set sqlQuery="Select * from CountryMaster_tb" + whereData ;

call sqlQuery;

end;

I Need to Pass full where clause from my page because some time ill pass by Country_Name and sometime ill pass just short name of Country_Name using like

What I have tried:

CREATE PROCEDURE sp_MasterCountry (IN whereData longtext)
begin
declare sqlQuery longtext;
set sqlQuery="Select * from CountryMaster_tb" + whereData ;
 
call sqlQuery;
 
end;


giving me error

in MYSQL Server
I am using MYSQL Workbench for GUI
Posted
Updated 6-Apr-20 2:46am

1 solution

Spaces are important.
Try changing this:
set sqlQuery="Select * from CountryMaster_tb" + whereData ;
To this:
set sqlQuery="Select * from CountryMaster_tb " + whereData ;


But ... be aware that that is very dangerous.
You shouldnNever concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

In this case, you probably want to make the SP more intelligent and pass optional parameters to it that let it select which WHERE clause to use instead of passing a string containing "WHERE ..." at all.
   
v2
Comments
hareshdgr8 6-Apr-20 7:48am
   
sqlQuery does not exist getting this issue sir
OriginalGriff 6-Apr-20 7:56am
   
Try Exec instead of Call - but take note of what I said regarding SQL Injection...
hareshdgr8 6-Apr-20 8:08am
   
Exec is in SQL not in MYSQL sir
I read sir about SQL Injection
If I need to pass two Parameter but if i dont want to use first parameter then how i can do it like

Country_No int,Country_Name some time i dont want to use Country_No then how should i write mysql store Procedure
OriginalGriff 6-Apr-20 8:23am
   
Simple - allow the parameters to contain NULL.
Then your SP checks is one or both are empty and chooses the appropriate SELECT statement.
Maciej Los 6-Apr-20 9:14am
   
Another 5 for this comment!
Maciej Los 6-Apr-20 9:14am
   
5ed!

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