question

ctruglio avatar image
ctruglio asked

passing guid as parameter to sp

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 ,
stored-proceduressyntaxparameter
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@crruglio/Moderators: Please edit the site discussion value from meta-askssc to default, so that the thread comes up in the feed with other related questions. Thanks.
2 Likes 2 ·
formatted the code
0 Likes 0 ·

1 Answer

· Write an Answer
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.