如何从临时表中获取数据？ [英] How to get the data from temp table?

问题描述

我有2个临时表，我希望得到数据并在浏览器中查看它但我总是得到索引超出范围的错误被捕获....我怎么能摆脱那个问题..

I have 2 temp table and i want to get there data and view it in browser but i always get an error of index out of range was caught.... how can I get out of that problem..

推荐答案

下面是我的数据访问代码...

below is my data access codes...

public List<CategoriesRollup> CategBranch()
   {
       var categBranch = new List<CategoriesRollup>();
       const string spName = "categories_rollup_newbranchlist"; //"categories_rollup_branchlist";
       using (var con = _connectionString.GetConnection())
       using (var cmd = new SqlCommand(spName, con))
       {
           try
           {
               cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = UsersSession.Users.UserName;
               cmd.Parameters.Add("@terminal", SqlDbType.VarChar).Value = IpAddress.GetMyIpAddress();
               cmd.Parameters.Add("@type", SqlDbType.TinyInt).Value = 1;
               cmd.CommandType = CommandType.StoredProcedure;
               var rdr = cmd.ExecuteReader();

               if (rdr.HasRows)
                   while (rdr.Read())
                categBranch.Add(new CategoriesRollup
                                      {
                                      // Result = Convert.ToInt32(rdr["result"]),
                                       BranchLookupKey = Convert.ToInt32(rdr["BranchLookupKey"]),
                                       Branch = rdr["Branch"].ToString(),
                                       ComboPromoKey = Convert.ToInt32(rdr["ComboPromoKey"]),
                                       Combo = rdr["Combo"].ToString(),
                                       CategoryKey = Convert.ToInt32(rdr["CategoryKey"]),
                                       Category = rdr["Category"].ToString(),
                                       ItemCategoryKey = Convert.ToInt32(rdr["ItemCategoryKey"]),
                                       Item = rdr["Item"].ToString()
                                      });

then我的sp in ssms

then my sp in ssms

ALTER PROCEDURE [dbo].[categories_rollup_branchlist]

@range INT = 0,
@username VARCHAR(20), 
@terminal VARCHAR(60),
@type TINYINT


AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
@Error INT,
@Rowcount INT,
--****** SYSTEM LOGS
@action VARCHAR(20),
@log_details VARCHAR(8000),
@record_key INT,


---temporary table
--@key INT,
--@result INT,
@categoryKeys VARCHAR(max),
@startPromos INT,
@countPromos INT,
@promoKey INT,
@parse VARCHAR(max),
@final VARCHAR(max),
@branch_code VARCHAR(max)
SET @Error = 0

SET @action = 'CATEGORIES ROLLUP LIST'
SET @record_key = 0

BEGIN TRANSACTION begin_transactionA
SAVE TRANSACTION save_transactionB

	SET ROWCOUNT @range
	
	-----
CREATE TABLE #promosPerBranch	
(   -- id INT ,
	id INT IDENTITY (1,1),
	PromoKey int
)
CREATE TABLE #wholebranch (
		--Id INT IDENTITY (1,1),
		--Id INT,
		--   result INT,
		BranchLookupKey INT,
		Branch VARCHAR(50),
		ComboPromoKey INT,
		Combo VARCHAR(60),
		CategoryKey INT,
		Category VARCHAR(50),
		ItemCategoryKey INT,
		Item VARCHAR(60)	
)
    --SET IDENTITY_INSERT #promosPerBranch ON
		INSERT INTO #promosPerBranch
		SELECT promo_key AS 'PromoKey' 
		FROM dbo.branch_promos 
		WHERE branch_lookup_key = branch_lookup_key
		SELECT @countPromos = COUNT(*) from #promosPerBranch
		SET @startPromos = 1
		WHILE @startPromos <= @countPromos
BEGIN
		SELECT @branch_code = branch_code FROM dbo.branches WHERE lookup_key = @promoKey
		SELECT @promoKey = promoKey FROM #promosPerBranch WHERE id = @startPromos
		SELECT @categoryKeys = p.category_keys FROM dbo.promos p WHERE [key] = @promoKey

	--SET IDENTITY_INSERT #wholebranch ON
		INSERT INTO #wholebranch
		SELECT  
			--   0 AS 'result',
			   b.lookup_key AS 'BranchLookupKey',
			   b.branch_code AS 'Branch',
			   p.[key] AS 'ComboPromoKey',
			   p.name AS 'Combo',
			   c.[key] AS 'CategoryKey',
			   c.name AS 'Category',
			   i.category_key AS 'ItemCategoryKey',
			   i.name AS 'Item'
			 
			   FROM items i 
			   INNER JOIN dbo.branches b ON i.category_key = b.lookup_key
			   INNER JOIN dbo.categories c ON i.category_key = c.[key]  
			   INNER JOIN dbo.promos p ON p.[key] = @promoKey
			   WHERE category_key IN (SELECT [key] FROM dbo.categories
			   WHERE [key] IN (SELECT * FROM dbo.parse_comma_delimited_string(@categoryKeys)))
		SET @startPromos = @startPromos + 1
	--SET IDENTITY_INSERT #wholebranch OFF
	--SET IDENTITY_INSERT #promosPerBranch OFF
END	
SELECT * FROM #promosPerBranch
SELECT * FROM #wholebranch
DROP TABLE #promosPerBranch
DROP TABLE #wholebranch

这篇关于如何从临时表中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！