SQL Stored Procedure Examples-Create,Delete and Drop Table ,insert,row count

Create,Delete and Drop Table  Through SQL Stored Procedure

Following is code which you can create new Stored procedure in SQL Stored procedure section and can write following code. You can understand easily by reviewing whole code.

 set ANSI_NULLS OFF  
 set QUOTED_IDENTIFIER OFF  
 GO  
 Create PROCEDURE [dbo].[SP_abc](@userID char(3)) AS  
 BEGIN  
 -- Statement to create table  
 if NOT exists (select * from dbo.sysobjects where id = object_id('abc'))  
 exec("CREATE TABLE abc(@field1 CHAR(3),LOGDT SMALLDATETIME,LOGTM DATETIME )")  
 END  
 set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 GO  
 -- Statement to Delete records from table table  
 Create PROCEDURE [dbo].[SP_DELETE](@ID1 char(3)) AS  
 BEGIN  
 SET NOCOUNT ON  
 if exists (select * from dbo.sysobjects where id = object_id('L'))  
 BEGIN  
 DELETE FROM abc WHERE ID1=@id1  
 END  
 END  
 set ANSI_NULLS OFF  
 set QUOTED_IDENTIFIER OFF  
 GO  
 -- Statement to Drop table  
 Create PROCEDURE [dbo].[DropTable1](@ID1 char(3)) AS  
 BEGIN  
 if exists (select * from dbo.sysobjects where id = object_id(@Id1))  
 EXEC("DROP TABLE " + @ID1)  
 END  

As mention variable with @ sign mean its parameter which you can pass value at run time.

E LEFT(NO,2)=@CD

Stored Procedure example to get Total Rows using query

 Set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 GO  
 Create PROCEDURE [dbo].[ COUNT_ROWS1](@CD CHAR(2))  
 AS  
 SELECT COUNT(*) AS TOTROW FROM HEAD1 WHERE LEFT(NO,2)=@CD  

This is a Stored Procedure to get Total Rows using select  query in SQL . You can use count method to get value of record count form table. You can specify parameter in query. For example how to get total number of rows which is code 0001 etc.




This will count row as per given cd no.

 Stored Procedure example to Insert values in tables

You can write following lines to create Stored Procedure to Insert values in tables in sql.

 Create PROCEDURE [dbo].[SP_INSERT_STATE]  
 (  
 @STATE_CD1 CHAR(3),  
 @STATE_NAME1 CHAR(22),  
 @COUNTRY_CD1 CHAR(4),  
 )  
 AS  
 INSERT INTO STATE VALUES(@STATE_CD,@STATE_NAME,@COUNTRY_CD)  
 RETURN  

In above statements (@STATE_CD,@STATE_NAME,@COUNTRY_CD is Parameter which can be passed at run time.