存储过程遇到的问题

[jiaai]

declare @sqlstr nvarchar(500),
@SaleNum int,
@Style varchar(10),
@StoreId varchar(10),
@TName varchar(10)
Set @TName='200608'
Set @StoreId='24'
Set @Style='W16004'
Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='+rtrim(@Style)
Execute sp_executesql @sqlstr,N'@SaleNum int out',@SaleNum out
列名 'W16004' 无效,烦请帮忙

[tx1icenhe]

declare @sqlstr nvarchar(500),
@SaleNum int,
@Style varchar(10),
@StoreId varchar(10),
@TName varchar(10)
Set @TName='200608'
Set @StoreId='24'
Set @Style='W16004'
Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='''+rtrim(@Style)+''''
Execute sp_executesql @sqlstr,N'@SaleNum int out',@SaleNum out

[tx1icenhe]

这个问题不值100分

[九斤半]

呵呵，这可是冒牌说的，进来蹭点分~~~

[tx1icenhe]

说说动态语句的一般调试方法

declare @sqlstr nvarchar(500),
@SaleNum int,
@Style varchar(10),
@StoreId varchar(10),
@TName varchar(10)
Set @TName='200608'
Set @StoreId='24'
Set @Style='W16004'

Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='+rtrim(@Style)

print @sqlstr --print出sql语句可以很快定位错误

Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='''+rtrim(@Style)+''''

print @sqlstr

Execute sp_executesql @sqlstr,N'@SaleNum int out',@SaleNum out

[diaowf]

说说动态语句的一般调试方法

declare @sqlstr nvarchar(500),
@SaleNum int,
@Style varchar(10),
@StoreId varchar(10),
@TName varchar(10)
Set @TName='200608'
Set @StoreId='24'
Set @Style='W16004'

Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='+rtrim(@Style)

print @sqlstr --print出sql语句可以很快定位错误

Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='''+rtrim(@Style)+''''

print @sqlstr

Execute sp_executesql @sqlstr,N'@SaleNum int out',@SaleNum out

[chenjunjarysky]

顶，让我学习到了一种检测错误的方法。

[OracleRoob]

print @sql


动态sql语句基本语法
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

[chenjunjarysky]

我靠！这个贴都快20天了，还没结贴啊。

[老本]

呵呵

[wisdomone]

up

[allright_flash]

declare @sqlstr nvarchar(500),
@SaleNum int,
@Style varchar(10),
@StoreId varchar(10),
@TName varchar(10)
Set @TName='200608'
Set @StoreId='24'
Set @Style='W16004'
Set @sqlstr=N'Select @SaleNum=SaleNum from ['+@TName+'] where ['+@TName+'].StoreID='+@StoreID+' and ['+@TName+'].Style='''+rtrim(@Style)+''''

[xzq111]

这是自己给自己找麻烦，把问题复杂化了，简单的select 语句就可以输出，非要用变量输出。

[allright_flash]

不止值100?