doubt on stored procedure

Question

1.00/5 (1 vote)

See more:

i create a table

SQL

CREATE TABLE [dbo].[STS_ADDRESS_BOOK](
         [AB_NAME] [varchar](100),
         [AB_EMAIL] [varchar](100),
         [AB_FAX] [varchar](100),
         [AB_ID] int NOT NULL IDENTITY(1,1) CONSTRAINT primarykey PRIMARY KEY)

then wrote a stored procedure,
--------------------------------------------------------------------------------

SQL

GO
IF OBJECT_ID ('dbo.selectAddressBookInformation')IS NOT NULL
DROP PROC  selectAddressBookInformation
GO

CREATE PROC selectAddressBookInformation @columnvalue varchar(100),
@searchword varchar(100)
AS
exec('SELECT * FROM STS_ADDRESS_BOOK WHERE' +@columnvalue+ '=' +@searchword)

-------------------------------------------------------------------------------
But the execution of this stored procedure using,

SQL

exec selectAddressBookInformation [AB_NAME],'hari'

give error, Incorrect syntax near '='

why i get this error,what i done wrong?

Posted 24-Sep-12 0:25am

hasbina

Updated 24-Sep-12 0:32am

_Amy

v2

Add a Solution

3 solutions

Solution 3

You'd better use the stored procedure sp_executesql[^] to construct and parameterise your SQL statement:

SQL

CREATE PROC selectAddressBookInformation
    @columnvalue varchar(100),
    @searchword varchar(100)
AS
    DECLARE @SQLString nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);
    SET @SQLString = N'SELECT * FROM STS_ADDRESS_BOOK WHERE @columnname=@searchterm';
    SET @ParmDefinition = N'@columnname varchar(100), @searchterm varchar(100)';
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @columnname = @columnvalue, @searchterm = @searchword;

You can see that the stored procedure sp_executesql needs an SQL statement which is parameterised and the declaration of the parameters (names and types). Then following that comes the definition of said parameters. This is better than trying to construct the SQL by string concatenation for the obvious reasons.

Regards,

— Manfred

Posted 24-Sep-12 0:45am

Manfred Rudolf Bihy

Updated 24-Sep-12 1:31am

v5

Comments

hasbina 24-Sep-12 7:08am

what is N?

Manfred Rudolf Bihy 24-Sep-12 7:10am

The SQLString being constructed is nvarchar so for the SQL interpreter to know that the string literal is supposed to be nvarchar one has to prefix it with a catpital N.

hasbina 24-Sep-12 7:13am

hi Manfred,

CREATE PROC selectAddressBookInformation
@columnvalue varchar(100),
@searchword varchar(100)
as
DECLARE @SQLString nvarchar(500),@ParmDefinition nvarchar(500)
SET @SQLString = 'SELECT * FROM STS_ADDRESS_BOOK WHERE @columnvalue=@searchwor'
SET @ParmDefinition = '@columnname varchar(100), @searchterm varchar(100)';

the execution of the stored procedure using

EXECUTE sp_executesql @SQLString, @ParmDefinition, @columnname =[AB_NAME], @searchterm = 'ban';

give error Must declare the scalar variable "@SQLString".

why?

Manfred Rudolf Bihy 24-Sep-12 7:37am

I fixed a small error in my SP, I had missed the definition of the variable @ParmDefinition. Please try the code exactly how I have given it (except the definition of the parameter values maybe). You must use the N in front of the string literal being assigned to SQLString.

hasbina 24-Sep-12 7:42am

hi Manfred,

CREATE PROC selectAddressBookInformation
@columnvalue varchar(100),
@searchword varchar(100)
AS
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SET @SQLString = N'SELECT * FROM STS_ADDRESS_BOOK WHERE @columnname=@searchterm';
SET @ParmDefinition = N'@columnname varchar(100), @searchterm varchar(100)';

but the execution of storedprocedure using

EXECUTE sp_executesql @SQLString, @ParmDefinition, @columnname = [AB_NAME], @searchterm ='ban';

give error as

Must declare the scalar variable "@SQLString".

why?what is the mistake?

Manfred Rudolf Bihy 25-Sep-12 9:21am

The EXECUTE sp_executesql ... belongs to the stored procedure selectAddressBookInformation and the variable is declared there. All you have to do is call your stored procedure (the one I defined in my solution) like this:
EXEC selectAddressBookInformation '[AB_Name]', 'ban'

and that is it. I don't know what you are doing wrong, but that's the way it should work.

Solution 1

I checked your SP. There you made a common mistake. Modify your stored procedure and put a space between where and single quote.

SQL

GO
IF OBJECT_ID ('dbo.selectAddressBookInformation')IS NOT NULL
DROP PROC  selectAddressBookInformation
GO

CREATE PROC selectAddressBookInformation @columnvalue varchar(100),
@searchword varchar(100)
AS
--Here you need to put a spce between where and single quote(')
exec('SELECT * FROM STS_ADDRESS_BOOK WHERE ' +@columnvalue+ '=''' +@searchword+'''')

This will work now.
--Amit

Posted 24-Sep-12 0:41am

_Amy

Updated 24-Sep-12 1:25am

v8

Comments

_Amy 24-Sep-12 6:50am

Instead of equals(=) sign, he need to use LIKE operator for the strings. And what is the problem if he is searching with the exact value? This is upto OP. :)

Manfred Rudolf Bihy 24-Sep-12 6:54am

For varchar the quotes around @searchword were missing. I added them to your solution.
You're welcome! ;)

Manfred Rudolf Bihy 24-Sep-12 6:48am

That was one problem, the other being that the SQL is constructed via a string plus the missing quotes around the searchword. I advised OP to use the stored procedure sp_executesql instead.

Cheers!

_Amy 24-Sep-12 7:04am

Ya I understood, but can you check with this print statement:

DECLARE @columnvalue varchar(5)='aa' 
DECLARE @searchword varchar(5)='bb' 
Print 'SELECT * FROM STS_ADDRESS_BOOK WHERE ' +@columnvalue+ '=' +@searchword

This is returning the exact query which OP needs. That's why my answer was that. The modification which you did in my answer is not producing the exact query. I checked. You, please verify that.
I roll backed with the older version. You check it and let me know.

--Amit

Manfred Rudolf Bihy 24-Sep-12 7:18am

I don'T think you know exactly what you are up to. This is what I got from your snippet:

SELECT * FROM STS_ADDRESS_BOOK WHERE AA=BB

AA is supposed to be the column name so that's ok, but BB is supposed to be the value to be searched for and it is MISSING the quotes.

This is what should be the output from your construction statement:

SELECT * FROM STS_ADDRESS_BOOK WHERE AA='BB'

See what I mean?

hasbina 24-Sep-12 6:57am

hi Amit,

Main problem is how can execute the stored procedure?

exec selectAddressBookInformation [AB_NAME],'hari' will get error as

Invalid column name 'hari'.

why?

_Amy 24-Sep-12 7:02am

Check your procedure carefully, the first parameter it is expecting as columnvalue and second is column name. So, your execute statement should be:
exec selectAddressBookInformation @columnvalue=[AB_NAME], @searchword='hari'

Manfred Rudolf Bihy 24-Sep-12 7:08am

You're poking OP into the wrong direction. Ther problem is with the missing quotes I introduced into your solution in the meantime. One does not nead to use named parameters is the paramters are given in the same order as specified in the stored procedure definition.

Manfred Rudolf Bihy 24-Sep-12 7:05am

Because you did not yet use the correction I made to Amit's code. See where I added the nescessary quotes. Without the quotes the string literal 'hari' will be interpreted as a column name. This is one of the reasos I dislike the contruction of SQL statements via concatenation: It's to error prone.

A better alternative would be to use sp_exectesql as I advised in my solution.

Cheers!

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)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2012-09-24T02:29:00

Solution 5

Change your Store procedure like below.

SQL

ALTER PROC selectAddressBookInformation
@columnvalue varchar(100),
@searchword varchar(100)
AS
DECLARE @query varchar(100)
SET @query = 'SELECT * FROM STS_ADDRESS_BOOK WHERE ' + @columnvalue + '='''+ @searchword + ''''
exec(@query)

And execute as you were executing like below.

SQL

exec selectAddressBookInformation [AB_NAME],'hari'

It will work... Tested at my end.

Thanks...

Posted 24-Sep-12 2:29am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

hasbina 25-Sep-12 1:34am

Thanks Tadit...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 25-Sep-12 1:38am

Anytime and thanks a lot for accepting the answer @hasbina...
I was depressed due to the person who downvoted (I also lost most valuable points), but now I am happy due to your response, for which I was waiting eagerly since yesterday..
Thanks again..:) :)

hasbina 25-Sep-12 2:07am

Thanks Tadit...

again i have a doubt,

i have same table as above,then sp as below,

GO
IF OBJECT_ID ('dbo.selectAddressBookInformation1')IS NOT NULL
DROP PROC selectAddressBookInformation1
GO

CREATE PROC selectAddressBookInformation1 @columnvalue varchar(100),
@searchword varchar(100)
AS

exec ('select* from STS_ADDRESS_BOOK where CONTAINS('+@columnvalue+','+@searchword+')')

this command executed successfully. but when execute sp using

exec selectAddressBookInformation1 @columnvalue=[AB_NAME],@searchword='hari'

give error as

Incorrect syntax near 'hari'.
why? plz help Tadit...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 25-Sep-12 3:35am

Actually the query you are executing is


select * from STS_ADDRESS_BOOK where CONTAINS(AB_NAME, hari)

but it is wrong as hari should be one varchar value like 'hari' and query should look like...


select * from STS_ADDRESS_BOOK where CONTAINS(AB_NAME,'hari')

So the procedure needs to be changed accordingly...


ALTER PROC selectAddressBookInformation1 
@columnvalue varchar(100),
@searchword varchar(100)
AS
exec ('select * from STS_ADDRESS_BOOK where CONTAINS('+@columnvalue+','''+@searchword+''')')

Then if you execute the procedure like below,


exec selectAddressBookInformation1 @columnvalue=[AB_NAME],@searchword='hari'

it will throw an error like below...


Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'STS_ADDRESS_BOOK' because it is not full-text indexed.

.
So, then you need to
1. install full text indexing feature (if you don't have then you need to re-install)
2. create FullTextCatalog and
3. define full-text index for the table like below
Follow this correct answer for the detailed steps.


-- Creating full text catalogue
create fulltext catalog FullTextCatalog as default
select *
from sys.fulltext_catalogs

-- For creating full-text index in table
CREATE FULLTEXT INDEX ON STS_ADDRESS_BOOK
(AB_NAME)
KEY INDEX primarykey
ON FullTextCatalog
WITH STOPLIST = SYSTEM

I may be wrong in the above queries for creating the FullTextCatalogue and Full Text Index on Table as I can't test the execution of procedure at my end because Indexing feature is not available in my SQL Server Management Studio. You just follow the steps given in that link I have given you above and try to implement...

After that, you can execute your query with no errors.

Thanks a lot...

hasbina 25-Sep-12 13:07pm

Tadit....Thanks a lot for your greatful replay...Thanks again..

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 25-Sep-12 14:41pm

Most welcome... Anytime... My pleasure...