SQL Server - ..Execute statements which are stored in DB

Asked By shah zeb on 17-Nov-11 01:56 AM
Hi,
I have statements which i need to execute in store procedures.
The statement i store in variable but cannot execute it.

How this can be done?
Regards,
sz
Sunil Darji replied to shah zeb on 17-Nov-11 01:59 AM

 Use like below


DECLARE @query varchar(Max)
set @query ='Select * from Employee'
exec(@query )

hope this help u
Reena Jain replied to shah zeb on 17-Nov-11 01:59 AM
hi,

Like this you can call stored procedure in page
SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=Northwind;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("Files_Insert", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Name", textbox1.text);
command.Parameters.AddWithValue("@PhoneNo", textbox2.text);
conn.Open();
int result=Convert.ToInt32(command.ExecuteNonQuery());
if(result >0)
{
Lable1.Text="Record Successfully Inserted";
}
conn.Close();

hope this will help you
Chintan Vaghela replied to shah zeb on 17-Nov-11 02:00 AM
Hello,

Try following way

Create PROCEDURE [dbo].[TestSP]

AS

BEGIN

   

DECLARE @Query AS VARCHAR(MAX)

    SET @Query = 'Select * from Table'

   

    EXEC(@Query)

    END

dipa ahuja replied to shah zeb on 17-Nov-11 02:06 AM
To Create Procedure Go to solution explorer expand database and from the stored Procedure , right click on it and choose new stored procedure
 
ALTER PROCEDURE dbo.Deletion
(
  @ID int
)
    
AS
    /* SET NOCOUNT ON */
DELETE FROM  table2 WHERE ID=  @ID
RETURN
 
protected void Button1_Click(object sender, EventArgs e)
{
   string conn = "ConnectionString";
  SqlConnection sqlcon = new SqlConnection(conn);
  sqlcon.Open();
  SqlCommand comm = new SqlCommand("Deletion", sqlcon);
   comm.CommandType = CommandType.StoredProcedure;
  comm.Parameters.AddWithValue("ID", int.Parse(txtid.Text.ToString()));
  comm.ExecuteNonQuery();
}
 
hope this will help you!!
 
 
 
 
 
shah zeb replied to Sunil Darji on 17-Nov-11 02:13 AM
Thanks Sunil for a quick reply but lets say i need something like this

DECLARE @query varchar(Max)
set @result_sum ='10+10'
exec(@result_sum)

When i do something like this i get error
Sunil Darji replied to shah zeb on 18-Nov-11 03:55 AM

 1. if want to sum of 10 + 10 then use like

    Declare @result_sum int
   set @result_sum  = 10+10
   select @result_sum

2. If you want to print '10+10' then

  DECLARE @result_sum varchar(Max)
  set @result_sum ='10+10'
   select @result_sum

3.  if you want execute query then use

   DECLARE @Query varchar(Max)
   set @Query ='select * from Employee'
   exec(@Query)



   Hope help you...
   Happy Coding