T-SQL (SS2K8) :: Call Stored Procedure And Obtain Specific Results

Apr 17, 2014

I have a few questions about the following t-sql 2008 r2 sql code listed below that is calling a stored procedure:

DECLARE@return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C'

EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules

The sql listed above does execute the stored procedure called [dbo].[sproom] successfully and returns all the data all the rows from the stored procedure multiple times. However can you tell me the following:

1. How can I have the stored procedure return distinct rows?
2. I want the stored procedure to return selected columns. I tried using the OUTPUT parameter for some of the columns, but I got the error message, "Procedure or function spHomeroom has too many arguments specified.".

when I change the sql above to:

DECLARE @return_value int,
@endYear SMALLINT = 2014,
@CustomerID INT = '9999',
@Schedules CHAR(1) = N'C',
@CustName varchar(50)
EXEC [dbo].[sproom] @endYear
,@CustomerID
,@Schedules
,@CustName

That is when I get the error message.

A solution might be to change the stored procedure, but I would prefer not to since this is a generic stored procedure that I believe a lot of t-sqls and stored procedures will use.

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Call Stored Procedure With 3 Parameters

Apr 16, 2014

In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are needed for the main sql. My problem is I do not know how to have the t-sql call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.

The basic dataset is the following:

SELECT SchoolNumber,
SchoolName,
StudentNumber,
from [Trans].[dbo].[Student]
order by SchoolNumber,
SchoolName,
StudentNumber

I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.

The columns that I need from the stored procedure called StudentData will return the following data columns
that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.

Thus can you show me how to setup the sql to meet this requirement I have?

View 2 Replies View Related

T-SQL (SS2K8) :: Run Block In Stored Procedure Only During Specific Time Frame

May 11, 2015

I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.

Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN

I can get the current minutes of the current time by using:

Select DATEPART(MINUTE,GetDate())

I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.

View 7 Replies View Related

T-SQL (SS2K8) :: Stored Procedure Returning Inconsistent Results?

Mar 11, 2015

Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.

Background

We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.

The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.

Observations

The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.

So the Sproc...

USE [xxxxxxxxxx]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

[Code]....

So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).

The only suggestions I can make have been...

change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS

The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.

I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?

View 6 Replies View Related

T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

Aug 20, 2013

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:

with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???

View 9 Replies View Related

T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results

Dec 4, 2014

I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:

USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

When I do this in the same SSMS window:

exec mobile.GetAllMobileDeviceUsers

SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION

[Code] ....

I get two result sets. The first is 422 rows; the second is 467 rows. Why?

View 4 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

View 9 Replies View Related

Context Of Procedure Call That Results In Trigger

May 8, 2008

If an application makes a connection under a given user, performs the following:
- calls a stored procedure which performs an insert,
- the insert causes a trigger to be fired
- the trigger causes a sql statement to execute against a different
database

Questions:
* Does the user who called the procedure, also need permissions to this 2nd database ?
* What security context does this trigger get executed ?


I have a stored procedure that is being called by my .Net application. The user account that makes the connection has execute permission to the procedure. However, the procedure does an insert which causes a trigger to fire. This trigger does a sql statement against a table in a different database, which the user who is calling the procedure, does not have permissions to access. Im getting an exception when I call the procedure, and it basically says there was a permission error with the user accessing this other database.

View 2 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

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

Need One Query To Obtain Results I Can Only Get With Two Queries

Jul 23, 2005

I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill

View 3 Replies View Related

How To Call A Stored Procedure Using C#

Aug 29, 2007

I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
(  @MarketCode  nvarchar(20),  @MarketName  nvarchar(100),  @LastUpdateDate  nvarchar(2),)
ASINSERT INTO Market(  MarketCode  MarketName  LastUpdateDate)VALUES(  @MarketCode  @MarketName  @LastUpdateDate
)

View 5 Replies View Related

How To Call A Stored Procedure In Asp.net

Mar 31, 2006

I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav

View 2 Replies View Related

Call For BCP Within A Stored Procedure

Aug 7, 2001

Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks

View 2 Replies View Related

How Can I Use Stored Procedure To Call Dll

Aug 7, 2001

Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.

View 1 Replies View Related

How Can I Use Stored Procedure To Call Dll

Aug 7, 2001

Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.

View 2 Replies View Related

Call SQL Stored Procedure

Nov 9, 2005

Greetings,

Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.

Many thanks in Advance

View 1 Replies View Related

Stored Procedure Call

Jul 4, 2006

hi,

i created a stored procedure and below is the code segment of it;

create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS


Declare @SQL_INS VarChar(1000)


SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'

Exec (@SQL_INS)

Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.

View 5 Replies View Related

Call Stored Procedure From Another SP

Dec 11, 2007

Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks

View 3 Replies View Related

How To Call Stored Procedure In VC++

Oct 6, 2007



Respected Sir/MAm

i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....

Could you please help me for the correct syntax code with example.


Thank You.
Upali

View 4 Replies View Related

Stored Procedure's Call

Apr 7, 2008

Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure

The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you

View 8 Replies View Related

T-SQL (SS2K8) :: Obtain A Record Set That Returns Just First Occurrence Of ItemNo Field

Jun 9, 2015

Here is a CTE query

With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo

The results from Query1 and Query2 overlap sometimes.

The result set looks like:

1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000

I only want the first occurrence of each item. The desired result set is:

1 Item1 10000
1 Item2 20000
1 Item3 30000

I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?

View 7 Replies View Related

Asynch Call DTS Or Stored Procedure 1.1

Jul 10, 2006

Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks

View 3 Replies View Related

Call Stored Procedure In Loop

Oct 26, 2006

I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent  method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close();   If so exactly how do I do this? How do I reset the parmaters for the proc?  I haven't done this before where I need to loop through passing parameter to the same proc. thanks    

View 2 Replies View Related

Call Function From Stored Procedure

Jan 19, 2007

Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible.  So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures.  When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno.  I want to put that current batchno into the Update statement and update the record with the current batchno.  Can someone point me in the right direction?  Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View 2 Replies View Related

How To Call A Stored Procedure From A Web Page With VB

Feb 13, 2007

Dear Masters;
How can I call a stored procedure with VB code?
Thanks

View 2 Replies View Related

How To Call Stored Procedure To Table??

Nov 12, 2007

Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you

View 2 Replies View Related

Make A Call To AS/400 Stored Procedure From DTS

Feb 15, 2000

Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.

View 1 Replies View Related

Error Within Stored Procedure Call

Feb 20, 2003

I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):

-- Decalre variables
DECLARE @object INT
DECLARE @hr INT
DECLARE @property VARCHAR(255)
DECLARE @return VARCHAR(255)
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)

-- Create object
EXEC @hr = sp_OACreate 'SQLActiveXTest.SQLActiveXTestClass', @object OUT

-- Call method
EXEC @hr = sp_OAMethod @object, 'getDesc', @return OUT

-- Destroy object
EXEC @hr = sp_OADestroy @object

View 1 Replies View Related

Using A Variable To Call A Stored Procedure Name

Jan 31, 2006

Hello;

I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.

I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.

So how do I refer to a procedures name using a variable?

Here's my code;


Function LieuBen()

MyCalendar = CurrTSCalendar

Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")

End Function

Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String

Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"

Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command

ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"

' Connect to the data source.
objConn.Open ConnectionString

' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn

objConn.MySProc MyCalendar, objRs

objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing


End Function

View 1 Replies View Related

Call Stored Procedure From View

Sep 7, 2006

Hello all,
does anyone know if it's possible to call a stored procedure from a view.

Thnx,
Patrick

View 9 Replies View Related

How To Call A Function In Stored Procedure

Sep 12, 2012

How to call a sql function in stored procedure using Sqlserver 2008?

View 4 Replies View Related

Call DTS Using Parameter In Stored Procedure?

Jul 18, 2014

I have created a DTS package which stores file data into Database table.

I want to pass file name dynamically to stored procedure from which DTS retrieve data and store it in table.

View 3 Replies View Related







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