Home » VB.Net

Dataset/Database Stored Procedure

I am attempting to create a tier application. Im wondering which approach would be better to take on. Should i have stored procedures on the database and then call these from my Data Access Layer (this is when you right click your dataset and add a table adapter, select existing stored procedures or create the entire query in my Dataset under TableAdapter?

My reason for asking is because my Stored procs are created to insert and update in one command (by using an If statement), but saw the other approach recently.

Any reason for one over the other?



2 Answers Found


Answer 1

This is a difficult question because there as so many choices and many, many opinions on those choices.

Personally, I primarily use stored  procedures and ADO.NET with techniques that I demonstrate here:


I believe that Microsoft's current recommended approach  is to use Entity Framework. You can find more information about it here:


Hope this helps.


Answer 2


Has mostly to do with security. 

Some organizations want to keep the developer as far from the database  as possible. 

Then is using a stored  procedure an option because only those who have SQL Server administrator rights on the SQL server can modify those.

With the exception of an IBM DB2 database server there is not much difference between using dynamic SQL transacts scripts or stored SQL transact scripts. 

Linq to SQL creates for instance completely dynamically SQL transact scripts.

If it are procedures  like you wrote, then I like more stored procedures, but that is only because they are more easy to debug themselves than often with quotes messed dynamic scripts




I am attempting to create a tier application. Im wondering which approach would be better to take on. Should i have stored procedures on the database and then call these from my Data Access Layer (this is when you right click your dataset and add a table adapter, select existing stored procedures or create the entire query in my Dataset under TableAdapter?

My reason for asking is because my Stored procs are created to insert and update in one command (by using an If statement), but saw the other approach recently.

Any reason for one over the other?



Dear frnds,

I am trying to execute a SSIS package that is stored in a SQL Server 2005 database

Want to execute from a stored procedure in same database.

 What commands/operations are necessary ?

I am also having Two parameter.




I am using a procedure as a dataset for a SSRS report and the procedure returns a number of decimal data types.  Standard practice in the T-SQL is isnull([column name],0).  In the report I noticed for one of the columns the data was 0 and yet when I run the procedure with the same paramters the data was not 0 for that same column. 

The dataset tab in the report was returning incorrect values when testing from the report so I know it wasn't a layout issue.

In the procedure I removed the isnull from the column and regenerated the procedure and the report then had the correct non zero numbers.  I put the isnull back  and the report continues to work fine.  I am suspecting a bug in the OLE DB layer and my recompiling somehow fixed the issue (not really a fix if this can be a problem).  Am curious if this has been reported by others or if there is KB article regarding this unwanted behavior. 


Report Designer in 2008 SSRS R2 times out when adding a dataset which calls a long running stored procedure.  I have captured the trace on SQL profiler and I can see the procedure call which takes just over one minute to complete execution.

Is there anyway to increase (looks like 30 seconds is the default) the time out?



Hi I have a Stored procedure whose number of columns will be known only after executing with 2 parameters passed on.

I need to make it as rdlc report in VB.Net. 

i have a form in it and a report viewer dropped in it.

i have created a new dataset and have attached the stored procedure from the DB. No columns is visible in it but the parameters are caught correctly. also when checked for Preview Data, its retrieving all the result columns well.

But i am unable to create a rdlc report as there are no columns displayed in the dataset, which can be dragged and dropped to the table.

How can it be achieved.  Kindly please let me know.

Thanks a Lot in advance !!



I have a stored proc which returns different no. of columns as output based on the input parameter. The issue I am facing is that the SSRS 2008 is always populating the column list of the dataset with the first select list irrespective of the input parameter.

How can I have the SSRS populate the column list based on the input parameter I am passing?



IF @type ='test'





SELECT 1 AS Col1,2 AS Col2






ELSEIF @type ='test1'





SELECT 1 AS Col1,2 AS Col2,3 AS Col3




SSRS always populates the dataset with two columns  viz col1 & col2 even when I pass 'test1' as input parameter.

Thanks for the help in advance.





hey guys, I'm having a problem with calling a stored procedure using a table adapter in my dataset

the stored procedure takes 4 parameters the last two of which are output parameters:

ALTER proc SeConnecter
@compte varchar(50), 
@pass varchar(50),
@TypeCompte VARCHAR(50) out,
@Reponse bit out
 if exists ( select '' from utilisateurs where UserName=@compte and Pass=@pass)
		SET @Reponse=1 
		SELECT @TypeCompte=TypeCompte FROM Utilisateurs
WHERE UserName=@compte and Pass=@pass END else SET @Reponse=0 GO

in my dataset i have the stored rocedure in my 'QueriesTableAdapter', For some reason, when i pass in the parameters for the call, i get ane Error :

"The best overloaded method for Achats.AchatsDataSetTableAdapters.QueriesTableAdapter.SeConnecter(string,string,ref string, ref bool?) has some invalid arguments"

Here's my C# code :

using Achats.ACHATSDataSetTableAdapters;

QueriesTableAdapter ConnexionTA = new QueriesTableAdapter();
Boolean exist = 0;
String TypeCompte="";

am I doing anything wrong? I'm new to C#, never had this sort of problem in VB so I'm quite confused;

thanks a bunch in advance :)




I am developing with VS2010. When I generate a store procedure (DATASET>RIGHT CLICK ADD>SELECT>NEW STORE PROCEDURE) for a data table of a dataset from VS2010, the enviroment generates this:

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectQuery' AND user_name(uid) = 'DOMAIN\USER-LOGIN')

SELECT contract_id, product_id, contract_description FROM dbo.TContracts

The creation of the procedure ends with erros since my schema is dbo.

So far, I work around copying and pasting the store procedure changing the schema and starting again from the beginning: DATASET>RIGHT CLICK ADD>SELECT>FROM EXISTING STORE PROCEDURE.

Is there any way to configure VS2100 to generate the scripts avoinding to prefix the supposed schema of my user.

Many thanks.


Hi All

I have created typed dataset then a drag-drop a table from the data source to my form then VS created all the commends (Insert,Update, Delete) to this table then i change the InsertCommend commend Type from text to stored procedure then i hits F5 but i got an error says


"Procedure or Function 'Insert_Order' expects parameter "@Order_Desc'", which was not supplied." 

so how to solve that?






I have to design a Page Similar to Report Server's Viewer Page where if any Parameter is multivalued and if any Stored Procedure/Query Name is specified in the Report, then a it is internally executed and checkbox dropdown list is displayed to select the values.

Similar to that I have to design an aspx page with the similar behaviour. How to get Stored Procedure Name(if Specified/Query) of any Parameter of a report? So that I can bind my Checkbox list on the aspx page.


Please Help!

PublicClass stat
  DimWithEvents connection As SqlClient.SqlConnection
  Dim connectionString AsStringDim da As SqlClient.SqlDataAdapter
  Dim ds AsNew DataSet
  Dim sql AsStringPrivateSub stat_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
    connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Info_300_DB\SuperMarket_alpha3.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
    connection = New SqlClient.SqlConnection(connectionString)
    da.InsertCommand = New SqlClient.SqlCommand("top5_best_selling_products", connection)
    da.InsertCommand.CommandType = CommandType.StoredProcedure
i'am trying to fill the dataset by calling the stored procedure "top5_best_selling_products" but the connection is not closed so the erros is with the adapter or something please Help !


I have a typed dataset that I populate with rows I get from executing a stored procedure. If the stored procedure returns DBNull, then I don't see the column in the xml response. In the client side if I try to access the field it gives the following exception. I have no clue on how to handle this.

System.Data.StrongTypingException: The value for column 'col'  in table 'tbl' is DBNull.System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.

I tried changing the properties of the column in typed dataset to the following. But no use.

AllowDBNull : True

DefaultValue : (Empty)

NullValue : (Empty)

FYI, I created the datatables in the Typed dataset by dragging the database tables from server explorer.

Thanks in advance.





I am having a stored procedure(SP) with one parameter say SP_Test.

Now in another SP say SP_Test1 i need to execute the above SP and get the row count or result count for some manipulation.

When i use

create proc SP_Test1


    declare @count int

    exec SP_Test

    set @count = (select @@rowcount)

    select @count


i am getting two result set .

1. the result set of SP_Test and

2. The count result

How to get the second result set alone. Please guide me.


Umar Ali


I want to call a stored procedure within my main stored procedure to return a value.

Here is the code snippet:   EXECUTE @ClaimNumber =  [dbo].[uspGetNextNumber] 'BRP_INT' , 'ClaimHeader', 'ClaimNumber', 'dlivelsb'

When I run uspGetNextNumber by itself thru debugger, the proc returns the value. When I run the proc inside my stored procedure, it returns zero.

Any thoughts on why this is not working?



I have store procedure need to call another store procedure to return value.

Create PROCEDURE Testone


        exec TestTwo   --return value


How to get the return value when value from second store procedure.

As always, your help is highly appreciated.


Hello people,

When I am trying to call a function I made from a stored procedure of my creation as well I am getting:

Running [dbo].[DeleteSetByTime].

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.

No rows affected.

(0 row(s) returned)


Finished running [dbo].[DeleteSetByTime].

This is my function:






RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'

This is my stored procedure:






SET @TTL = dbo.TTLValue()

DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)


CreatedTime is a datetime column and TTL is an integer column.

I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.

Your help is much appreciated.


Is it possible to use a stored procedure to create a bunch of other stored procedures?  When the user creates a new database, I need to create a bunch of stored procedures and I would like to just run a stored procedure on the new database that creates all the needed stored procedures.  I tried just putting the CREATE PROCEDURE syntax inside a stored procedure and got some errors.  I imagine this can be done using dynamic sql and the EXECUTE function but I was hoping there was a better way.




Hello. I am new to the forum system... go easy on me :)

I am working in SQL Server Management Studio 2008. I have a stored procedure, say st_alpha and a second stored procedure, say st_beta . If I execute st_alpha , and within it, st_beta is executed, then st_beta writes to a log, is there a way to write in the log which stored procedure executed st_beta ? St_alpha could be any stored procedure in our system, st_beta on the other hand, is the same stored procedure that could be executed from within any st_alpha. I would just like to write a record to the log from st_beta as to which stored procedure executed st_beta without having to go through all of the st_alpha procedures and add a description variable that is passed to st_beta.


Any ideas?


hi experts,

  In control flow

Execute SQL Task --> executes  the stored procedure and on completion pointer return to Script Task --> which will check the returnvalue of the stored procedure and if return value is not zero then display an error message using message box.

What i want is how do i write the message to a flat file instead of displaying as message box. plz help me with sample code.


My code for display message box in script task

  int intRetun = Convert.ToInt32(Dts.Variables["intReturnVal"].Value);
            string strErrorMessage = Dts.Variables["strSqlErrorMsg"].Value.ToString();


            if (intRetun != 0)
                MessageBox.Show(" Stored procedure return an error. \n" + "Error Message : " + strErrorMessage + "\n" + "Error Code : " + intRetun.ToString(), "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error);







I created a database (.mdf) file.  I added data to it after running the program in debug mode. When I closed it and opened it up again, the data I got was stored and loaded again.

I closed my whole Visual Basic Express program. When I opened it up again, all the data had disappeared. 

Can you tell me what I am doing wrong with the program? I can't get queries to work either. It worked when I used the NOrthwind dataset this past weekend but it is not working when I use my own or empty dataset.






<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure