如何把参数传到数据库的存储过程中生成sql语句的执行？

[chenhaoying]

三个变量@Code， @Item 和@Value
DECLARE @Sql NVarchar(4000)
SET @Sql=…………
EXEC (@Sql)
怎么根据参数生成sql语句，假如@Code='a'， @Item='b' 和@Value ='dsa' ，则生成
select * from tb where code='a' and b like '%dsa%'执行啊。

[fengfangfang]

set @sql='select * from tb where code=' + @Code + ' and ' + @Item + ' like '''%' + @Value + '%'''

[ljf96345]

楼上, 正解!!!

[cliff1002]

Dim mobjOraConnection As New OracleConnection
Dim mobjOraCommand As New OracleCommand

Dim strSQL as String = "select * from tb where code=@Code and @Item like @Value "

mobjOraConnection.Open()

mobjOraCommand.Parameters.Add("@Code","a")
mobjOraCommand.Parameters.Add("@Item","b")
mobjOraCommand.Parameters.Add("@Value ","%dsa%")

....执行strSQL就行了。

mobjOraConnection.Close()

[renyu732]

接分.

[happyRose]

Dim myConnection As New sqlConnection
Dim myCommand As New sqlCommand

Dim strSQL as String = "select * from tb where code=@Code and @Item like @Value "

myConnection .Open()

myCommand .Parameters.Add("@Code","a")
myCommand .Parameters.Add("@Item","b")
myCommand .Parameters.Add("@Value ","%dsa%")

....执行strSQL就行了。

myConnection .Close()

[chenhaoying]

我是想在存储过程中生成的。
二楼的：
set @sql='select * from tb where code=' + @Code + ' and ' + @Item + ' like '''%' + @Value + '%'''
不知是少了个单引号还是多了，就是改不对。高手帮忙啊。

[zyug]

set @sql='select * from tb where code=' + @Code + ' and ' + @Item + ' like %' + @Value + '%'
应该是这样吧

[luckyprg]

改成这样
set @sql='select * from tb where code=' + @Code + ' and ' + @Item + ' like ''%' + @Value + '%'''
like后面应该是两个单引号。

[njuzgj]

UP

[xiedan79]

SqlConnection conn = new ......
conn.open();
SqlCommand comm = new SqlCommand("存储过程名",conn);
comm.CommandType = CommandType.StoredProcedure;
下面是定义存储过程返回值的参数

SqlParameter Param = new SqlParameter();
Param.ParameterName = "参数名";
Param.DbType = DbType.Int32;
Param.Size = 4;
Param.Direction = ParameterDirection.Output;

不返回值的参数就不用说了吧

[zyug]

哈哈,我知道你的意思
你要把 '转义

[aspme]

set @sql="select * from tb where code='" + @Code +" ' and ' "+ @Item +" ' like '%" + @Value + "%'"

如果是int类型的话就把''去掉,比如:'" + @Code +" ' 就要改成:" + @Code +"

[luckyprg]

或者改成这样：
set @sql='select * from tb where code=''' + @Code + ''' and ' + @Item + ' like ''%' + @Value + '%'''

[chliang315]

Dim mobjOraConnection As New OracleConnection
Dim mobjOraCommand As New OracleCommand

Dim strSQL as String = "select * from tb where code=@Code and @Item like @Value "

mobjOraConnection.Open()

mobjOraCommand.Parameters.Add("@Code",SqlDbType.NVarChar,20,"Code");
mobjOraCommand.Parameters["@Code"].Value='a';
mobjOraCommand.Parameters.Add("@Item",SqlDbType.NVarChar,20,"Item");
mobjOraCommand.Parameters["@Item"].Value='b'
mobjOraCommand.Parameters.Add("@Value ",SqlDbType.NVarChar,20,"Value");
mobjOraCommand.Parameters["@Value"].Value='%dsa%';

....执行strSQL就行了。

mobjOraConnection.Close()

[ghchen]

set @sql='select * from tb where code=''' + @Code + ''' and ' + @Item + ' like ''%' + @Value + '%'''

[xzq686]

--生成测试数据
--drop table tb
CREATE TABLE tb
(
Code varchar(20),
b varchar(20)
)

insert tb
select 'a','adsa' union
select 'a','aadsaa' union
select 'aa','aadsaa' union
select 'aa','aadsaa'

--drop procedure proc_select_example
CREATE PROCEDURE dbo.proc_select_example
@Code varchar(20),
@Item varchar(20),
@Value varchar(20)
AS

DECLARE @Sql NVarchar(4000)
set @sql='select * from tb where code=''' + @Code +''' and ' + @Item + ' like ''%' + @Value + '%'''
--print @sql
EXEC (@Sql)

GO

exec proc_select_example 'a','b','dsa'

--生成结果：
--
--code b
--a aadsaa
--a adsa

[xzq686]

--生成测试数据
--drop table tb
CREATE TABLE tb
(
Code varchar(20),
b varchar(20)
)

insert tb
select 'a','adsa' union
select 'a','aadsaa' union
select 'aa','aadsaa' union
select 'aa','aadsaa'

--drop procedure proc_select_example
CREATE PROCEDURE dbo.proc_select_example
@Code varchar(20),
@Item varchar(20),
@Value varchar(20)
AS

DECLARE @Sql NVarchar(4000)
set @sql='select * from tb where code=''' + @Code +''' and ' + @Item + ' like ''%' + @Value + '%'''
--print @sql
EXEC (@Sql)

GO

exec proc_select_example 'a','b','dsa'

--生成结果：
--
--code b
--a aadsaa
--a adsa