I Am Getting Error In Creating Sp In Mysql Please Help!! Note: Getting Error Only When I Am Adding Where Clause Or Else Its Working Fine

Question

1.00/5 (1 vote)

See more:

SQL

create procedure StudentTimeLineByUserCode( _userCode varchar(20), _startDate datetime, _endDate datetime)
 
Begin

SET @strTblQuery = concat("select * from ", _userCode, "_", "Timeline" where dateCreated>=_startDate AND dateCreated<=_endDate);

    SELECT @strTblQuery;

        PREPARE stmt1 FROM @strTblQuery;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

 end

Posted 29-Jan-14 22:58pm

Mohammed_Faisal_Majeed

Updated 29-Jan-14 23:01pm

v2

Add a Solution

Comments

thatraja 30-Jan-14 5:01am

what's the error? always include those details in your question

[no name] 30-Jan-14 5:03am

Execution Error

thatraja 30-Jan-14 5:04am

We need complete error message. Always include complete details in your question

[no name] 30-Jan-14 5:08am

Error is near WHERE clause

[no name] 30-Jan-14 5:05am

execution Error near where clause

thatraja 30-Jan-14 5:06am

You got answer below, check it out

[no name] 30-Jan-14 5:09am

where is the answer .. i am not getting can u please guide

thatraja 30-Jan-14 5:11am

Click here

[no name] 30-Jan-14 5:15am

after using u r solution, sp got created but ERROR in calling sp

call StudentTimeLineByUserCode ('UA14', '2013-07-12 02:50:20', '2014-01-25 08:44:03')

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)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-01-29T23:02:00

Solution 1

This line has wrong quote-pairs...

SET @strTblQuery = concat("select * from ", _userCode, "_", "Timeline" where dateCreated>=_startDate AND dateCreated<=_endDate);

Try this...

SET @strTblQuery = concat("select * from ", _userCode, "_Timeline where dateCreated>='",_startDate, "' AND dateCreated<='",_endDate,"'");

Posted 29-Jan-14 23:02pm

Kornfeld Eliyahu Peter

Updated 29-Jan-14 23:37pm

v2

Comments

[no name] 30-Jan-14 5:16am

after using u r solution, sp got created but ERROR in calling sp

call StudentTimeLineByUserCode ('UA14', '2013-07-12 02:50:20', '2014-01-25 08:44:03')

thatraja 30-Jan-14 5:21am

Again you're doing the same mistake. You should include the error message too in your reply. Without those details we can't help you.

[no name] 30-Jan-14 5:29am

When i am calling the sp

call StudentTimeLineByUserCode ('UA14', '2013-07-12 02:50:20', '2014-01-25 08:44:03')

ERROR IS:

SQL Execution Error #1064. Response from the database
You have an error in your SQL syntax; check the manual that
corresponds to your MYSQL server version for the right syntax to use
near '02:50:20 AND dateCreated <=2014-01-25 08:44:03' at line 1

Kornfeld Eliyahu Peter 30-Jan-14 5:37am

You have to enclose your date values with quotes...
See update

[no name] 30-Jan-14 5:40am

I have enclosed with quotes

[no name] 30-Jan-14 5:47am

Thank you so much i got it :)

thatraja 30-Jan-14 5:38am

Two issue I think.
0) You have to put single quotes around date values
OR
1)You have to format the date values.
Google for "MySql date format"

[no name] 30-Jan-14 5:41am

ok :(

thatraja 30-Jan-14 5:52am

It's not a tough task, use Google, you could solve the issues yourself