如何在sql server 2008中使用两个参数在商店过程中使用top子句。 [英] how to use top clause in store procedure with two parameters in sql server 2008.

问题描述

大家好，



请帮帮我



如何在商店程序中使用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屋！