Kind of new to this, so forgive if question is beyond basic. This sproc below, when executed, only runs the sproc being called (dashboard_data_tenant_final) once, no matter what my select stmt says. Table "Tenant" has over a thousand rows. Is there a boneheaded mistake in this code? Any help or assistance would be greatly appreciated. /****** Object: StoredProcedure [dbo].[p_Dashboard_Tenant_Final] Script Date: 09/27/2013 09:28:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[p_Dashboard_Tenant_Final] AS BEGIN SET NOCOUNT ON DECLARE @tenantid UNIQUEIDENTIFIER /** Truncate DashboardData table for processing ***/ Delete DashboardData /** Truncate Dashboard tables for processing ***/ Delete DashboardDtl SELECT @TenantId= id, FROM Tenant EXEC [p_Dashboard_Data_Tenant_Final] @tenantid; SET NOCOUNT OFF END ,
Here's the mistake! SELECT @TenantId= id, FROM Tenant As you say that Table "Tenant" has over a 1000 rows, the above statement will try to insert all the 1000 rows into one variable at a time which is clearly insane. Possible Solution(s): 1. Narrow the SELECT such that ONLY ONE value of ID is returned to the variable declared. 2. Employ a CURSOR to handle the SELECT and EXEC statements **If and Only IF** your requirement is to execute the SP for each value of ID present in the Tenant table. Happy to Help!