.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

Problems while creating the Stored Procedures with Concatination

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi I am creating one Stored Procedure where want to use concatination in that. For that I had written the query but I am unable to do it, so I need help for this. My SQL query is: SET QUOTED_IDENTIFIER ON CREATE PROCEDURE Team @Int VARCHAR(5), @str VARCHAR(150), AS SELECT * FROM Emp WHERE ID = @Int + '@str' Go In @str is we are passing the value at run time (like @str =AND NAME = 'Test') Actuall quey is: SELECT * FROM Emp WHERE ID = 1 AND NAME = 'Test' Thanks in advance Amit

View Complete Post

More Related Resource Links

Issue while creating a BCS entity in visual studio for SQL stored procedures


Hi Everyone,

                  I have an issue while creating an entity in Visual studio for SQL stored procedures.I have two input parameters startdate and enddate as datetime datatypes in the stored procedures.I have added two input parameters startdate and enddate in the Readlist and ReadItem methods and created two filter descriptors startdate and enddate of "comparison" type and related these to their respective input parameters.

                 In my readlist and readitem methods I have used LINQ TO SQL query to query the SP.When I debug the program,I am receiving an error message-->remote procedure cannot be debugged.Also my parameters are not being passed to the SP.Any help is appreciated..also if any one has step by step approach,that helps me a lot.

Setting up publisher not creating msIns/msUpdt/msDel stored procedures


Hi All,

I have added a replication (Transaction) :


-- Adding the transactional publication
exec sp_addpublication
    @publication = N'XXX_PUSH',
    @description = N'Transactional publication of database.',
    @sync_method = N'concurrent',
    @retention = 0,
    @allow_push = N'true',
    @allow_pull = N'true',
    @allow_anonymous = N'true',
    @enabled_for_internet = N'false',
    @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false',
    @ftp_port = 21,
    @ftp_login = N'anonymous',
    @allow_subscription_copy = N'false',
    @add_to_active_directory = N'false',
    @repl_freq = N'continuous',
    @status = N'active',
    @independent_agent = N'true',
    @immediate_sync = N'true',
    @allow_sync_tran = N'false',
    @autogen_sync_procs = N'true',
    @allow_queued_tran = N'false',
    @allow_dts = N'false',

Transact-SQL Built-In Stored Procedures

To assist you with managing Microsoft SQL Server databases, Transact-SQL provides many built-in stored procedures.

Renaming an Object

Sending Email

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.

SqlDataSource how to change the stored procedures at run time



Is it possible to change the select, insert and update stored procedure of an SqlDataSource at run time. I don't mean to change the definition of the stored procedure, but I mean to change the assignment, i.e. To change the select stored procedure from "selectSP1" to "selectSP2" at run time. Is it possible?



Test Run: Testing SQL Stored Procedures Using LINQ


Language Integrated Query makes lots of things easier. Here we put LINQ, or more specifically the LINQ to SQL provider, to use testing SQL stored procedures.

Dr. James McCaffrey

MSDN Magazine April 2008

Advanced T-SQL: Automate the Generation of Stored Procedures for Your Database


Design-time automation makes coding faster and ensures that all the procedures generated use the same naming conventions and structure. In an effort to improve their coding efficiency in a large SQL project, the authors wrote a set of design-time stored procedures that generate run-time stored procedures, and have used them in project after project ever since. Recently, the authors updated their procedures to make use of SQL Server 2000 features, including user-defined functions. This article covers the creation and execution of these dynamic T-SQL scripts to automate the coding of common database stored procedures.

Peter W. DeBetta and J. Byer Hill

MSDN Magazine April 2003

.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

SQL and XML: Use XML to Invoke and Return Stored Procedures Over the Web


Front-end developers frequently want to add functionality to the presentation tier of an n-tier architecture, but such requests can require changes on all tiers just to get the data and present it. This process can be made easier and more flexible by using SQL Server stored procedures to automate the delivery of data in XML format from the database to the front-end components. In the component presented here, stored procedures are invoked by XML strings, XML is returned, converted using XSL, and presented to the client in HTML. The technique supports rapid changes yet doesn't sacrifice the n-tier approach. This approach can be used with either SQL Server 7.0 or SQL Server 2000.

Dave Grundgeiger, Anson Goldade, and Varon Fugman

MSDN Magazine August 2001

EF 4: Function Import problem with Stored Procedures


I have the following SP which includes a temp table:

 DECLARE @pos int

--create table to hold parsed values
CREATE TABLE #list (val varchar

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?

Stored Procedures & SqlDataAdapter not updating a calendar control


Hi all,

I have a database linked to a calendar. I had a peice of code that retrieved dates from a database and populated the calendar. I wanted to tidy up my code using stored procedures (they seem neater & i assume there's a benefit somewhere). so i came up with this, however it's not working. The calendar displays, but it's not being populated with the database entries. The SqlCommand & SqlParameter I have working elsewhere in my code; as with the SqlDataAdapter (which also worked before i tried to conver to a stored procedure!) but combining these three (SqlCommand, SqlParameter & SqlDataAdapter) seem to be causing me problems.

It's probably something stupid but if someone could take a look and point me in the right direction, I'd be very grateful.


Public Sub dbConnection()
        dbConnString = ConfigurationManager.ConnectionStrings("connString").ConnectionString
        sqlConn = New SqlConnection(dbConnString)
    End Sub

Public Sub EquipmentDDLSelection()

        Dim dbCMD As New SqlCommand("PopulateCalendar", sqlConn)
        dbCMD.CommandType = CommandType.StoredProcedure

        Dim parameterBookedEvents As New SqlParameter("@bookedEvents", SqlDbType.VarChar, 50)

Problems connecting stored procedure to Crystal Reports


I am working on updating a reporting system that uses Crystal Reports.  All of the 250+ reports were created in CR 8 and I have been opening up the old files in Visual Studio 2005 and updating the database location.  All I have had to do with all connections to views and even a couple of stored procedures is create a new connection to the database and then update the old report's datasource location.  The reason I need to update the location is so that the .NET application will use the correct driver.  Everything has worked fine up until I got to one stored procedure.  The old version of the report works perfectly with the stored procedure, but when I try to run the report using VS 2005 I keep getting errors.

First I connected to the database using the Oracle OLE DB provider, then updated the location of the stored procedure in the report.  It then prompts me for the two parameter values for the stored procedure, both of which I keep as NULL so that the report parameters will be passed to the procedure.  Then I get the following error:

Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'

The proce

Problems creating a BDC file that use a unique select statment


I'm trying to build an BDC file that will pull information back from two tables in an Oracle database, I can import it without any errors, but then it tells me I do not have a filter when I try and select an item.  When I add a filter I get an error when I import the file.  The error I get is the following.
"Application definition import failed. The following error occurred: The IdentifierName Attribute of the TypeDescriptor named 'TestDate' of the Parameter named ':TestDate' of the Method named 'Get_TestTwelve_List' of the Entity named 'TestTwelve' does not match any of the defined Identifiers for the Entity. Error was encountered at or just before Line: '133' and Position: '16'."

Here is the section of

      <Property Name="RdbCommandText" Type="System.String"><![CDATA[

Creating DLL file for Extended Stored procedure in C#

Hi All,   Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?   I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?   Any pointers to this will be highly appreciated.   Thanks Sid
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