Software Applications Programming Errors and solutions.

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 PROCEDUR

You can write Stored procedure following  to Insert, Update and delete records from tables in sql. 1. Stored procedure to Insert records in table. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_Insert] @CD char(7), @ITEM_DESC CHAR(60) AS INSERT [dbo].[ITEM] ( [CD], [ITEM_DESC] ) VALUES ( @CD, @ITEM_DESC ) 2. Example of Stored procedure to Update data in to table . set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_Update] @CD char(7), @ITEM_DESC char(60) AS UPDATE [dbo].[ITEM] SET [ITEM_DESC] = @ITEM_DESC WHERE [CD] = @ CD 3. Example of Stored procedure to Update data in to table set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SP_ITEM_ Delete] @ CD char(7) AS DELETE FROM [dbo].[ITEM] WHERE [CD] = @CD You can set more conditions and columns as per requirement .

Function To Returns Max ID As Identity With Table Name and Field Name As Parameter in SQL CREATE PROCEDURE [dbo].[aMaxId] @TableName nvarchar(100), @FieldName nvarchar(100) AS DECLARE @sql nvarchar(4000) SELECT @sql = ' SELECT IsNull(Max('+@FieldName+'),0)+1 as Maxi' + ' FROM dbo.'+@TableName EXEC sp_executesql @sql

This is a Sql stored procedure which can be used to change column name from lower case to upper in tables. Its simple to use and understand. DECLARE @count INT, @script NVARCHAR(1000), @column_name_old VARCHAR(100), @column_name_new VARCHAR(100) DECLARE @table TABLE (nid INT IDENTITY(1,1), column_name VARCHAR(1000)) INSERT INTO @table(column_name) SELECT name FROM syscolumns WHERE id = OBJECT_ID('test_column_change') SELECT @count= count(1) FROM @table WHILE(@count >=1) BEGIN SELECT @column_name_old = column_name FROM @table WHERE nid = @count SELECT @column_name_new = UPPER(@column_name_old) SELECT @Script ='sp_rename ''test_column_change.'+@column_name_old+''' , '''+@column_name_new+''' , ''COLUMN''' --select @script EXEC (@Script) SELECT @Count = @count-1 END

This is nice example of Stored procedure . You can get Idea that how to Create stored procedure to get employee details as per filtered parameter range of sql. To become SQL Expert we should required to expert in Stored procedure and parameter terms. SET ANSI_NULLS ON Go SET QUOTED_IDENTIFIER ON Go CREATE PROCEDURE spx_GetEMploye @Filter VARCHAR(50) AS BEGIN SET NOCOUNT ON; IF @Filter = 'ALL' SELECT ContactName, City, Country, PostalCode FROM Employee ELSE IF @Filter = '10' SELECT TOP 10 ContactName, City, Country, PostalCode FROM Employee ELSE SELECT ContactName, City, Country, PostalCode FROM Employee WHERE mailto:Country=@Filter END GO

To skip a record in a cursor and continue with the next record you can try below code in oracle or sql. declare cursor cur1 is select * from emp; rec cur1%rowtype; begin open cur1; loop fetch cur1 into rec; dbms_output.put_line(rec.ename||' '||rec.sal); fetch cur1 into rec; exit when cur1%notfound; end loop; close cur1; end;

#!/usr/bin/perl open(FOO,">c:/prod_test.sql"); $create = "Create or replace procedure "; $body = "\nbegin\n null;\n"; $statement = $create."foo("; for ($x=0;$x<8188;$x++){ $statement .= "a$x in varchar2,\n "; } $statement .= "done in varchar2)\n as"; print FOO $statement.$body."end;"; close FOO; at 8187 parameters it compiled. at 8188 it gave a program to large error. its  tested body length by adding "null;" to the body 10,000 times and it didn't mind. One other thing is the 2499 character limit per line.

You can write following code to generate ID from any Table through stored procedure. Here Generate_Id is parameter to pass Id field name. You may have different field name for your table so this stored procedure is common for all tables. This stored procedure may be very helpful for you. This is a Common code for all table just call this stored procedure and pass table Name, Criteria and Primary key column Name as parameter. set ANSI_NULLS OFF set QUOTED_IDENTIFIER OFF GO Create PROCEDURE [dbo].[Generate_ID] @TablePK CHAR(50), @Table CHAR(30), @Criteria VARCHAR(300)='' AS BEGIN IF @Criteria <> '' BEGIN EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+' WHERE '+@Criteria+'') END ELSE BEGIN EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+'') END END RETURN

This is example of Stored Procedure to get Grand Total of columns using select query in sql. You can try for your database table too. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[CALC_TOTAL1] ( @NO CHAR(15), @AMTNUMERIC1(9,2)=NULL, @TAX NUMERIC1(9,2)=NULL, @DISCNUMERIC1(9,2)=NULL, @G_TOTAL NUMERIC1(9,2)=NULL ) AS SELECT @TOT_AMOUNT=SUM(AMOUNT) FROM TRANS WHERE TNO=@CNO SELECT @TAX=ISNULL(SUM(TAX_A),0) FROM TAX WHERE NO=@TCNO SELECT @TOT_DISC_A=ISNULL(SUM(DISC_A),0) FROM DISCOUNT WHERE NO=@CNO SET @G_TOTAL = (@AMT- @TOT_DISC_A) + @TAX UPDATE FOOT SET SUBTOTAL=@TOT_AMOUNT,TAX=@TAX,DISCOUNT=@TOT_DISC_A,G_TOTAL=@G_TOTAL WHERE CNO=@CNO