SP Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

SP Problem

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-04 : 20:08:37

This works
ShowComboLocation 'SELECT * FROM dbo.iViewAll '

but this one does not work

ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ' %FRANCE%' '

it is showing
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '%'.

Pls help

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 20:20:26

Try this:

ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '

Note that this '' is two single quotes, not a double quote.

Edit: FIRST!!!

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-04 : 20:21:13

use double single quote

ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '

-----------------
[KH]
2006 a new beginning

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-04 : 20:21:52

Write it as follows
ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '

(ie. single quotes within the string should be replaced with 2 single quotes)

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-05 : 15:26:51

Guys none of the solution worked pls help,

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-05 : 16:15:19

What is the result? Same Error ? Different Error ? or not returning any value?

First try
ShowComboLocation 'SELECT * FROM dbo.iViewAll' and c whether that works.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-05 : 16:20:28

It worked but does not work with [ LIKE ''%FRANCE%'' ' ]
Pls help

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-05 : 18:02:37

Hi OBINNA_EKE,
When answering can u answer it in a bit detail? Its very difficult to do mind reading.
What do u mean by "does not work" ?
Is it giving an error?
Is it not returning any result ?
Did u copy & paste what v gave right away or did u type it urself ?

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-05 : 18:49:55

This is the SP

CREATE PROCEDURE [dbo].[ShowComboLocationx]
@Keyword varchar(2000)
AS
EXEC(@keyword)
GO

it is showing
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '%'.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-05 : 19:21:53

ShowComboLocationx 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-05 : 19:32:38

quote:
Originally posted by Srinika

Did u copy & paste what v gave right away or did u type it urself ?

please answer

in query analyzer copy & paste & run the following :

ShowComboLocationx 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '

I was successfull with the following:

use pubs

CREATE PROCEDURE [dbo].[ShowComboLocationx]
@Keyword varchar(2000)
AS
EXEC(@keyword)
GO

ShowComboLocationx 'Select * from authors where phone like ''%08%'''

I also get the same error when one-single quote set is sorrounding %FRANCE%

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-06 : 15:40:22

Thank you Srinika (you code worked) and all others above that supplied me with code
However, the SP executes via Query Analyzer but not in my C# function, pls check the error here

http://194.247.250.130/amber/searchrent.aspx?keyword=&Type=S

PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE ''%ton%''");

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-06 : 20:54:40

If u do something like that then it should be


PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%'");

ie. sorround the %ton% with single set of 2 single quotes as
'%ton%'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-06 : 21:52:02

in SQL, a string is enclosed in single quote not double quote.
for example

declare @str varchar(100)
    select @str = 'This is a string'

so if you need to include a single quote in the string you need to use the single quote twice to escape it.
example

    select @str = 'This string is with a single quote ''.'

in C#, a string is define in double quote. As shown here in the Hello World example

Console.WriteLine("Hello World !");

Which means, you don't have to escape the single quote to use it in C#.

Console.WriteLine("This string is with a single quote '");

Hope this clear things up for you.

-----------------
[KH]

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-06 : 22:18:44

And also in SQL QA, any text constant which is supposed to be inside single quotes should be in 2 quotes set

ie.
in C#
PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%' ");

in SQL QA
Exec ('SELECT * from dbo.iViewAll WHERE PropertyArea LIKE ''%ton%'' ')

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-01-07 : 06:15:18

GUYS thanks a lot, I really appreciated all the help I got,

PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%' ");

it still doesn't work, it complains about

Line 1: Incorrect syntax near 'LIKE'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'LIKE'.

I will reverting it my code back (ie fixing the url not to show error ); SQL+C# compiler won this time
Any other suggestion will be appreciated

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-07 : 10:18:49

This problem seems to be something to do with c#.
May be it considers both single quote & dbl quote as text delimeters.

Try the following:

PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE " & chr(39) & "%ton%" & chr(39) );

[ I'm not a C# guy and I assumed that the ampersand (&) does concatenation of the strings, if not use any such instead of & ]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources