Generating Stored Procedures dynamically

Share |

Create Stored Procedure

Create stored procedure to auto generate another stored procedure contain insert command for any tables.

CREATE PROCEDURE Create_procedure_To_insert

parameters

You must add two parameters:

1- Table Name: to generate insert command on it.

2- Developer Name: to set developer name in comments.

//
// 

CREATE PROCEDURE create_procedure

@table varchar(200),

@DeveloperName varchar(200),

@Createtable varchar(20)

//

Declaration Variables

You need to declaration many variables to use in stored procedure see below code to know it.

declare @testTable varchar(8000)

declare @testTable2 varchar(8000)

declare @testTable3 varchar(8000)

declare @opration varchar(8000)

declare @final varchar(8000)

declare @OP varchar(100)

 

1- @testTable: used this variable to set all columns from table.

2- @testTable2:used this variable to set all datatype for columns from table.

3- @testTable3:used this variable to set all parameters for columns from table.

4- @opration :used this variable to set insert command.

5-@final :used this variable to set auto generate stored procedure.

6-@OP :used this variable to set name for the new stored procedure.

Initialization Variables

You need to set empty values in the below variables.

set @testTable=''

set @testTable2=''

set @final=''

set @testTable3=''

set @opration=''

declare @Datetime varchar(50)

set @Datetime=getdate()

Importents Code

You need to create three select statment :

1- The First Select statment to get all columns from information_schema.columns when table equal table name in parameters ,you must get 'isidentity'= zero becuase if 'isidentity'=one you can not insert data on identity columns

2-The second Select statment to get all datatype from information_schema.columns when table equal table name in parameters .you must get 'isidentity'= zero becuase if 'isidentity'=one you can not insert data on identity columns.

3-The Third Select statment to get columns name and set @columns name in parameters

after that you must generate the new stored procedure ,so you can create structure for procedure in string datatype and set the string in execute function to execution stored procedure "Exec(@final)"

select @testTable=@testTable+ ',

'+column_name from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0) AND (column_default IS NULL)

select @testTable2=@testTable2+ ',

@'+column_name+' '+data_type+'(' + cast(character_maximum_length as varchar(10)) +')' + case is_nullable when 'no' then ' ' when 'yes' then '=null' end from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)and character_maximum_length<>null AND (column_default IS NULL)and data_type<>'text'

select @testTable2=@testTable2+ ',

@'+column_name+' '+data_type from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)and (character_maximum_length=null or data_type='text' ) AND (column_default IS NULL) 

select @testTable3=@testTable3+ ',

@'+column_name from information_schema.columns where table_name=@table and (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0) AND (column_default IS NULL)

set @testTable=SUBSTRING(@testTable,2,len(@testTable))

set @testTable2=SUBSTRING(@testTable2,4,len(@testTable2))

set @testTable3=SUBSTRING(@testTable3,2,len(@testTable3))

set @opration=' insert into [' +@table+'] 

(

'+@testTable+'

) 

values

(

'+ @testTable3 +'

)'

set @OP='InsertNew'+@table

set @final='/*

----------------------------------------------------------------------------------------

Store Procedure Name : SP__'+@OP +'

----------------------------------------------------------------------------------------

1- Creation Date :'+convert (varchar,getdate(),103) +'

2- Last Update :'+convert (varchar,getdate(),103)+'

3- Parametars No:6

4- Creation By :'+@DeveloperName+'

5- Last Update By :'+@DeveloperName+'

6- Return Value : Dataset

---------------------------------------------------------------------------------------

*/

Create PROCEDURE SP__'+@OP+'

(
'+ @testTable2 + ' 
)

AS

set nocount on 

' + @opration + '

Select * from [' +@table +']'

exec (@final)

Stored Procedures for ASP, ASP.NET, VB and VB.NET Programmers

Share |

Requirements: Programming knowledge in ASP or VB, MS SQL server & Database concepts.

When I was starting my carrier as a VB programmer, All I knew for connecting a Database with my front-end are, the Data control and RDO. Later When I moved to ASP, I was enjoying writing SQL statements. It was fun when the SQL statements were simple and hard when it comes to complex and nested queries. Writing direct SQL commands inside my VB or ASP program were not been a big issue to me, until I started thinking about performance of my applications. But when time came, I started learning Stored Procedures I was really encouraged to do possible re-factoring of my previous coding with Stored Procedures, and my life with direct SQL commands came to an end. I hope my SP(Stored Procedure) history was not boring. With an assumption that you are good in ASP or VB and SQL let us begin SPs.

What are Stored Procedures?
SPs are an advanced feature in SQL server that offers you to create, compile and run SQL statements in the server itself, to isolate your business logic from data logic and to improve the performance of your application.
In short, write SQL queries in a specific format in the SQL server and call them from your application, instead of writing queries inside your program code.

Why write Stored procedures?

1.Mainly to increase the performance and the momentum to our programs. When you write a stored procedure, it will be pre-compiled by the SQL server, so that it can increase the speed of executing the queries and hence your application. When you write a stored procedure the database(DB) server automatically generates an execution plan for the procedure. The plan will be updated whenever a change is made in the procedure by the DB server.
2.You can take away all your SQL commands so that the data logic can be isolated from your business logic(ie.,Coding). This kind of encapsulation helps the web server to read and interpret lengthy and complex SQL commands.
3.Then like COM, stored procedures can be reused. For example if you want to do the same query in two different ASP pages or in VB forms, you can reuse the stored procedure which you have written for one page, It saves you time.
4.The application code as well as the stored procedure code are becoming easy to maintain. Updating a stored procedure may not affect the other part of an application or user who uses the same stored procedure.
5.The queries can be customized by using input/output parameters, like functions and procedures which you write in your programming languages.

What are the types of Stored Procedures?
There are three types of stored procedures. Microsoft supplies several stored procedures to manipulate and administrate the database. Apart from them can write custom stored procedure to use them in our application level.

1. System stored procedures:
System stored procedures are mainly used for administrating, assisting, configuring and monitoring the SQL server.
2. Extended stored procedures:
Extended stored procedures are used to access SQL server by using dlls. We can use C or C++ to write extended stored procedures with Dlls. One good example is accessing the operating system commands on SQL server. We can use a stored procedure called xp_cmdshell to to run a DOS command, like the following one.

xp_cmdshell "dir c:\",

will list the files in the root directory of C drive

3. Custom stored procedures:
These are the stored procedures we write. There are several advantages in writing our own stored procedures. We are able to write complex and nested statements with less effort.
In this article we focus only on custom stored procedures, in the following section.

Where to write Stored Procedures?
You can write Stored Procedures in the following possible ways,
1. Using SQL Server Enterprise manager - To create a stored procedure, Open SQL server enterprise manager,

• Expand a server group; click and expand a server.
• Expand Databases; select the database where under which you want to create the procedure.
• Right-click Stored Procedures; then click New Stored Procedure..
• Type the stored procedure. Press CTRL-TAB to indent the text of a stored procedure.
• To check the syntax, click Check Syntax.
• To set the permissions, click Permissions.

2. Using Query Analyzer - Open query analyzer, select the DB, type the procedure and execute it.
3. From ASP Code - Write the procedures, create the connection with the DB using ADO, call the procedures directly from your code.
4. Using Visual Studio.NET - VS.NET users can write their procedures using "create New Procedure" like in Enterprise Manager and check the syntax there itself.
5. Create using Enterprise manager wizard - We can also use the wizard to create the procedures. Follow the steps to use the wizard.

• Expand a server group; then expand the server in which to create the view.
• On the Tools menu click Wizards...
• Expand Database.
• Double-click Create Stored Procedure Wizard.
• Complete the steps in the wizard.

Starting Stored Procedures
Now let us see an example procedure and peek into it a while for the basic understanding and structure of any stored procedure. I prefer enterprise manager to write stored procedure, and during the time of learning I recommend you to use Enterprise manager.

CREATE PROCEDURE sp_selauthors AS

BEGIN 

SELECT au_fname,au_lname,title,pub_name,pub_year

FROM tblAuthors 

WHERE pub_name = 'WROX'

END 

GO

After tying the procedure you can immediately check the syntax. Here all the SQL commands will be written in the BEGIN...END block. Here sp_selauthor is the procedures name.
All about parameters
We have seen a simple stored procedure that just selects the required values from a table. Now let us see a little more complex stored procedure with parameters. Stored procedures allow input and output(i/o) parameters to have a better control over the i/o values.
Here is an example with an input parameter,

CREATE PROCEDURE sp_selauthors 

@publisher_name varchar(50)

AS

BEGIN

SELECT au_fname,au_lname,title,pub_name,pub_year

FROM tblAuthors 

WHERE pub_name = @publisher_name

END

GO

....................[EXAMPLE1]

In the above example, @publisher_name is the input parameter and the values will be sent by your ASP or VB code. We will see, how to send values shortly.

Another example , an Insert statement, with two input parameter can help us to understand better.

CREATE PROCEDURE sp_InsName

@FirstName varchar(20), 

@LastName varchar(30)

AS 

BEGIN

INSERT INTO Names(FirstName, LastName)values(@FirstName, @LastName)

END

GO

....................[EXAMPLE2]
Here is an example with an output parameter,

CREATE PROCEDURE sp_sel_no_authors 

@count_authors int OUTPUT

AS

BEGIN

SELECT @count_authors = Count(*) FROM 

tblAuthors 

WHERE tblAuthors.pub_Name = "Wrox"

END

GO

....................[EXAMPLE3]

Here, the total no of authors who write for WROX press will be counted and sent through the output parameter. You can see the keyword OUTPUT to differentiate the output parameter from the input parameter.

Calling Stored procedures from your ASP code
Now let us call the stored procedure from ASP code. I will take stored procedure EXAMPLE2, to explain.

<%
   1: Dim objConn, sqlInsName, FName, LName
   2: FName = "Benny"
   3: LName = "Alexander"
   4: set objConn = Server.CreateObject("ADODB.Connection")
   5: objConn.Open "DSN=macdb;uid=test;pwd=test" 
   6: sqlInsName = "sp_InsName '" & FName & "', '" & LName & "'"
   7: objConn.Execute(sqlInsName) 
%>

The line ObjConn.Excecute runs the SP from the ASP code. Hope you understand how simple it is.
In the following ASP code we are going to see how to use command object to execute a stored procedure and also to get a OUTPUT value from the stored procedure.
Here I am using the stored procedure EXAMPLE3 for illustration.

<%
   1:  <!--#INCLUDE VIRTUAL="/include/adovbs.inc"-->
   2: Dim objConn, objCmd, objParam
   3: set objConn = Server.CreateObject("ADODB.Connection")
   4: objConn.Open "DSN=macdb;uid=test;pwd=test" 
   5: Set objCmd = Server.CreateObject("ADODB.Command")
   6: objCmd.CommandText = "sp_sel_no_authors"
   7: objCmd.CommandType = adCmdStoredProc
   8: objcmd.ActiveConnection = objConn 
   9: Set objParam = objCmd.CreateParameter ("@count_authors",adInteger,adParamOutput,4)
  10: objCmd.Parameters.Append objParam
  11: ObjCmd.Execute
  12: <HTML>
  13: <BODY>
  14: No of Authors write for wrox are: <%= objCmd.Parameters.("@count_authors")
%>

</BODY> 

</HTML> 

You may need a little explanation on the above written ASP code. Here I create a command object objCmd, and setting the parameters. The name of the returned variable is "@count_authors", which is also mentioned in the stored procedure. The type of this variable is integer with length 4. When I execute the SP, sp_sel_no_authors, I get the return value, as output which can be displayed in the web page. I hope this two examples are sufficient enough for ASP programmers. Now let us turn to VB.

A Tip: You may get an error when you pass date as an input parameter. In that case send the date as a string and convert the string as date inside the SP.
For example if you pass the date as a variable called Orderdate, Then add the following lines of code in your stored procedure,

DECLARE @Orderdate DATETIME

SELECT @Orderdate=CONVERT(datetime, @Orderdate)

Here we convert the date string into datatime data type. In this way you can manipulate dates.

Calling Stored procedures from your VB code
If a stored procedures that do not return records (or rows) can be executed from Visual Basic with the ExecuteSQL() method as follows. That means we can not use this method with SELECT statements. But if the the SQL statement returns records then we need to use a Dynaset or Snapshot to capture the values. The following returns a set of recordset values using a Data control.

objDC.Options = dbSQLPassThrough

objDC.Recordsource = "sp_sel_no_authors" 

objDC.Refresh

Another example can give you better understanding with stored procedures that return values and that not return values.

Dim objDB as Database

Dim lng as Long

Dim objRS as Recordset

Set objDB = DBEngine.Workspaces(0).OpenDatabase("", False, False,"ODBC;_ DSN = macdsn;uid=test;pwd=test:") ' For SPs that don't return rows.

lng = Db.ExecuteSQL("YourSP_Name") ' SP which return rows.

Set ObjRS = Db.OpenRecordset("YourSP_Name", dbOpenSnapshot, _

dbSQLPassThrough)

Column1.text = objRS(0) ' Column one

Column2.text = objRS!ColumnName ' Column two

Column3.text = objRS("ColumnName") ' Column three

Handling errors in stored procedures
In this section we will cover the necessary information you need to know about finding and dealing errors in a stored procedure during the time of execution. In fact there are about 3800 SQL server error messages, which are maintained in the master catalog's "sysmessages" table. Every error message has its own severity level and it ranges from 0 to 25, depending on how bad the error is.
There are two types of errors you can face when you execute a stored procedure in a SQL server. One is Fatal and another one is nonfatal. Fatal errors normally terminates the execution of the SP and terminates the connection between the SQL server and the client application while nonfatal errors do not.
Here is an example for Fatal error, I am trying to execute a SELECT SQL statement in a table which does not exist.

CREATE PROCEDURE sp_Fatal

SELECT * FROM empDB

PRINT 'Table Does not exist.' 

GO 

As this table doesn't exist the SQL server raises a Fatal Error and the execution of the procedure terminates. So the PRINT statement will not be executed and we will not get the error message 'Table Does not exist'. Instead we get the error message raised by the Server.
Server: Msg 208,Level 16,State 1,Procedure sp_Fatal,Line 3 Invalid object name 'empDB'.

With one example the nonfatal errors can be illustrated. Let us assume that you are trying to Insert NULL value to a filed which is designed a Primary key. This will raise a non fatal error and will allow you to execute the entire procedure.

In three ways you can get the catch the errors in Stored procedures. Using @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL the Server. Let us discuss the one by one.
@@ Error method:
The @@ERROR system function returns 0 if the last procedure executed successfully; if the statement generated an error, @@ERROR returns the error number. The following example explains with an Insert statement,

CREATE PROCEDURE sp_addEmployee

@empId varchar(10),@empName varchar(40),@phone char(12),@address varchar(40) = NULL,

@city varchar(20) = NULL,@state char(2) = NULL,@zip char(5) = NULL

AS

INSERT INTO tblEmployee

(fldEmpId, fldEmpName, fldPhone, fldAddress, fldCity, fldState, fldZip) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)

IF @@ERROR <> 0 

BEGIN

PRINT "An error occurred while adding the new Employee information"

RETURN(99)

END

ELSE

BEGIN

PRINT "The new author information has been loaded"

RETURN(0)

END

GO

In this example the IF...ELSE statements test @@ERROR after an INSERT statement which inserts the employee details in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating the success or failure of the procedure.
Using SP_ADDMESSAGE :
SP_ADDMESSAGE is a system stored procedure used to add a new error message to the sysmessages table. This message could be a custom defined one. The syntax for this stored procedure is,

sp_addmessage [@msgnum =] msg_id, 

[@severity =] severity, 

[@msgtext =] 'msg' 

[, [@lang =] 'language'] 

[, [@with_log =] 'with_log']

[, [@replace =] 'replace']

Here, [@msgnum =] msg_id is the ID of the message; [@severity =] is the severity level of the error (severity is smallint) and value varies from 0-25 as mentioned earlier; [@msgtext =] 'msg' is the text of the error message; [@lang =] 'language' is the language for this message, which helps to display the error message in multiple languages. [@with_log =] is whether the message is to be written to the Microsoft® Windows NT® application log when it occurs, the value will be true or false. [@replace =],If specified as the string REPLACE, an existing error message is overwritten with new message text and severity level.
An example which adds a new error message is,

EXEC sp_addmessage 50001, 16, 

N'Give perscentage value betwwn 1 to 10

Please reexecute with a more appropriate value.'

The RAISERROR method:
Though you can use print statements, RAISERROR is a more powerful statement than PRINT, for returning messages back to applications. In two ways RAISERROR can return messages.
1. Using sp_addmessage a user-defined error message has been added to master.dbo.sysmessages.
2. Using the message string specified in the RAISERROR statement.
The advantages of using RAISERROR over PRINT is it can assign a specific error number, severity, and state. Moreover the error can be logged.

The syntax for RAISERROR method is as follows,

RAISERROR ({msg_id | msg_str}, severity, state

[, argument1 [, argument2]])

[WITH options]

Stored Procedures for ASP, ASP.NET, VB and VB.NET Programmers

ASP.NET: Using SQLite with Enterprise Library 3.1

Share |

Summary of how to use the EntlibContrib SQLite provider with Enterprise Library 3.1 (May 2007) and ASP.NET

Information and download of System.Data.SqLite:
http://sqlite.phxsoftware.com/

Enterprise Library 3.1 (May 2007):
http://www.microsoft.com/downloads/details.aspx?FamilyID=4c557c63-708f-4280-8f0c-637481c31718&displaylang=en

EntLibContrib Project at codeplex:
http://www.codeplex.com/entlibcontrib

I've extolled the virtues of the amazing SQLite database engine a number of times here (just use our Search widget with "SQLITE") so I won't go into much detail about it. Here are a few bullet points, though:

1. Complete ADO.NET 2.0 Implementation
2. Supports the Full and Compact .NET Framework as well as native C/C++
3. Completely portable database files
4. Incredibly fast, faster than most every other embedded database, including Sql Server Mobile
5. Encryption Support
6. Full Text Search
7. Visual Studio 2005 Design-Time Support
8. Single file redistributable under 500kb -- nothing to "install"!
9. Extensive SQL support
10. User-Defined Functions & Collating Sequences, Triggers
11. Full Source Included.  100% Free

You can thank my friend Robert Simpson for all this effort and contribution. In sum, SQLite is very fast, requires no configuration  or installation (only that the assembly be present), and implements most all of the SQL 92 spec. With the ADO.NET Provider, you are dealing with datareaders, DataSets and all the familiar programming tools you are used to. The SQL syntax is a bit of a variant from T-SQL, but the differences are quite minor.
At Codeplex, there is a Enterprise Library "contrib" project with a number of enhancements written by users like you and me. One in particular that caught my attention was the SQLite provider, written by Ken Scott  http://freqken.net/.

The solution here shows how to wire up your Entlib configuration to use the ADO.NET 2.0 System.Data.SqLite engine with Enterprise Library 3.1

First you will need the required EntLib and Contrib assemblies:

Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Common
EnterpriseLibrary.Contrib.Data.SqLite
EnterpriseLibrary.Contrib.Data

Then, you'll set up your configuration, which can be done with the EntLib Configuration Add-on from within Visual Studio:

My sample config looks like this:

<?xml version="1.0"?>

<configuration>

  <configSections>

    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

  </configSections>

  <dataConfiguration defaultDatabase="ConnectionString">

    <providerMappings>

      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Contrib.Data.SqLite, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

        name="System.Data.SqLite" />

    </providerMappings>

  </dataConfiguration>

  <connectionStrings>

    <add name="ConnectionString" connectionString="Data Source=dbpath;New=True;UTF8Encoding=True;Version=3"

      providerName="System.Data.SQLite" />

  </connectionStrings>

  <appSettings>

    <add key="Setting" value="" />

  </appSettings>

  <system.web>  

    <compilation debug="true" />   

    <authentication mode="Windows" />   

  </system.web>

</configuration>

Finally, I've got two sample classes that I ripped right out of the test fixtures from the Contrib project, and massaged to fit this demo:

using System.Configuration;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;

namespace  SQLiteEntLib 

{

    public class TestConfigurationSource

    {

        public static DictionaryConfigurationSource CreateConfigurationSource()

        {

            DictionaryConfigurationSource source = new DictionaryConfigurationSource();

            DatabaseSettings settings = new DatabaseSettings();

            settings.DefaultDatabase = "Quotes";

            ConnectionStringsSection section = new ConnectionStringsSection();

            string dbPath = System.Web.HttpContext.Current.Server.MapPath("quotes.db3");

            section.ConnectionStrings.Add(new ConnectionStringSettings("ConnectionString", @"Data Source=" + dbPath , "System.Data.SQLite"));

            source.Add(DatabaseSettings.SectionName, settings);

            source.Add("connectionStrings", section);

            return source;

        }

    }

}

using System;

using System.Data;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace SQLiteEntLib 

{

    public class SQLiteExecuteDatasetHelper

    {

        private const string queryString = "Select * from quotes where authorname like 'a%'";

        private Database db;

        public DataSet  ExecuteDataSetFromSqlString()

        {

            DatabaseProviderFactory factory = new DatabaseProviderFactory(TestConfigurationSource.CreateConfigurationSource());

            db = factory.Create("ConnectionString");

            DataSet dataSet = db.ExecuteDataSet(CommandType.Text, queryString);

            return dataSet;

        }

    }

}

And that's it! The Database object has most of the same familiar methods that are present for all the other Providers. The only thing you don't have is stored procedures.

However, based on all the "stored procs are evil" diatribe I've listened to and read, that could be a good thing!

Note that I am doing a string Replace on the "dbPath" string with Server.MapPath and the name of the database file to make this more portable for ASP.NET.