如何编写存储过程以传递许多参数 [英] how to write stored procedure to pass many parameters

问题描述

我正在写
这样的存储过程

i am writing the stored procedure like

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创建成功.但是当iam执行proc iam时，错误为

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=[e-mail] and currentlocation=Kolkata and Gender=Female'.

请帮助我

please help me

推荐答案

在代码末尾的大括号内放入您的sql变量.

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

exec (@selquery)

遵循类似方法以了解有关EXEC，EXEC()和EXECUTE的更多信息.

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


如果解决了问题，则将其标记为答案，这会激发动机:)

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 :)

除了其他答案，您还需要对某些变量进行字符串限定

您的SQL当前正在被构建为...

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

Your SQL is currently being built as...

select Emailid, currentlocation,Gender from userdetails where Emailid=[e-mail] and currentlocation=Kolkata and Gender=Female

它需要阅读更多类似

It would need to read more like

select Emailid, currentlocation,Gender from userdetails where Emailid='[e-mail]' and currentlocation='Kolkata' and Gender='Female'

很明显，您不知道自己在做什么...
您的查询未充分利用查询缓存(这会使其变慢)，它易于SQL注入并且无法读取.
实际上，您在那里所做的一切根本没有任何意义.
建议您阅读初学者存储过程 [您的操作方式错误 [

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[^]...

这篇关于如何编写存储过程以传递许多参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！