Passing a string to a stored procedure

[JohnnyLong]

Hi, I'm having real problems running a stored proc with a string passed from vb.net. In query analyzer I can successfully run the following dynamic sql query:

DECLARE @pSearch1 as varchar(500), @pSearch2 as varchar(500), @pSearch3 as varchar(500)

SET @pSearch1 = 'SELECT SurName,FirstName FROM Candidates WHERE '

SET @pSearch2 = 'CONTAINS(AddressTown,''"enf*"'')'

SET @pSearch3 = ' ORDER BY Surname, Firstname'

SET @pSearch1 = @pSearch1 + @pSearch2 + @pSearch3

EXEC (@pSearch1)


However, when I pass 'CONTAINS(AddressTown,''"enf*"'')' to @pSearch2 from vb.net code, I get the error "Incorrect syntax near 'CONTAINS(AddressTown,''"enf*"'')'.

The string is passed in the format:
strBuilder = "'" + "CONTAINS(AddressTown, ''""enf*""'')" + "'"

I have tried various combinations of single and double quotes but it won't have it.

Am I missing something?

 

[r937]

why the embedded double quotes? i've never seen an address with doublequotes in it

perhaps try LIKE instead of CONTAINS

r937.com | rudy.ca

 

[JohnnyLong]

It's part of the CONTAINS syntax. If you want the * wildcard to work you need to use ' ' " searchword*" ' '. CONTAINS is used for FULL TEXT Searches which are significantly quicker than LIKE.

 

[SimonSellick]

Hi,

Perhaps try creating a temp table

create table t(sql varchar(2000))

then in your stored proc, just insert the completed string

insert into t values(@pSearch1)

and commit it. Then you can check that all the nested quotes have unravelled themselves properly - and if it looks ok, paste the row into QA and see if it runs.

HTH
Simon.

 

[JohnnyLong]

Thanks for that Simon. This is what I've discovered; the following all return the correct results in QA:

SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, "enf") /*simple select*/

SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, '"enf*"') /*simple select with wildcard*/

EXEC('SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, "enf")') /*simple exec select*/

EXEC('SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, ''"enfie*"'')') /*simple exec select with wildcard*/

notice the different quotation mark syntax.
My problem seems to be passing the EXEC statements from VB code to @pSearch2 in SQL.

SET @pSearch1 = @pSearch1 + "SELECT SurName FROM rc_Candidates WHERE "

SET @pSearch1 = @pSearch1 + @pSearch2

EXEC (@pSearch1)


For example, if I pass the string as:
'SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, "enfield")'

I get an incorrect syntax error.

Any ideas anyone?

 

[JohnnyLong]

Okay, this just gets more weird!

If I pass "SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, "enfield")"

without any single quotes then it works. However, we need to use the wildcard which only appears to work in QA in this format:

SELECT SurName FROM Candidates WHERE CONTAINS(AddressTown, ''"enf*"'')

This maybe a vb.net question but does anyone know how to pass two single quotes to SQL from vb. I've tried
strBuilder = "CONTAINS(AddressTown, ''""enf*""'')"

but I get an invalid syntax near 'enf*' error.

 

[chrissie1]

I'm pretty sure they will know in the vb.net forum. However they would ask why you are doing it this way. Why not just make a SP that accepts one parameter.

Christiaan Baes
Belgium

"My new site" - Me

 

[SimonSellick]

Hi,

Unless .net tries to be clever, two consecutive single quotes should arrive as such in SQL Server which will interpret them as a single one. I haven't used .net so I don't know for certain.

Actually, I haven't used full-text indexing and CONTAINS either, so I'm not really going to be much use on this one - but I've started now, so here are a few points:

I've previously managed to avoid having to use EXEC (and so avoided this issue) by specifying

LIKE '*' + @pSearch + '*'

as the search string, but that only helps if (a) you don't mind where in the column the string appears and (b) you don't need to vary the column you are searching in.

The second parameter to CONTAINS looks like a specification rather than a simple search string, so should it perhaps contain double quotes around the parts of the spec - e.g.

CONTAINS(column, ' "A" OR "B" ')

?

Where did 'rc_Candidates' come from? All the others just say 'Candidates'.

Simon.

 

[JohnnyLong]

Thanks guys, I've sussed it.

SELECT SurName FROM Candidates WHERE CONTAINS(addresstown,'"enf*"') /*put this into QA and all ok.

@pSelect = SELECT SurName FROM Candidates WHERE CONTAINS(addresstown,'"enf*"') /*put this into QA and errors. SQL needs single commas around the SELECT and consequently double single quotes around the text like this:

@pSelect = 'SELECT SurName FROM Candidates WHERE CONTAINS(addresstown,''"enf*"'')'

But if you pass this select statement from vb.net to the stored procedure in this format it fails. You need to pass it without the extraneous single quotes:
SELECT SurName FROM Candidates WHERE CONTAINS(addresstown,'"enf*"')

Pretty simple really, just got a bit sidetracked by the QA and the dozens of different single double quote combos!!

JP