我以前用的是:
SELECT Count(id) FROM news where CONTAINS(SearthText, '" + SearthSql + "')现在想改成存储过程,不知道怎么写,按下面3种写好象全部不正确(得到的记录数全是0),大家帮我看看SELECT Count(id) FROM news where CONTAINS(SearthText, '"@SearthText"')SELECT Count(id) FROM news where CONTAINS(SearthText, '@SearthText')SELECT Count(id) FROM news where CONTAINS(SearthText, @SearthText)
SELECT Count(id) FROM news where CONTAINS(SearthText, '" + SearthSql + "')现在想改成存储过程,不知道怎么写,按下面3种写好象全部不正确(得到的记录数全是0),大家帮我看看SELECT Count(id) FROM news where CONTAINS(SearthText, '"@SearthText"')SELECT Count(id) FROM news where CONTAINS(SearthText, '@SearthText')SELECT Count(id) FROM news where CONTAINS(SearthText, @SearthText)
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SellSearth_Count]
@NowSearthText varchar
AS
SELECT Count(id) FROM news where CONTAINS(SearthText, '"@NowSearthText"')
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
exec("select * from table")
declare @s varchar(200)
set @= 'SELECT Count(id) FROM news where CONTAINS(SearthText, '"'+@NowSearthText+'"')
exec(s )
SELECT Count(id) FROM news where CONTAINS(SearthText, @SearthText)
set @s = 'SELECT Count(id) FROM news where CONTAINS(SearthText, '"'+@NowSearthText+'"')
exec(@s)
set @s = 'SELECT Count(id) FROM news where CONTAINS(SearthText, "'+@NowSearthText+'"')
exec(@s)
''+@NowSearthText+'' 附近有语法错误。呵呵
DECLARE @s varchar(10)
SET @s=char(34)+'事务'+char(34)--或者'事务'
SELECT Tname,name,value,gg FROM aaaa WHERE CONTAINS(gg,@s)
--result
/*Tname name value gg
-------------------- -------------------- -------------------- --------------------------------------------------------------
aa_1 地点1的名称 南头 一.A.不用事务,关用SELECT 语句.是否可以分布式查询? ...
*/
你这个是可以得到记录总数,变量放进去好象不行顺便咨询一个小问题 varchar 后不定义字符长短有关系吗?
DECLARE @g int
SET @s=char(34)+'事务'+char(34)--或者'事务'
SELECT @g=count(*) FROM aaaa WHERE CONTAINS(gg,@s)
SELECT @g
DECLARE @y varchar(1000)
SET @s=char(34)+'事务'+char(34)--或者'事务'
SET @y='SELECT * FROM aaaa WHERE CONTAINS(gg,'''+@s+''')'
exec(@y)
DECLARE @NowSearthText varchar(10)
DECLARE @y varchar(1000)
SET @NowSearthText=char(34)+'事务'+char(34)--或者'事务'
SET @y='SELECT * FROM aaaa WHERE CONTAINS(gg,'''+@NowSearthText+''')'
exec(@y)
ASDECLARE @y varchar(1000)
SET @y='SELECT * FROM aaaa WHERE CONTAINS(gg,'''+@NowSearthText+''')'
exec(@y)go
getCoo '事务'--呵呵,看还有没有要写的
AS
SELECT Tname,name,value,gg FROM aaaa WHERE CONTAINS(gg,@NowSearthText) go
getCoo '事务'--result
/*Tname name value gg
-------------------- -------------------- -------------------- ------------------------------
aa_1 地点1的名称 南头 一.A.不用事务,关用SELECT 语句.是否可以分布式(所影响的行数为 1 行)
*/
SELECT Count(id) FROM news WHERE CONTAINS(SearthText,@NowSearthText) 但刚才怎么不行
揭贴了