就像动态函数一样
下面的代码运行良好。然而，我在尝试将其变成类似的语句时遇到问题，我需要一些帮助
CREATE PROCEDURE [dbo].[searcher]
@deliverer nvarchar (100)
AS
BEGIN
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT location, deliverer, charger FROM Store where 1=1'
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer =@pt'
DECLARE @t1 as TABLE
(
location varchar(1000),
deliverer varchar(100),
charger varchar(100)
)
INSERT INTO t1
EXEC sp_executesql @sql,
N'@pt nvarchar(100)',
@pt=location
SELECT * FROM t1
END
到目前为止，我已经尝试了下面的代码，但
DECLARE @pt nvarchar (100)
SET @pt = '%' + @pt + '%'
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer like @pt'
我也尝试过但没有取得多大成功；
DECLARE @pt nvarchar (100)
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer like ''% + @pt + %'''
The code below works well. I however have issues trying to turn it into a like statement that I need some assistance with
CREATE PROCEDURE [dbo].[searcher]
@deliverer nvarchar (100)
AS
BEGIN
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT location, deliverer, charger FROM Store where 1=1'
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer =@pt'
DECLARE @t1 as TABLE
(
location varchar(1000),
deliverer varchar(100),
charger varchar(100)
)
INSERT INTO t1
EXEC sp_executesql @sql,
N'@pt nvarchar(100)',
@pt=location
SELECT * FROM t1
END
So far, I have tried the code below but with not much success
DECLARE @pt nvarchar (100)
SET @pt = '%' + @pt + '%'
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer like @pt'
I have also tried;
DECLARE @pt nvarchar (100)
IF (@deliverer IS NOT NULL)
SET @sql = @sql + ' and deliverer like ''% + @pt + %'''
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您的存储过程参数是@deliverer并且您的动态SQL参数是@pt，我相信您的sp_executesql执行应该将该参数分配为
@pt = @deliverer。至于添加通配符，您可以在调用之前添加它们
，也可以在动态 SQL 中添加它们，
注意 % 周围的双引号。变量@pt没有被引用
If your stored procedure parameter is @deliverer and your dynamic SQL parameter is @pt, I believe your sp_executesql execution should assign the parameter as
@pt = @deliverer.As for adding wildcards, you can either add them before the call with
or add them in the dynamic SQL with
Note the doubled up quotes around the %. The variable @pt is not quoted