Calling A Stored Procedure Which Uses A Return @count

Aug 21, 2007

Hi again, and so soon...
Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. 
Firstly the stored proc looks like this:
----------- 
 ALTER PROCEDURE dbo.CountTEst

AS

   SET NOCOUNT ON
    DECLARE @sql nvarchar(100);
    DECLARE @recCount int;  
    DECLARE @parms nvarchar(100);

SET @sql = 'SELECT @recCount1 = COUNT(*)
            FROM Pool '
SET @parms = '@recCount1 int OUTPUT'            
 
exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUT

RETURN @recCount
 -------------
When tested from the stored proc, the result is: 
@RETURN_VALUE = 4 
 
My code that calls this stored proc is:
 
        Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True")
        connect.Open()
        Dim cmd As New SqlCommand("CountTEst", connect)
        cmd.CommandType = CommandType.StoredProcedure
        Dim MyCount As Int32
        MyCount = cmd.ExecuteScalar
        connect.Close()
So I expext MyCount = 4 but this code returns MyCount = 0 
With the RETURN statement in the stored proc, and  then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please.

thanks, 

View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Can't Get Row Count To Return To Calling Stored Procedure

Jul 9, 2014

SQL Server 2012 Standard SP 1.

Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:

declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;

Print statement prints @NumBufferManagerRows as 0.

Here is the called stored procedure B:

CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT

[Code] ...

View 2 Replies View Related

Return A Value From Stored Procedure To Calling Application

Jul 20, 2005

Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?

View 4 Replies View Related

JDBC: Calling A Stored Procedure With Multiple Return Values.

Jul 23, 2005

Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.

View 4 Replies View Related

Stored Procedure - Return Count

Jun 16, 2005

Having a little trouble with this sp... just need to return the count.....CREATE  PROCEDURE dbo.getTotalObjectives @courseId   VARCHAR(20) ASBEGIN DECLARE @errCode     INT
  SELECT count(*)  FROM   objstructure  WHERE  courseId  = @courseId
 SET @errCode = 0  RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath

View 4 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

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

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

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

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

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

Calling Stored Procedure Fromanother Stored Procedure

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

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 View Related

Calling A Stored Procedure

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

Out Vs. Ref When Calling Stored Procedure

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

Calling A DTS Stored Procedure From ASP.NET

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

Calling Stored Procedure

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

Calling DTS From Stored Procedure

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

Calling A Job From A Stored Procedure

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

Calling Stored Procedure

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

Calling A VB Exe From A Stored Procedure

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

Calling A Stored Procedure From A Dll

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

Calling Stored Procedure From Another Sp

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

Calling A Stored Procedure From Vb.net

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

Calling A Stored Procedure

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

Calling Stored Procedure In Another

Nov 19, 2014

I have three stored procedure already created ABC. Now I need to create another one and call other three in each situation. Like If Apple then use Sp_A, if Orange then use Sp_B, and if Mango then use sp_C.

View 16 Replies View Related

Calling BCP From A Stored Procedure

Mar 28, 2006

Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

This is the stored procedure:
PROCEDURE DownloadLinkEvents
@localPath varchar(80)
AS
declare @bcpCommand varchar(200)
begin
set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>'
exec master..xp_cmdshell @bcpCommand
end

Thanx.

View 3 Replies View Related

Calling A Stored Procedure From Within Another

Feb 29, 2008

Hi,

This is probably the most basic of solutions, but I have spent the last 3 hours trying to work it out, and searching google!

I am trying to call information from a stored procedure (B), from within another stored procedure (A). The select statement from Procedure A contains information to be passed to Procedure B, to get some information.

This is the Procedure I have come up with so far, and I have included dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID) as part of the SELECT clause, in a vain attempt that this would work....but it dosn't!

Anyone got any ideas of how to do this? Or even what it would be called so I can start making inteligent searches on google?


USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/29/2008 18:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@STYPE Int, @UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100)

As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age, dbo.USERS_MEMBERSHIPSTATUS.STATUS(2, dbo.Members.EntryID)
FROM dbo.Members INNER JOIN

dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
WHERE @STYPE = '1' AND ((dbo.Members.EntryID = @UserID) or
(dbo.Members.Username = @Username) or
(dbo.Members.Surname = @surname) or
(dbo.Members.DateofBirth = Convert(datetime, @DOB)) or
(dbo.ActiveMember_Location.Location2 = @Location) or
(dbo.ActiveMember_Location.Location3 = @Location) or
(dbo.ActiveMember_Location.Location4 = @Location) or
(dbo.ActiveMember_Email.value = @Email) or
(dbo.ActiveMember_Mobile.value = @Mobile))



ORDER BY dbo.Members.Username

View 1 Replies View Related

Calling DTS From Stored Procedure

Aug 11, 2005

wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute aDTS package from a stored procedure. I had the dba (using the saaccount) create a wrapper stored procedure as recommended inhttp://msdn.microsoft.com/library/d...rary/en-us/dnsq....However, when I executed the wrapper stored procedure, I stillreceived privilege errors from the underlying sp_oa extended storedprocedures.Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 6EXECUTE permission denied on object 'sp_OACreate', database 'master',owner 'dbo'.Any ideas on what we could be doing wrong, or any suggested resolutionswould be appreciated.Thank you.

View 1 Replies View Related

Calling Stored Procedure

Aug 25, 2005

I have a created a report that needs to call a sybase Stored Procedure, my connection string is fine.  I am able to call the sp from sql advantage passing in several parameters and it runs just fine.  However when I created a dataset and tried to call it through the reporting service I get the following error message.

View 5 Replies View Related

Calling Stored Procedure

Oct 2, 2007

I wrote a stored a stored procedure in SQL 2005 which finds a specific number. If I execute the procedure in SQL it finds the correct number. I want to call this stored procedure from within Visual Basic 2005 so I can use it in my program.
Any thoughts???

View 1 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

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

Stored Procedure Calling Another Stored Procedure_

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

Calling Stored Procedures From Another Stored Procedure

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







Copyrights 2005-15 www.BigResource.com, All rights reserved