.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

Calling stored procedure from funciton in sql server 2005

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Is there any way to call stored procedure from funciton in sql server 2005?

If it is possible, please give some examples.



RAHUL Dot Net Developer

View Complete Post

More Related Resource Links

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......


@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS









FROM [ReportQueries]


[UserID] = @UserID

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net


Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me

Warm Regards


CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005


I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another


I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.


SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea


I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

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.

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Calling Stored Procedure in asp.net

Hi! I am trying to insert a row via button event that should call an Oracle Stored Procedure, but the insert never happens. Please see code below: C#-code {code} using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OracleClient; using System.Data;   public partial class new_user : System.Web.UI.Page {     protected void button1_click(object sender, EventArgs e)     {         OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();           b.UserID = "Test";         b.Password = "xxx";         b.DataSource = "ora11";           OracleConnection con = new OracleConnection(b.ToString());           try         {             con.Open();             OracleCommand spcmd = new OracleCommand();               spcmd.CommandType = CommandType.StoredProcedure;     &nbs

JAVA calling SQL Server Procedure

Reply Contact I have a JAVA developer asking me this question.  I am not sure how to answer him.  I am not sure I understand his question.  He is using JAVA to call my procedure. "Is there some conclusive signal returned by the the SP result that would letus know whether it succeeded or failed? I'm currently looking for the word "exception" in the error message, but that hardly seems conclusive to me." lcerni

SQL Server 2008 Stored Procedure output parameter

I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

Calling another Stored Procedure within a stored procedure

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?

SQL Server 2005 Stored Procedures

  I had written a stored proc. to get date & time in Std. format  dd/mm/yy hh:mm:ss AM/PM. I wanted that  when no value is given to the stored procedure it should produce current date & time in the above std. format & when some date is given , it should display that date in above std. format. The stored proc. is as follows: set  ANSI_NULLS ON set  QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GETDATETIME] @DATE DATETIME = NULL, @NEWDATE VARCHAR(MAX) OUTPUT AS BEGIN --DECLARE @TEMPDATE DATETIME; --SET @TEMPDATE=NULL; --SET @TEMPDATE=; IF (@DATE IS NULL) BEGIN SET @DATE=GETDATE(); SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); GOTO L1; END SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); -- Declare the return variable here --DECLARE @Result int -- Add the T-SQL statements to compute the return value here --SELECT @Result = @p1 -- Return the result of the function --SET @NEWDATE2=CONVERT(VARCHAR(MAX),@NEWDATE); L1: RETURN CONVERT(VARCHAR(MAX),@N

BIDS 2005 cannot find Stored Procedure

I have read how to do this in multiple places but my report is not able to use a stored procedure for retrieving data. When I select command type 'stored procedure' to populate my dataset, and then switch to the GUI query designer, I only see a partial list of system SPs, not my user defined SPs. My SP does return a single dataset.If I try to manually use the SP, with or without doublequotes and EXEC, the report designer simply tells me that the SP cannot be found.

Calling AS400 Stored Procedure from ASP.net C# Coding

Hi, I have a req in which I have to call an AS400 stored procedure.The stored has an input and output parameter. Can any body help me.I am new to this technology. Please help me its urgent Thanks, A2H  

CLR stored procedures asssemblies working in sql server 2005 but giving issues over sql server 2008

Hi I developed CLR stored procedures assemblies using VS2005 and deployed in sql server 2005 are working fine. Currently we are migrating to sql server 2008. The same CLR assemblies are not working in 2008 version and raising following error. Here i created CLR assemblies in sql server 2008 using 'sa' user account which is having db_owner permission over 'mydatabase'. I am getting an error message like "A .NET Framework error occurred during execution of user-defined routine or aggregate". In sql logs showing messages like as -- Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\Windows\Microsoft.NET\Framework\v2.0.50727\. -- AppDomain 2 (mydatabase.dbo[runtime].1) created.   Please let me know what may be the cause for this type of issues. Is there any specific backward compatibility settings has to be done over sql server 2008 or sql server 2008 R2 while using 2005 CLR routines?   Thanks in advance.  
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