.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Selecting VIEW dynamically from a stored procedure

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
What's the best way to dynamically reference a view in a sproc? I want to pass a View Name into a stored procedure, but I'm guessing this is only possible with a table in SQL Server 2008. Is the only way to do this through dynamic SQL (passing in the View name as a string, and executing the query as a built string)?. My concern is that if I use Dynamic SQL, what performance gain I might get by using a view may be undone by using Exec('string').   Any thoughts?   Thanks Steve

View Complete Post

More Related Resource Links

selecting between query and stored procedure

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1033506322; mso-list-type:hybrid; mso-list-template-ids:369124590 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> What would prefer to pull data out from sources stored procedure or query? and why?

Stored Procedure failed after underlying view got changed


Yesterday I came across a situation where after an underlying view was altered to add a new column(view is a simple select statement) the stored procedure using that view failed giving some conversion error. the stored procedure does not use * but uses explicit column names. 

even after recompiling the SP with sp_recompile the issue was not solved.

we then dropped stored procedure and recreated it (using same script). And it ran sucessfully.

Any specific reasons for this behaviour. I have an understanding that stored procedure should have got recompiled automatically.


update two tables from single grid view using stored procedure PLZ help


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace INV_DN
    public partial class Form1 : Form
        SqlConnection con;
        SqlDataAdapter da1;
        SqlCommand cmd;
        //  SqlCommandBuilder cb;
        DataSet ds;
        public Form1()

        private void Form1_Load(object sender, EventArgs e)
            con = new SqlConnection("User Id=sa;Password=123;database=amar");
            cmd = new SqlCommand("getdata", con);

Selecting Oracle Stored Procedure in SSRS Crashes Visual Studio 2005 IDE


Problem Conditions

I have a very simple Oracle (11g) Stored Procedure that is declared like so:

* FROM MyTable;

This compiles correctly. The cursor contains col1, col2 and col3.

In SSRS, i have a Shared Data Source that uses Oracle OLEDB Provider for Oracle 11g:


Dynamically assign stored procedure


I have a report that depending on what parameters the users selects can use one of three different sql stored procs. I figured out how to call the correct procedure by creating an expression for the data set, but the problem I have is the number of parameters each sp uses. One needs 7 parameters and one needs only 6 and the third only needs 5.

When I run the report and call the sp that uses 7 parameters, eveything works fine, but I get a 'too many parameters' error when I try to use the sp that needs only 5 or 6 parameters.

Is there any way to dynamically pass parameters to the sp?





Getting multiple rows from oracle stored procedure and binding it to grid view



To  get this stuff you need to create a Ref Cursor to return recordset.

             create or replace PACKAGE Types AS

            TYPE cursor_type IS REF CURSOR;

            END Types;

Now create a stored procedure to return multiple rows .

           create or replace

           PROCEDURE getAllCity(p_recordset OUT types.cursor_type) AS


           OPEN p_recordset FOR


stored procedure is not getting invoked on update command of grid view


Hello everybody!

In my project I am using a grid view to display data from multiple tables and I want to update that data on clicking edit button.

For selecting data I am using custom select statement, but for updating I have created a stored procedure which should update two different tables.


But when I click on update , it's not executing the stored procedure.


here is the code snippet of sqldatasource:-

<asp:SqlDataSource ID="SqlDataSource1" runat="server"  UpdateCommand="editEmployeeInfo" UpdateCommandType="StoredProcedure">


                    <asp:Parameter Name="empId" Type="Int32" />

                    <asp:Parameter Name="name" Type="String" />

                    <asp:Parameter Name="address1" Type="String" />

                    <asp:Parameter Name="address2" Type="String" />

                    <asp:Parameter Name="telNo" Type="String" />


Open stored procedure or view in excel



My customer wishes to view some SQL data in Excel.

I can write a stored procedure or even a view (no parameters) to query the data they require.

Would they be able to just open the view/call the stored procedure from within Excel - if so from where? Or would I need to code a VBA button on the spreadsheet or other solution?

Many thanks in advance

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Stored procedure generator?


Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



.NET Reflection: Dynamically Bind Your Data Layer to Stored Procedures and SQL Commands Using .NET M


One major inconvenience of using SQL stored procedures from code is having to match type information. You have to write long wrapper functions around the procedures just to expose the data types. In the .NET Framework, however, the reflection services of the System.Reflection namespace allow you to query rich metadata that describe data types. This article explains how to use reflection in .NET to end your stored procedure nightmares. The author creates four custom classes, including one that generates a SQLCommand object based on the metadata of a method. Using this library, you'll be on your way to automating the generation of stored procedures.

Atif Aziz

MSDN Magazine August 2002

Call SQL Stored Procedure Asyncronously



I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.

Best regards,


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend