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


Top 5 Contributors of the Month
Jean Paul
SP
satyapriyanayak
Ashutosh Jha
Simon Robert VoIP
Post New Web Links

Calling a stored procedure or function from another stored procedure

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server
 

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)

@RETURN_VALUE =

Finished running [dbo].[DeleteSetByTime].

This is my function:

ALTER FUNCTION dbo.TTLValue

(

)

RETURNS TABLE

AS

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

This is my stored procedure:

ALTER PROCEDURE dbo.DeleteSetByTime

AS

BEGIN



View Complete Post


More Related Resource Links

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

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?

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  

Cannot call Table Valued Function from Dynamic stored procedure statement

  
Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Entity Model- Add Function Import - selected stored procedure returns no columns

  
I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

Stored procedure and table value function

  

I have a function called F_TBL_VALS_FROM_STRING this function returns a table and uses '|' as a delimiter to distinguish between values.
this function is used in a procedure (bellow), while I've tested this function and it returns the requested values, using it in this stored procedure does not work.

ALTER PROCEDURE dbo.mytestproc
  (
    @doorList     varchar(4000),
    --From Date
    @FromDateYear   int,
    @FromDateMonth  int,
    @FromDateDay   int,
    --To Date
    @ToDateYear    int,
    @ToDateMonth   int,
    @ToDateDay    int,
    --From Time
    @FromTimeHour   int,
    @FromTimeMinute  int,
    @FromTimeSecond  int,
    --To Time
    @ToTimeHour     int,
    @ToTimeMinute   int,
    @ToTimeSecond   

calling stored procedure with in select statement

  

Hi,

        Any one pls tell me how to call the stored procedure in SQL server


Calling Oracle stored procedure

  

I am calling oracle procedure in SSIS script task, This stored procedure retrun value as output, I tried OLEDB it not working, Now i am trying using ADO.net connection, while executing task i am getting following error msg

 

[Execute SQL Task] Error: Executing the query "exec ssisproctes" failed with the following error: "Unable to cast object of type 'System.Data.OracleClient.OracleConnection' to type 'System.Data.SqlClient.SqlConnection'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am able to test connection in connection manager?

any help to resolve this issue, or is there any other method to call oracle procedure and get output value in a SSIS variable

Thanks

V

 


JDBC - calling stored procedure always returns 1 result set

  

I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave


Very funny video - What's your Metaphor?

function error regarding Stored procedure

  
Hello: 

I have defined a boolean function with is supposed to trigger a stored procedure. 

However when stepping to the code, I am receiving an error that says " Procedure or Function 'proc_admin_updateTraining' expects parameter '@Pin_Code', which was not supplied."

However this parameter is supplied within my code. Any help will be greatly appreciated.

I have implemented a similar function 6 other times and it's working for those. This is the only one returning the error.

public bool updateTraining()
    {

        int UpdatedRep = 0;
        SqlConnection sqlcon = new SqlConnection(connstring);
        SqlCommand sqlcom = new SqlCommand("proc_admin_updateTraining", sqlcon);
        SqlParameter ParPageID,ParWebLogin, ParPinCode, ParTrainerTelephone, ParTrainerName, ParTrainerContactEmail, ParOutlookApptBody, ParOutlookApptPriority,
        ParOutlookApptSubject, ParOutlookApptLocation, ParSendOutlookAppt;
        
        
        ParPageID = sqlcom.Parameters.Add("@web_site_page_id", SqlDbType.Int);
        ParWebLogin = sqlcom.Parameters.Add("@Web_Login", Microsoft.SqlServer.Management.Smo.SqlDataType.VarCharMax);
        ParPinCode = sqlcom.Pa

TableAdapter/DataSet calling Stored Procedure

  

Hi!

I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.

Now I want to just call a stored procedure from the database instead of calling a command like "select * from table".  But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call a procedure?


Calling stored procedure from funciton in sql server 2005

  

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

If it is possible, please give some examples.

Thanks.

 


RAHUL Dot Net Developer

Difference between [Stored Procedure / Trigger & Function]

  

 

 

hi

 

can any give me what are the major difference between [Stored Procedure / Trigger  & Function] with example

 

kinds regards

 


Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
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.

Solution
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.
Categories: 
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