存储过程返回值 没取到～ 求助！

[guohouchang]

CREATE PROCEDURE sp_DispNewPictrueList
@Count int = 0 output
AS
 EXEC( 'SELECT TOP 5 p.[id],u.userName as 作者,[Title] AS 作品,[imgUrl] AS url
,[vote] AS 票数,[hit] AS 浏览 FROM [PictrueVote].[dbo].[Pictrue] AS p INNER JOIN Users as u ON p.uid = u.id
  Where p.[state] = ''true'' order by p.postTime Desc');
SELECT @Count =COUNT(1) from  [PictrueVote].[dbo].[Pictrue]
GO

string proc = "sp_DispNewPictrueList";
            List<Pictrue> list = new List<Pictrue>();
            count = 0;
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = proc;
                SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value);
                cmd.Parameters.Add(param);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                count = (int)param.Value;
                while (reader.Read())
                {
                    Pictrue pic = new Pictrue
                    {
                        id = Convert.ToInt32(reader[0]),
                        UserName = Convert.ToString(reader[1]),
                        Title = Convert.ToString(reader[2]),
                        imgUrl = Convert.ToString(reader[3]),
                        vote = Convert.ToInt32(reader[4]),
                        hit = Convert.ToInt32(reader[5])
                    };
                    list.Add(pic);
                }
            }
            return list;

以上为主要代码～

[guohouchang]

ding~

[guohouchang]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[SQL77]

CREATE PROCEDURE sp_DispNewPictrueList
@Count int = 0 output
AS
EXEC( 'SELECT TOP 5 p.[id],u.userName as 作者,[Title] AS 作品,[imgUrl] AS url
,[vote] AS 票数,[hit] AS 浏览 FROM [PictrueVote].[dbo].[Pictrue] AS p INNER JOIN Users as u ON p.uid = u.id
Where p.[state] = ''true'' order by p.postTime Desc');
SELECT @Count =COUNT(1) from [PictrueVote].[dbo].[Pictrue]
GO

楼主这是什么意思????

那第一句动态执行,与第二句没关系吧!!!!

[fuda_1985]

SELECT @Count =COUNT(1) from [PictrueVote].[dbo].[Pictrue]
你这样写是取不到值的，在sql中select还有个作用是赋值。
你要再加上一句
select @Count才行。

[SQL77]

[Quote=引用 4 楼 fuda_1985 的回复:]
SELECT @Count =COUNT(1) from  [PictrueVote].[dbo].[Pictrue]
你这样写是取不到值的，在sql中select还有个作用是赋值。
你要再加上一句
select @Count才行。
[/Quote]
就是这样了,要查询出来才显示的,

[slund]

存储过程执行成功否？

[hrbwgs1111]

借楼主的帖子发下我的问题，如有打扰见谅

http://topic.csdn.net/u/20090825/15/71d11f43-08a6-4f04-8152-a0737dddd2d4.html?seed=1079724398&r=59322234#r_59322234

[mengxj85]

CREATE PROCEDURE sp_DispNewPictrueList
@Count int = 0 output
AS
EXEC( 'SELECT TOP 5 p.[id],u.userName as 作者,[Title] AS 作品,[imgUrl] AS url
,[vote] AS 票数,[hit] AS 浏览 FROM [PictrueVote].[dbo].[Pictrue] AS p INNER JOIN Users as u ON p.uid = u.id
Where p.[state] = ''true'' order by p.postTime Desc');
SELECT @Count =COUNT(1) from [PictrueVote].[dbo].[Pictrue]
--
select @Count
---OR
print @count
GO

[tommy326]

SELECT @Count =COUNT(1) from [PictrueVote].[dbo].[Pictrue]
这句话是对@Count赋值

你应该加个输出@Count的语句
select @Count

[guohouchang]

[Quote=引用 5 楼 sql77 的回复:]
引用 4 楼 fuda_1985 的回复:
SELECT @Count =COUNT(1) from  [PictrueVote].[dbo].[Pictrue]
你这样写是取不到值的，在sql中select还有个作用是赋值。
你要再加上一句
select @Count才行。

就是这样了,要查询出来才显示的,
[/Quote]

这样的话 查出的记录不是2张表吗。我就是想把这个@Count 作为一个输出参数返回～ 然后在后台代码中取得这个值。。其他的内容通过SqlDataReader读取

[振乾]

string proc = "sp_DispNewPictrueList";
            List<Pictrue> list = new List<Pictrue>();
            count = 0;
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = proc;
                SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value);
                cmd.Parameters.Add(param);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                count = (int)param.Value;
                while (reader.Read())
                {
                    Pictrue pic = new Pictrue
                    {
                        id = Convert.ToInt32(reader[0]),
                        UserName = Convert.ToString(reader[1]),
                        Title = Convert.ToString(reader[2]),
                        imgUrl = Convert.ToString(reader[3]),
                        vote = Convert.ToInt32(reader[4]),
                        hit = Convert.ToInt32(reader[5])
                    };
                    list.Add(pic);
                }
            }
            return list;

昌哥，你这里是不是应该把 count = (int)param.Value;
放在 while 循环之后试试哈。

[ruanxiaping]

SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Output, true, 0, 0,string.Empty, DataRowVersion.Default, DBNull.Value);

参数过长！！！！有很多可疑点：
1:第四个参数0，代表参数的长度，为0 的话，返回的是什么？记录的总数的长度应该大于等于1位数的，指定为4试一下
2:最后一个参数：DBNull.Value 是不是已经指定了值为空啊？，所以你才出现空引用异常？
建议：
SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;

[yh7272hy]

[Quote=引用 10 楼 ruanxiaping 的回复:]
SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Output, true, 0, 0,string.Empty, DataRowVersion.Default, DBNull.Value);

参数过长！！！！有很多可疑点：
1:第四个参数0，代表参数的长度，为0 的话，返回的是什么？记录的总数的长度应该大于等于1位数的，指定为4试一下
2:最后一个参数：DBNull.Value 是不是已经指定了值为空啊？，所以你才出现空引用异常？
建议：
  SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 4);
                param.Direction = ParameterDirection.Output;
[/Quote]

[振乾]

[Quote=引用 10 楼 ruanxiaping 的回复:]
SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Output, true, 0, 0,string.Empty, DataRowVersion.Default, DBNull.Value);

参数过长！！！！有很多可疑点：
1:第四个参数0，代表参数的长度，为0 的话，返回的是什么？记录的总数的长度应该大于等于1位数的，指定为4试一下
2:最后一个参数：DBNull.Value 是不是已经指定了值为空啊？，所以你才出现空引用异常？
建议：
  SqlParameter param = new SqlParameter("@Count", SqlDbType.Int, 4);
                param.Direction = ParameterDirection.Output;
[/Quote]

平哥，的讲解 很深入,ding ………………~！~！~！