how to write stored procedure to pass many parameters

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi,

i am writing the stored procedure like

SQL

ALTER proc [dbo].[testmultiplevalues](@emailid varchar(max)=null, @currlocation varchar(max)=null,@gender varchar(max)=null)
as
begin
declare @selquery varchar(max)
declare @concat varchar(max)
set @concat = ' '
set @selquery='select Emailid, currentlocation,Gender from userdetails where '
if(@emailid is not null)
begin
set @selquery=@selquery + 'Emailid=' +@emailid
set @concat=' and '
end
if(@currlocation is not null)
begin
set @selquery=@selquery + @concat + 'currentlocation=' +@currlocation
set @concat=' and '
end
if(@gender is not null)
begin
set @selquery =@selquery + @concat +'Gender=' + @gender
end
exec @selquery
end

proc created successfully.but when iam executing the proc iam getting the error as

Msg 2812, Level 16, State 62, Procedure testmultiplevalues, Line 22
Could not find stored procedure 'select Emailid, currentlocation,Gender from userdetails where Emailid=nsaritha34@gmail.com and currentlocation=Kolkata and Gender=Female'.

please help me

Posted 17-Nov-11 21:47pm

Member 7932936

Updated 17-Nov-11 21:49pm

Mehdi Gholam

v2

Add a Solution

Comments

Mehdi Gholam 18-Nov-11 3:49am

EDIT -> fixed formatting

Mehdi Gholam 18-Nov-11 3:51am

This is a bad use of stored procedures, you are better off just creating the query and executing it as normal.

5 solutions

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)

Pandya Anil · Answer 1 · 2011-11-17T21:58:00

put your sql variable at the end of the code, inside the braces like.

SQL

exec (@selquery)

follow the like to learn more about the EXEC,EXEC() and EXECUTE.

http://blog.sqlauthority.com/2007/09/13/sql-server-difference-between-exec-and-execute-vs-exec-use-execexecute-for-sp-always/[^]

mark as answer if solves your problem, it motivates :)

Dylan Morley · Answer 2 · 2011-11-17T22:01:00

In addition to the other answers, you also need to string qualify some of your variables

Your SQL is currently being built as...

SQL

select Emailid, currentlocation,Gender from userdetails where Emailid=nsaritha34@gmail.com and currentlocation=Kolkata and Gender=Female

It would need to read more like

SQL

select Emailid, currentlocation,Gender from userdetails where Emailid='nsaritha34@gmail.com' and currentlocation='Kolkata' and Gender='Female'

Sander Rossel · Answer 3 · 2011-11-17T22:01:00

Obviously you have no clue as to what you are doing...
Your query does not make optimal use of query caching (which makes it slower), it's prone to SQL Injection and it's impossible to read.
Actually, what you are doing there makes no sense at all.
I suggest you read up on Stored Procedures for beginners[^].
After that you might see the error of your ways[^]...

sachin10d · Answer 4 · 2011-11-17T23:05:00

Try this

SQL

alter proc [dbo].[testmultiplevalues](@emailid varchar(max)=null, @currlocation varchar(max)=null,@gender varchar(max)=null)
as
begin
    declare @selquery varchar(max)
    declare @concat varchar(max)
    set @concat = ' '
    set @selquery='select Emailid, currentlocation,Gender from userdetails where '
    if(@emailid is not null)
    begin
        set @selquery=@selquery + 'Emailid=' +char(39)+@emailid+ char(39)
        set @concat=' and '
    end
    if(@currlocation is not null)
    begin
        set @selquery=@selquery + @concat + 'currentlocation=' +char(39)+@currlocation+char(39)
        set @concat=' and '
    end
    if(@gender is not null)
    begin
        set @selquery =@selquery + @concat +'Gender=' + char(39)+@gender+char(39)
    end
    exec sp_executesql @selquery
end

Member 7932936 · Answer 5 · 2012-01-04T00:38:00

Solution 5

i missed brackets at exec (variable)

Posted 4-Jan-12 0:38am

Member 7932936

how to write stored procedure to pass many parameters

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0