public string fourns()
{
string sql = "select * from Table_1 where 1=1"; if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
{
sql += " and ID ='" + textBox1.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
{
sql += " and Name= '" + textBox2.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(comboBox1.Text.Trim()))
{
sql += " and Sex ='" + comboBox1.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
{
sql += " and Age='" + textBox4.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(comboBox2.Text.Trim()))
{
sql += " and Class='" + comboBox2.Text.Trim() + "'";
}
return sql.ToString(); }
上面这段写成存数过程怎么写?
create proc chanxun
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
{
string sql = "select * from Table_1 where 1=1"; if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
{
sql += " and ID ='" + textBox1.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
{
sql += " and Name= '" + textBox2.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(comboBox1.Text.Trim()))
{
sql += " and Sex ='" + comboBox1.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
{
sql += " and Age='" + textBox4.Text.Trim() + "'";
}
if (!string.IsNullOrEmpty(comboBox2.Text.Trim()))
{
sql += " and Class='" + comboBox2.Text.Trim() + "'";
}
return sql.ToString(); }
上面这段写成存数过程怎么写?
create proc chanxun
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
解决方案 »
- WINFORM读取数据!响应速度慢!求解决方案!
- 计时器,如何在每天指定时间运行?
- 关于XML 被另一进程访问
- ERP系统学习架构,先从什么入手? 我要做接口。
- 如何datagridview的值传到ACCESS中
- 用DataGrid控件中ItemCommand命令进行处理后,再点击分页按钮时,他响应的是ItemCommand事件而不是_PageIndexChanged事件,请问有没有解
- C#中调取数据问题
- 学习C#需要什么样的基础?我适合学C#吗?(呵,清家荡产只有这点分了)
- 请问哪儿有速度比较快的C#下载,我今天打算通宵下了!如果地址有效马上给分
- 问个问题vc6怎么样编译C#
- 关于ICE在C#里的应用
- c#如何实现只能本机打开excel文件
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
declare @sql varchar(100)
set @sql= (select * from dbo.Table_1 where 1=1)
IF(@ID != NULL)
set @sql += ' and ID =@ID'
if(@Name !=NULL)
set @sql += 'and Name =@Name'
if(@Sex != NULL)
set @sql += 'and Sex =@Sex'
if(@Age != null)
set @sql += 'and Age = @Age'
if(@Class != null)
set @sql += 'and Class = @Class'
return @sql
go没搞懂!!
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/andy1118/archive/2010/02/08/5297698.aspx
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
declare @sql varchar(2000)
set @sql = 'select * from Table_1 where 1=1 '
if @ID<>''
set @sql =@sql+' and ID =''' + @ID +'''
if @Name <>''
set @sql =@sql +' and Name =''' + @Name +'''
if @Sex <>''
set @sql =@sql+' and Sex =''' + @Sex +'''
if @Age <>''
set @sql =@sql+' and Age =''' + @Age +'''
if @Class <>''
set @sql =@sql+' and Class =''' + @Class +'''
exec (@sql)
--select @sql
發現全部少打一個點,,每行,,,, ID =''' + @ID +''''
最後都應該4個點
@ID NVARCHAR(50),
@Name NVARCHAR(50),
@Sex NVARCHAR(2),
@Age INT,
@Class NVARCHAR(100)
AS
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'select * from Table_1 WHERE 1=1'
IF @ID <> ''
set @SQL = @sql + ' AND ID=' + @ID + ''
IF @Name <> ''
set @SQL = @sql + ' AND NAME=' + @Name + ''
............... EXEC(@SQL)
要是@SQL长度大于8000了咋办?
不会吧,你什么sql会如此的长?
create proc chanxun
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
declare @sql varchar(100)
select @sql= '(select * from dbo.Table_1 where 1=1'
IF(@ID != NULL)
select @sql += ' and ID ='''+@ID+''
if(@Name !=NULL)
select @sql += 'and Name ='''+@Name+''
if(@Sex != NULL)
select @sql += 'and Sex ='''+@Sex+''
if(@Age != null)
select @sql += 'and Age ='''+@Age+''
if(@Class != null)
select @sql += 'and Class ='''+@Class+''
select @sql +=')'
exec @sql
go 可以建立存储过程
可是 用exec chanxun 找不到该存储过程???
exec chanxun '001','name','M','18','A class'
试试
CREATE proc chanxun
as
declare @sql varchar(100)
set @sql= 'select * from dbo.Table_1 where 1=1'
print @sql
GO
这样就可以CREATE proc chanxun
as
declare @sql varchar(100)
set @sql= 'select * from dbo.Table_1 where 1=1'
exec @sql
GO
这样就不行
写入参数 报错:消息 2812,级别 16,状态 62,过程 chaxun,第 18 行
找不到存储过程 '(select * from dbo.Table_1 where 1=1 )'。用20楼 print
消息: (select * from dbo.Table_1 where 1=1 )语句有问题
@ID varchar(50),@Name varchar(50),
@Sex varchar(50),@Age varchar(50),@Class varchar(50)
as
declare @sql varchar(100)
SET @sql= 'select * from dbo.Table_1 where 1=1'
IF(@ID != NULL)
set @sql = @sql + ' and ID ='''+@ID+''''
if(@Name !=NULL)
set @sql = @sql + 'and Name ='''+@Name+''''
if(@Sex != NULL)
set @sql = @sql + 'and Sex ='''+@Sex+''''
if(@Age != null)
set @sql = @sql + 'and Age ='''+@Age+''''
if(@Class != null)
set @sql = @sql + 'and Class ='''+@Class+''''exec @sql
go