Calling A Stored Procedure Or Function From Another Stored Procedure
Mar 2, 2007
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
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
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.
View 6 Replies
ADVERTISEMENT
Oct 26, 2007
Hello all,
I'm trying to construct a select statement in a stored procedure that filters based on the returned values of a number of functions. My function works fine, but when I try to call the function from the stored procedure I get an error.
I'm going to try explain the thought process behind what I'm doing. Hope I make enough sense.The purpose of the stored procedure is to perform a wildcard search on a tool. The tool contains a number of FK that link to different tables (e.g., manufacturer, vendor). So I'm creating functions that also search the manufacturer and vendor and return the matching IDs.
Example of tool SELECT statement:SELECT tool_number, tool_description
FROM tool
WHERE tool_manufacturer IN (UDFmanufacturer_SearchName(@search_string)
This gives me an error:'UDFmanufacturer_SearchName' is not a recognized built-in function name.
Function code (removed some wrapping code for simplicity):SELECT manufacturer_id
FROM manufacturer
WHERE manufacturer_name LIKE '%' + @search_string + '%'These statements both work if I run a independent query: SELECT *
FROM UDFmanufacturer_SearchName('mol') SELECT *
FROM tool
WHERE tool_manufacturer IN (SELECT *FROM UDFmanufacturer_SearchName('mol')) This code fails:SELECT *
FROM ato_tool
WHERE ato_tool_manufacturer IN (UDFmanufacturer_SearchName('mol'))
I'm stuck. I haven't been able to find anything that shows me where I'm going wrong. Any thoughts or suggestions are appreciated. Thanks,Jay
View 4 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Sep 29, 2007
I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.
This is code in Form.asp
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)
'When it returns can check it here........
if ab_HasAccessToClient result is a 1 then
'boolean would be 1 so show panel
Else
'boolean would be 0 so show access denied
'allow them to go back to the original page.
end if
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
ON SQL SERVER: Stored Procedure
----------------------------------------------------------
--------------------------------
rx_sp_HasAccessToClient
CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]
@EmployeeID INT,
@ClientSSN varchar(50),
@ReturnBitValue = OUTPUT
/*
' Parameters here passed via call from Form.asp - not sure what is passed
yet.
*/
AS
set nocount on
/*
Written by Mike Belcher 9/27/2007 for Form.asp
'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.
'Gets return bit and passes that back to the call from Form.asp
*/
GO
----------------------------------------------------------
--------------------------------
ON SQL SERVER: User-Defined Function
----------------------------------------------------------
--------------------------------
ab_HasAccessToClient
CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))
@ClientSSN varchar(50),
@EmployeeID,
@ReturnBitValue = OUTPUT
AS
SELECT 1
FROM tblEmployeesClients ec
INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN
INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName
WHERE e.EmployeeID= @EmployeeID
AND c.InActiveClient=0
AND c.ClientSSN = @ClientSSN
'Some Code here to save result bit ..
RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient
----------------------------------------------------------
--------------------------------
</FLOW WITH CODE AND FUNCTIONS :>
View 5 Replies
View Related
Sep 29, 2007
I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.
This is code in Form.asp
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)
'When it returns can check it here........
if ab_HasAccessToClient result is a 1 then
'boolean would be 1 so show panel
Else
'boolean would be 0 so show access denied
'allow them to go back to the original page.
end if
'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>
ON SQL SERVER: Stored Procedure
----------------------------------------------------------
--------------------------------
rx_sp_HasAccessToClient
CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]
@EmployeeID INT,
@ClientSSN varchar(50),
@ReturnBitValue = OUTPUT
/*
' Parameters here passed via call from Form.asp - not sure what is passed
yet.
*/
AS
set nocount on
/*
Written by Mike Belcher 9/27/2007 for Form.asp
'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.
'Gets return bit and passes that back to the call from Form.asp
*/
GO
----------------------------------------------------------
--------------------------------
ON SQL SERVER: User-Defined Function
----------------------------------------------------------
--------------------------------
ab_HasAccessToClient
CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))
@ClientSSN varchar(50),
@EmployeeID,
@ReturnBitValue = OUTPUT
AS
SELECT 1
FROM tblEmployeesClients ec
INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN
INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName
WHERE e.EmployeeID= @EmployeeID
AND c.InActiveClient=0
AND c.ClientSSN = @ClientSSN
'Some Code here to save result bit ..
RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient
----------------------------------------------------------
--------------------------------
</FLOW WITH CODE AND FUNCTIONS :>
View 1 Replies
View Related
Jul 30, 2007
When i call user defined function from stored procedure, i am getting an error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.
why this error is happening?.
i am calling this way... dbo.MyFunction(param1,param2)
View 6 Replies
View Related
Apr 6, 2007
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
List<string> myList = new List<string> ();
conn.Open ();
SqlCommand command = conn.CreateCommand ();
command.CommandText = "GetObject";
command.Parameters.AddWithValue ( "@map", "Item" );
command.CommandType = System.Data.CommandType.StoredProcedure;
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Code SnippetEXEC GetObject 'Item'
Is there some sorf of trick I am missing?
Thank you!
View 3 Replies
View Related
Oct 10, 2006
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies
View Related
Feb 4, 2008
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Sep 12, 2006
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End
oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;
gdvCategories.DataBind(); <<--- Error occured here
View 1 Replies
View Related
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Mar 26, 2007
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
Thanks Nickdel68
View 5 Replies
View Related
Jun 22, 2006
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question ..
1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why?
2) I try to move a file .. how do I find out if the moving is completed successfully?
-- MOVE FILES SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD
Thanks for your reply in advance.
View 3 Replies
View Related
Jul 13, 2005
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath
View 1 Replies
View Related
Apr 11, 2002
I need to call a stored proc(SPB) from within a stored proc(SPA). I do not want to pass SPB's resultset out of SPA, but I want to do a select on it from within SPA and return a different result set. Below is an example of how I would like to do this, please let me know of any ideas you might have.
Thanks,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
declare @viewID uniqueidentifier
declare @userID uniqueidentifier
declare @ProjectID uniqueidentifier
set @userID = (select memberGUID from member where username = 'karlb')
set @viewID =
(select pk_viewDefinitionId viewDefinitionId
From ViewDefinition vd
Join ViewDefinitionGlobal vdg on vd.fk_viewDefinitionGlobalId = vdg.pk_viewDefinitionGlobalId
and vdg.viewDefinitionGlobalName = 'Admin - Organizational Units'
and vd.fk_projectId = (select projectguid from project where projectname = 'Cleveland IAS Test'))
/*
Here is the point where I have the problem
I think you can see what I would like to do, but I need
to take only distinct rows and only certain columns
*/
Select @ProjectID AS fk_projectid,
distinct(itemID),
itemName,
seqNum
from(exec stoActionSelectIronCladTree @viewID, @userID, null,null,null,null,null,null)
View 1 Replies
View Related
Apr 28, 2008
Hi folks,
I created a stored procedure called td_calculated initially for testing in hopes to later create a function from it. Problem is I'm using a few temp tables which I later found out can't be used inside of a function.
However I learned that it is possible to call a stored procedure within another one. I'm just a little fuzzy on the syntax
Right now td_calculated accepts @case (int) parameter and just displays a single value @td(int). I'd like to know how to allow @td to be passed to another procedure and how to call this one.
Can this be done within a column similar to a scalar function?
View 8 Replies
View Related
Nov 30, 2007
I have a simple stored procedure that inserts a record into a table that logs changes. My question is, if this procedure fails for whatever reason will the transaction be rolled back here and in the calling procedure? Thanks.
ALTER PROCEDURE dbo.cdds_User_Profile_Log_Insert
@UserId uniqueidentifier,
@fieldNames varchar(200),
@fieldValues varchar(200)
AS
BEGIN TRANSACTION
INSERT INTO [cdds_User_Profile_Log](Date_Time,UserId,FieldNames,FieldValues)
VALUES (GETDATE(), @UserId,@fieldNames,@fieldValues)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION Block
View 3 Replies
View Related
Mar 23, 2007
Hi, i've had this query method:
34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)35 {36 SqlConnection oConn = new SqlConnection(_connectionString);37 string strSql = "Insert into LogDetail (LogID, CategorieID, Inhoud)";38 strSql += "values(@logID, @categorieID, @inhoud)";39 SqlCommand oCmd = new SqlCommand(strSql, oConn);40 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;41 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;42 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;43 44 try45 {46 oConn.Open();47 int rowsAffected = oCmd.ExecuteNonQuery();48 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");49 oCmd.CommandText = "select @@IDENTITY";50 oCmd.Parameters.Clear();51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();52 }53 catch (Exception ex)54 {55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);56 }57 finally58 {59 if (oConn.State == ConnectionState.Open) oConn.Close();60 }61 }
which i've converted to a stored procedure: 1 ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]
2 -- Add the parameters for the stored procedure here
3 @dagverslagdetailID int,
4 @logID int,
5 @categorieID int,
6 @inhoud varchar(100)
7 AS
8 BEGIN
9 -- SET NOCOUNT ON added to prevent extra result sets from
10 -- interfering with SELECT statements.
11 SET NOCOUNT ON;
12 SET @dagverslagdetailID = SCOPE_IDENTITY()
13
14 -- Insert statements for procedure here
15 BEGIN TRANSACTION
16 INSERT LogDetail (LogID, CategorieID, Inhoud)
17 VALUES(@logID, @categorieID, @inhoud)
18 COMMIT TRANSACTION
19 END
Now i would like to call that stored procedure in my previous method, so i've changed it to this:
1 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslagDetail";
5 strSql += "values(@logID, @categorieID, @inhoud)";
6 SqlCommand oCmd = new SqlCommand(strSql, oConn);
7 oCmd.CommandType = CommandType.StoredProcedure;
8 oCmd.Parameters.Add(new SqlParameter("@logID", SqlDbType.Int)).Value = logID;
9 oCmd.Parameters.Add(new SqlParameter("@categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;
10 oCmd.Parameters.Add(new SqlParameter("@inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;
11
12 try
13 {
14 oConn.Open();
15 int rowsAffected = oCmd.ExecuteNonQuery();
16 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek detail");
17 oCmd.CommandText = "select @@IDENTITY";
18 oCmd.Parameters.Clear();
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
20 }
21 catch (Exception ex)
22 {
23 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);
24 }
25 finally
26 {
27 if (oConn.State == ConnectionState.Open) oConn.Close();
28 }
29 }
Do i still need the lines 17 oCmd.CommandText = "select @@IDENTITY";
19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
Because i've declared the identity in my stored procedure
View 1 Replies
View Related
Apr 8, 2008
Hi, I have a stored procedure, and it is expecting an output. If I declared the passing varaible as ref, it compiles fine, but it is not returning any value. If I pass the varaible as out, and add the paramater
MyComm.Parameters.Add(new SqlParameter("@ReturnValue", returnValue)); it gives the following error.
Compiler Error Message: CS0269: Use of unassigned out parameter 'quoteID'.
And if I don't supply the previous statement, the following error occurs.
System.Data.SqlClient.SqlException: Procedure 'CreateData' expects parameter '@ReturnValue', which was not supplied.
How Can I fix this? thanks.
View 2 Replies
View Related
Dec 15, 2003
I have a stored procedure that calls a DTS package to grab a text file that has been uploaded to the server and merge it with a table on the database. The DTS package works woderfully in SQL, as does the the file upload. The problem arrises when I create a stored procedure to run the DTS package. I know that you have to shell out and do a command line on the SQL server (and I think that I got the syntax correct) but its calling the Stored Procedure in the ASP.NET app that is causing me hardship. Here is the code that I have so far:
Stored Procedure:
CREATE PROCEDURE spSampleData AS exec master..xp_cmdshell 'dtsrun /SZEUSsqlServer113 /NdtsPackage /UuserID /Ppassword'
GO
VB to run DTS:
Dim myCommand As SqlCommand
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "spSampleData"
myCommand.ExecuteNonQuery()
I'm not sure what I am doing wrong but any help would be great.
Thanks!
View 3 Replies
View Related
Feb 6, 2004
I am trying to set up a call to a Stored Procedure to do an Insert. Here is my code snippet:
<%@ Page Language="VB" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data.SqlClient" %>
.
.
.
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdInsert as New SQLCommand("AdminUser_Insert", loConn)
cmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = cmdInsert
cmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "Account_Number"))
cmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(New SqlParameter("@UserID", SqlDbType.varchar, 50, "UserID"))
cmdInsert.Parameters("@UserID").Value = Request("UserID")
cmdInsert.Parameters.Add(New SqlParameter("@Password", SqlDbType.varchar, 32, "Password"))
cmdInsert.Parameters("@Password").Value = Request("Password")
cmdInsert.Parameters.Add(New SqlParameter("@First_Name", SqlDbType.varchar, 32, "First_Name"))
cmdInsert.Parameters("@First_Name").Value = Request("FirstName")
cmdInsert.Parameters.Add(New SqlParameter("@Middle_Name", SqlDbType.varchar, 32, "Middle_Name"))
cmdInsert.Parameters("@Middle_Name").Value = Request("MiddleName")
cmdInsert.Parameters.Add(New SqlParameter("@Last_Name", SqlDbType.varchar, 32, "Last_Name"))
cmdInsert.Parameters("@Last_Name").Value = Request("LastName")
loConn.Open()
command.ExecuteNonQuery()
loConn.Close()
I get the following error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30451: Name 'CommandType' is not declared.
This error happens on the line: cmdInsert.CommandType = CommandType.StoredProcedure
Any help would be appreciated,
Greg
View 2 Replies
View Related
May 22, 2000
Hello everybody,
How can I call DTS from stored procedure, any help pls.
PS: I appreciate if you please give me an example.
Thank you....
View 2 Replies
View Related
Jan 19, 2001
I'm trying to call a job from a stored procedure.
To do so, I've found that sp_start_job is doing just that.
My problem is that the sp_start_job is not in my master
database stored procedures and I don't know how to add it.
I'm working with SQL Server 7 Enterprise without the SP.
Am I looking for the right thing ? (sp_start_job)
Where can I find it ?
View 1 Replies
View Related
Nov 3, 2005
Hey, I have a parent SP, and within that parent I want to call a a child what is the code to call that child procedure? or teh easiest way to make that happen?
View 1 Replies
View Related
Jan 30, 2004
Can someone tell me a straightforward way to call a VB app (that accepts command line arguments) from a stored procedure.
I have got it to work by using xp_cmdshell, but in practice, the security constraints here prevent using this. Our DBAs don't want to set the proxy account required for a non-sysadmin user to eexecute xp_cmdshell.
I know that writing an extended SP invoking a C++ dll would be the cleanest solution. However I don't have the knowledge to do that.
Any ideas?
Thanks
View 1 Replies
View Related
Apr 7, 2004
When I call a stored procedure from a dll written in Builder C++, it gets blocked. But if I call the same SP from the main program, it works fine. but I need to call SP from the dll. What's the problem?
Thanks...
View 1 Replies
View Related
Apr 29, 2008
Hi,
I am new to SQL and new to stored procedures!
What I am trying to do is call one stored procedure from another stored procedure. Very simple piece of code but can not get it to work correctly.....
This is the calling stored procedure: sp_TechRiskMandatory. It is calling a stored procedure called sp_Test.
What is happening is that it is executing the line before the "EXECUTE" command and never getting to this line.
If I put the "EXECUTE" command first it will execute this line and not get to the next.
My code is returning out of the stored procedure before finishing executing the remainder of the code....
CREATE PROCEDURE [sp_TechRisk_Mandatory]
@Conclusion varchar(100),
@TechRisk varchar(100)
AS
If (@Conclusion = 'Application/Changed') and ((@TechRisk = " ") or (@TechRisk = "N/A"))
Begin
Select "AsxErrorMessage" = "Technical Risk must be specified"
EXECUTE sp_Test
End
Else
Begin
Select "Looks Good" = " "
EXECUTE sp_Test
End
GO
This is the code for the sp_Test:
CREATE PROCEDURE sp_Test
AS
BEGIN
Select "AsxErrorMessage" = "Test"
END
GO
View 2 Replies
View Related
May 30, 2008
I know this thread is sql, and i'm asking a vb.net question but I cannot find a straight forward answer anywhere else. I am using visual studio 2008 designing a vb.net application where I created a stored procedure using sql management studio 2005. Here is the stored procedure:
CREATE PROCEDURE dbo.StoredProcedure2
@intPID char(10)
AS
SELECT SUM(Financial.Fee) from dbo.Financial
WHERE
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, getdate())))=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Financial.Date)))
AND [SPatient Number]=@intPID
/* SET NOCOUNT ON */
RETURN
It takes the sum of my Fee column in my financial table where the current date is equal to the column named Date in my Financial table and SPatient Number equals my variable intPID, which the value is defined for in my application (yes, i know that is a horrible name for a column but I cannot change it for it is not my project).
Now, to my knowledge this procedure works fine and should output a single value. However, like i said, i am using visual studio 2008 and therefore am using vs's more automated way of connecting to sql servers(and by that I mean configuring the server through visual studio rather than manually defining datasets, dataadapters, and connection strings through code) All of the tutorials I find use data adapters and are done by manually declaring sql connections and so forth. I like visual studios more automated method of doing sql tasks, and would like to know if there is a simple way to call a stored procedure using visual studio in such a fashion where I would write something like "exec dbo.StoredProcedure2 'intPID' "
Any help is much appreciated, thank you
edit: If i did not provide enough information please let me know, i'm using a strongly typed dataset
View 1 Replies
View Related
Jun 10, 2008
I understand how to call a stored procedure using ordinal arguments, such as:
exec storedprocedure argument1 argument2
How do you do it while naming the arguments, and passing non-ordinal?
Thanks!
View 2 Replies
View Related
