在表中存储查询的问题 [英] Problem with Storing query in table

问题描述

这是我的程序:

this is my procedure:

ALTER procedure [dbo].[recursive]
(
   @id int =null,
  @studentname varchar(20) =null  
)
As
BEGIN
with ProductSearch 
As
(           
  select * from student        
)
                             
select * from ProductSearch 

if(@studentname is not null and @id is null)
Begin
  select * from productsearch where studentname =@studentname
end
else if(@studentname is  null and @id is not null)
Begin
  select * from productsearch where id =@id
end
end

执行后，出现以下错误
(受影响的3行)
消息208，级别16，状态1，过程递归，第39行
无效的对象名称"productsearch".

(受影响的1行)

After executing it I get following error
(3 row(s) affected)
Msg 208, Level 16, State 1, Procedure recursive, Line 39
Invalid object name ''productsearch''.

(1 row(s) affected)

推荐答案

在这种情况下，Sql区分大小写，将productsearch 替换为ProductSearch .

希望这会有所帮助，
Fredrik Bornander

Sql is case sensitive in this case, replace productsearch with ProductSearch .

Hope this helps,
Fredrik Bornander

尝试一下，

Try this,

ALTER procedure [dbo].[recursive]
(
   @id int =null,
  @studentname varchar(20) =null  
)
As
BEGIN
if(@studentname is not null and @id is null)
Begin
  select * from productsearch where studentname =@studentname
end
else if(@studentname is  null and @id is not null)
Begin
  select * from productsearch where id =@id
end
end

Remove 您存储过程中的以下内容.

Remove the below from your stored procedure.

with ProductSearch
As
(
  select * from student
)
select * from ProductSearch

这篇关于在表中存储查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！