如何在sql server 2008中使用两个参数在商店过程中使用top子句。 [英] how to use top clause in store procedure with two parameters in sql server 2008.
本文介绍了如何在sql server 2008中使用两个参数在商店过程中使用top子句。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
请帮帮我
如何在商店程序中使用top子句sql server 2008中的两个参数。
我正在使用的代码。
Hi All,
Please help me
how to use top clause in store procedure with two parameters in sql server 2008.
code that i am using .
create proc wiz_msg1 @usrnm varchar(500),@sndnm varchar(500),@ctr int
as begin
exec ('
SELECT top '+@ctr+'
Serial_No,
Sent_From,
Message,
UserName,
Date
FROM wizom_Receive_Knocks
WHERE Sent_From in('+@usrnm+','+@sndnm+')and UserName in('+@usrnm+','+@sndnm+') order by date desc')
end
此代码正确破坏(sp创建成功)
但是当我尝试执行此SP然后获取错误。
this code ruining properly(sp created successfully)
but when i am trying to execute this SP then getting an error .
exec wiz_msg1 'balbinder123','sachin',10
Error is: <br />
Msg 207, Level 16, State 1, Line 8<br />
Invalid column name ''sachin''.<br />
Msg 207, Level 16, State 1, Line 8<br />
Invalid column name ''balbinder123''.<br />
Msg 207, Level 16, State 1, Line 8<br />
Invalid column name ''sachin''.<br />
Msg 207, Level 16, State 1, Line 8<br />
Invalid column name ''balbinder123''.
推荐答案
您在比较字符串值时错过了单引号('')
请尝试以下代码
Hi,
You missed the Single Quote ('') while compare String values
Try the following code
CREATE PROC wiz_msg1 @usrnm VARCHAR(500),@sndnm VARCHAR(500),@ctr INT
AS BEGIN
EXEC('SELECT top '+CAST(@ctr AS VARCHAR(10))+'
Serial_No,
Sent_From,
Message,
UserName,
Date
FROM wizom_Receive_Knocks
WHERE Sent_From in('+CHAR(39)+@usrnm+CHAR(39)+','+CHAR(39)+@sndnm+CHAR(39)+')and UserName in('+CHAR(39)+@usrnm+CHAR(39)+','+CHAR(39)+@sndnm+CHAR(39)+') order by date desc')
END
-- Execute Statement
exec wiz_msg1 'balbinder123','sachin',10
问候,
GVPrabu
Regards,
GVPrabu
你收到了错误。 b''cos你错过了''+ @ usrnm +''附近的单引号,''+ @ sndnm''
你必须加三个单引号才能显示动态sql中的单引号。
我已修改你的qury如下:
You got error. b''cos you have missed single quotation near ''+@usrnm+'',''+@sndnm''
you have to add three single quot to display single quot in dynamic sql.
I have modified your qury like below:
declare @ctr int
set @ctr = 1
declare @usrnm varchar(500),@sndnm varchar(500)
set @usrnm = 'ww'
set @sndnm = 'qq'
declare @sql nvarchar(max)
set @sql ='SELECT top '+ cast(@ctr as varchar(max)) +' Serial_No, Sent_From, Message, UserName, [Date] FROM wizom_Receive_Knocks WHERE Sent_From in('''+@usrnm+''','''+@sndnm+''')and UserName in('''+@usrnm+''','''+@sndnm+''') order by date desc'
select @sql
exec (@sql)
这篇关于如何在sql server 2008中使用两个参数在商店过程中使用top子句。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文