创建存储过程动态生成SQL语句的问题

[No4000]

CREATE PROC GetInFo @tableName Varchar(30)
@filter varchar(30)

AS
declare @sql nvarchar(1000)

begin
set @sql ='select '+@filter+' from '+@tableName
print @sql
execute(@sql)
end
为什么这样写不对? 我要@tableName 后面再加一个where条件又如何做?

[fengming2222]

请问这句为什么要加+ ' where '+ ' 1=1 ' ?

----------------------------------
意思是你自己再加一个WHERE条件。
或者说用于程序中条件字符串的拼接，相当于while(true),if(true)。

[tre_sdlpq]

CREATE PROC GetInFo @tableName Varchar(30)
@filter varchar(30)

AS
declare @sql nvarchar(1000)


set @sql = 'select '+@filter+ ' from '+@tableName
set @sql = @sql+'where 条件'
print @sql
execute(@sql)
这样做绝对可以


给分阿

[hui_hui_2007]

ALTER      PROCEDURE   tmp
AS
declare @sql varchar(1000)
set   @sql= 'select * from Customers  where left(customerid,1)=''a'' order   by CustomerID'

exec(@sql) 

[glgoon]

CREATE   PROC   GetInFo   
@tableName   Varchar(30), 
@filter varchar(30),
@id varchar(2)
AS 
begin
  declare   @sql   nvarchar(1000) 

 
      set   @sql   = 'select '+@filter+' from '+@tableName+ ' where id='+@id
     print   @sql 
      execute(@sql) 
end
go
exec getinfo 'ta', 'col1','1'

drop proc getinfo

练习

[dobear_0922]

CREATE   PROC   GetInFo   @tableName   Varchar(30) 
                              @filter   varchar(30) 
                              
AS 
  declare   @sql   nvarchar(1000) 

begin 
      set   @sql   = 'select   '+@filter+ '   from   '+@tableName+' where '+' 1=1 '  
      print   @sql 
      execute(@sql) 
end

[dobear_0922]

CREATE   PROC   GetInFo   
@tableName   Varchar(30), 
@filter   varchar(30)                               
AS 
begin
  declare   @sql   nvarchar(1000) 

 
      set   @sql   = 'select   '+@filter+ '   from   '+@tableName+' where '+' 1=1 '  
      print   @sql 
      execute(@sql) 
end

[dobear_0922]

CREATE PROC GetInFo
@tableName Varchar(512),
@filter varchar(512),
@conditions varchar(512)=NULL --where后面的条件
AS
begin
declare @sql nvarchar(8000)
set @sql = 'select '+@filter+ ' from '+@tableName+isnull(' where '+@conditions,'')
print @sql
execute(@sql)
end

[-狙击手-]

create table ta(id int,col1 char(2))
go
insert into ta select 1,'a' union select 2,'b'

select * from ta
/*id          col1 
----------- ---- 
1           e 
2           b 
*/

CREATE   PROC   GetInFo   
@tableName   Varchar(30), 
@filter varchar(30),
@conditions   varchar(512)=NULL     --where后面的条件
AS 
begin
  declare   @sql   nvarchar(1000) 

 
      set   @sql   = 'select   '+@filter+ '   from   '+@tableName+ isnull( '   where   '+@conditions, ' ')
--      print   @sql 
      execute(@sql) 
end
go
exec getinfo 'ta', 'col1','id = 1'

drop proc getinfo

[-狙击手-]

exec getinfo 'ta', 'col1','id = 1'

drop proc getinfo

/*

col1
----
e

（所影响的行数为 1 行）
*/

[No4000]

set @sql = 'select '+@filter+ ' from '+@tableName+' where '+' 1=1 ' 请问这句为什么要加+' where '+' 1=1 ' ? 谢谢