对存储过程的怀疑 [英] doubt on stored procedure

问题描述

db中的表如下，

创建表[dbo].[STS_ADDRESS_BOOK](
[AB_ID] int NOT NULL IDENTITY(1,1)CONSTRAINT主键PRIMARY KEY，
[AB_NAME] [varchar](100)，
[AB_EMAIL] [varchar](100)，
[AB_FAX] [varchar](100)，
[AB_ADDRESS] [varchar](max)，
[AB_PHONE] [varchar](16)
)
-------------------------------------------------- ---
然后sp，
-------------------------------------------------- ------
GO
IF OBJECT_ID(''dbo.selectAddressBookInformation1'')不为空
删除PROC selectAddressBookInformation1
GO

创建PROC selectAddressBookInformation1 @columnvalue varchar(100)，
@searchword varchar(100)
AS

exec(``从STS_ADDRESS_BOOK中选择*，其中''+ @列值+''喜欢％''+ @searchword +``％'')

该命令执行成功.但是当使用sp执行时，
-------------------------------------------------- -------------------------
exec selectAddressBookInformation1 @ columnvalue = [AB_NAME]，@ searchword =''b''

得到错误的
--------------------------------------
关键字"LIKE"附近的语法不正确.为什么?什么是错误?

table in db as below,

CREATE TABLE [dbo].[STS_ADDRESS_BOOK](
[AB_ID] int NOT NULL IDENTITY(1,1) CONSTRAINT primarykey PRIMARY KEY,
[AB_NAME] [varchar](100),
[AB_EMAIL] [varchar](100),
[AB_FAX] [varchar](100),
[AB_ADDRESS][varchar](max),
[AB_PHONE][varchar](16)
)
-----------------------------------------------------
then sp,
--------------------------------------------------------
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'' +@columnvalue+'' LIKE %'' + @searchword + ''%'')

this command executed successfully. but when execute sp using,
---------------------------------------------------------------------------
exec selectAddressBookInformation1 @columnvalue=[AB_NAME],@searchword=''b''

get error as
--------------------------------------
Incorrect syntax near the keyword ''LIKE''. why? what is the mistake?

推荐答案

hiii
替换此行

exec(``从STS_ADDRESS_BOOK中选择*，其中''+ @列值+''喜欢％''+ @searchword +``％'')

与

exec(``从STS_ADDRESS_BOOK中选择*，其中''+ @ columnvalue +''LIKE''''％''+ @searchword +``％'''''')


您将获得正确的答案

hiii
replace this line

exec (''select* from STS_ADDRESS_BOOK where'' +@columnvalue+'' LIKE %'' + @searchword + ''%'')

with

exec (''select * from STS_ADDRESS_BOOK where '' +@columnvalue+'' LIKE ''''%'' + @searchword + ''%'''''')


you will get correct ans

这篇关于对存储过程的怀疑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！