Cannot get stored procedure to work

I am trying to pass a value from one stored procedure into another. I have got the following stored procedures:

Stored Procedure 1:

ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

)

AS

BEGIN

DECLARE@.Client_IDINT

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

RETURN@.Client_ID

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.Client_ID = @.NewClient_IDOUTPUT

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

When I run this proecedure I get the following error:

FailedProcedure or function 'test2' expects parameter '@.Business_Name', which was not supplied. Cannot insert the value NULL into column 'Client_ID', table 'C:\INETPUB\WWWROOT\SWWEBSITE\APP_DATA\SOUTHWESTSOLUTIONS.MDF.dbo.client_premises_address'; column does not allow nulls. INSERT fails. The statement has been terminated.

However If I run Stored Procedure Test2 on its own it runs fine

Can anyone help with this issue? Is there something that I have done wrong in my stored procedures?

Also does anyone know If I can use the second stored procedure in a second webpage, but get the value that was created by running the stored proecdure in the previous webpage?

Try to make two changes:

1. in your test2, move@.Client_ID to parameter section

@.Business_Namenvarchar(50),

@.Client_ID int = 0 OUTPUT

2. in your test3, change code as following:

EXEC test 2@.Business_Name, @.NewClient_IDOUTPUT --Here you need to pass @.Business_Name into test2 too.

Then,

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.NewClient_ID , @.AddressLine1)

|||

try this

ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

@.Client_Id INToutput

)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.NewClient_IDoutput

SET@.Client_ID=@.NewClient_ID

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

Please try doing this, I am in hurry now, will explain you in detail later,

|||

If I change the @.Client_ID and put it in the paramter then when I do my insert, it gives me an error telling me that it needs me to pass in the parameter @.Client_ID, also it doesn't appear to like the change to the second procedure either, and says that I need to pass in the paramter Business_Name.

Do I need to change anything in my insert parameters within by sql datasource to get this working?

|||

Thanks for the suggestion Ritesh, I tried using this code, However I got the following error:

FailedProcedure or function 'test2' expects parameter '@.Client_Id', which was not supplied

Do I need to change anything In my parameters witin my sqldatasource??

|||

Please read my post again. 1).Because in your SP test2, you had @.Business_Name as parameter, you got to pass it in (Even though I don't know what it is). Ohterwise, you need to remove it. 2). After you move @.Client_ID to parameter section and set it as OUTPUT, you still need to pass it in in your test3 but set it as OUTPUT too.

|||

It doesn't like it when I pass @.Business_Name into the second procedure, I get the following:

FailedProcedure or function 'test3' expects parameter '@.Business_Name', which was not supplied.

This is the code, where my insert parameters are:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

InsertCommand="test2"InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:ControlParameterControlID="TextBoxBusinessName"Name="Business_Name"

PropertyName="Text"Type="String"/>

<asp:ParameterDirection="Output"Name="Client_ID"Type="Int32"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource3"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

InsertCommand="test3"InsertCommandType="StoredProcedure">

<InsertParameters>

<asp:ControlParameterControlID="TextBoxAddressLine1"Name="AddressLine1"

PropertyName="Text"Type="String"/>

<asp:ParameterDirection="Output"Name="Client_ID"Type="Int32"/>

</InsertParameters>

</asp:SqlDataSource>

|||

I asked you to pass @.Business_Name to test3 because I saw you had it as Paramater. But, from your test2, I didn't see you pass it in as Parameter. I am not sure why you had this parameter for test2. Now, this error was caused because the code in test3

EXEC test2 @.Business_Name, @.NewClientID OUTPUT.

If you don't need to pass @.Business_Name into test2 from your test3, you can do

EXEC test2 '', @.NewClientID OUTPUT

Not sure that is your purpose or not.

|||ALTER PROCEDUREtest2

(

@.Business_Namenvarchar(50)

@.Client_Id INToutput

)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTOclient_details(Business_Name)VALUES(@.Business_Name)

SELECT@.Client_ID =scope_identity()

IF@.@.NESTLEVEL = 1

SET NOCOUNT OFF

END

Stored Procedure 2 - Taking Client_ID from the proecedure test2

ALTER PROCEDUREdbo.test3

(

@.AddressLine1varchar(MAX),

@.Client_IDINT OUTPUT

)

AS

DECLARE@.NewClient_IDINT

EXECtest2 @.business_name,@.NewClient_IDoutput -- forgot to pass bussiness name

SET@.Client_ID=@.NewClient_ID

INSERT INTOclient_premises_address(Client_ID, AddressLine1)VALUES(@.Client_ID, @.AddressLine1)

SELECT@.NewClient_ID

--------

EXECtest2 @.business_name,@.NewClient_IDoutput -- forgot to pass bussiness name

please pass this parameter and try again

|||

Do I still need to pass @.Business_Name even though it is not needed in the cient_premises_address table?

The only variable which I wish to pass in from the first procedure is @.Client_ID which is a foreign key in the client_premises_address table

Sorry Im very new to this, so it is taking me a while to grasp