[2005] Regarding stored procedure

[jnaneswari]

Hello all,

Is it possible to pass query as a value to the stored procedure input parameter.

[popskie]

can u give a sample of what you want?

[jnaneswari]

i have 2 tables product and cutomer details , i want to store customerid in product table i created stored procedure for inserting into product table.
Customer Name is the input field in my product form.
i want to fetch the corresponding customerid and stored in product table.

strSQL = "SELECT CustomerId FROM CustomerDetails Where CustomerName=" TextBox1.Text  "
        sda = New SqlDataAdapter(strSQL, strConn)
          sda.Fill(ds, "CustomerDetails")
        If ds.Tables("CustomerDetails").Rows.Count = 0 Then
            CustomerId = 0
        Else
            CustomerId = ds.Tables("CustomerDetails").Rows(0).Item("CustomerDtlsId")
        End If
 
        Cmd.Parameters.Add("@CustomerDtlsId", SqlDbType.VarChar).Value = CustomerId

what i want is can i pass the query directly to the stored procedure parameter, or is there any other way to do this.

Cmd.Parameters.Add("@CustomerDtlsId", SqlDbType.VarChar).Value="SELECT CustomerId FROM CustomerDetails Where CustomerName=" TextBox1.Text "

[popskie]

in my project i do this , just try

Sqlsel.CommandType = CommandType.StoredProcedure;
Sqlsel.Parameters.Add(new MySqlParameter("?CustomerId",MySqlDbType.String,20));
Sqlsel.Parameters["?CustomerId"].Value = textBox1.Text;

[jnaneswari]

i am not getting this where should i write the sql query

[popskie]

SELECT CustomerId FROM CustomerDetails Where CustomerName=?CustomerId

[sachien]

You can pass a query to Store procedure as input parameter and get this parameter in a variable of datatype nvarchar.

Then in that stored procedure run like this

exec sp_executesql <your variable>

I hope this will help

-Sachien

[jnaneswari]

Can you please post me a line of code

[jnaneswari]

This part is in sql server 2005

declare @sql as nvarchar(500)

insert into product(ProductName,customerId)values(@productName,@CustomerId)


how can i pass sqlquery to @sql parameter from the codebehind file of asp.net 2.0.how can i take the value passed or query passed from front end in sql server 2005.

after that i need to execute that query store the result in @customerid and pass that @customerid to the insert statement of the product table.

Please help me to solve this

Thanks in advance