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...

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

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