代码如下
USE test3IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20),
@print varchar(600)
AS
SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE @col like '@print' and charindex('@print',@col) > 0
go
可编译,但是我运行
execute s_map_mohu @print='abc'的时候
出现提示过程 's_map_mohu' 需要参数 '@col',但未提供该参数。
我那不是写条件WHERE @col like '@print' and charindex('@print',@col) > 0 了吗?为什么还会出现这个问题呢?
USE test3IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20),
@print varchar(600)
AS
SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE @col like '@print' and charindex('@print',@col) > 0
go
可编译,但是我运行
execute s_map_mohu @print='abc'的时候
出现提示过程 's_map_mohu' 需要参数 '@col',但未提供该参数。
我那不是写条件WHERE @col like '@print' and charindex('@print',@col) > 0 了吗?为什么还会出现这个问题呢?
@print varchar(600)--这个有
execute s_map_mohu '参数','abc'
除了参数外 这个语句好像有问题
set @col like '%@print%'?
--@col要传递exec('SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE '+@col+' like '''+@print+''' and charindex('''+@print+''',@col) > 0')
--@col要传递exec('SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE charindex('''+@print+''','+@col+') > 0') 感觉这样就可以了
@col varchar(20) =null,
@print varchar(600) =null
AS
SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE @col like '@print' and charindex('@print',@col) > 0
go 给参数设置为默认数值
不设置的话你调用存储过程就需要提供这两个默认数值此外,你的存储过程逻辑有问题,相信你可以看到
你的参数条件不应该也在where 里面的。
如果想详细询问,发站内信
USE test3 IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20),
@print varchar(600)
AS
set @sql='SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE '+@col+' like '''+@print+''' and charindex('''+@print+''','+@col+') > 0 '
exec (@sql)
go
USE test3 IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20),
@print varchar(600)
declare @sql varchar(2000)
AS
set @sql='SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE '+@col+' like '''+@print+''' and charindex('''+@print+''','+@col+') > 0 '
exec (@sql)
go
我一写这个就提示有语法错误,不知道为什么
declare @sql varchar(2000)
应该加在as 的下面
USE test3 IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20),
@print varchar(600)
AS
declare @sql varchar(2000)
set @sql='SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE '+@col+' like '''+@print+''' and charindex('''+@print+''','+@col+') > 0 '
exec (@sql)
go
过程 's_map_mohu' 需要参数 '@col',但未提供该参数。
execute s_map_mohu @col='这个地方加上你的字段名',@print='abc'
那我@col varchar(20)=null
这样也可以吧
代码如下
USE test3 IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20)=null,
@print varchar(600)
AS
DECLARE @sql varchar(2000)
set @sql='SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE '+@col+' like '''+@print+''' and charindex('''+@print+''','+@col+') > 0 '
exec (@sql)
go 然后执行
execute s_map_mohu @print='a'就给我显示个
命令已成功完成。什么也没有了郁闷
因为是联合查询,我就想直接@col=@print是不是就可以了
第一个是你要查的表的列名
第二个是值execute s_map_mohu '列名','值'
USE test3IF EXISTS (SELECT name FROM sysobjects
WHERE name='s_map_mohu' and type='P')
DROP PROCEDURE s_map_mohu
go
CREATE PROCEDURE s_map_mohu
@col varchar(20)=null,
@print varchar(600)=null
AS
SELECT distinct *
FROM economies INNER JOIN
land ON economies.land_id =land.id INNER JOIN
sort ON economies.sort_id = sort.id
WHERE charindex('@print',@col) > 0 and @col like '%'+@print+'%'
go
因为我查询的东西比较多,比如我要查‘名字,地点,车站’等等
这些在where后面都要一个一个的写出来吗?
有简单的写法吗?
WHERE charindex('@print',@col) > 0 and @col like '%'+@print+'%' 直接定义多变量了名字 like @a
地点 like @b
车站 like @c是不是就可以了?跟我想做的输入的模糊查询相违背吗?