Returning Resulset

Jan 2, 2007

Hello, I need to do the following, But I receive an error:


CREATE PROCEDURE dbo.EJEMPLO_A AS
BEGIN
SELECT TOP 10 NAME, ID FROM SYSCOLUMNS
END
-- and Later use the one above... like this...


CREATE PROCEDURE dbo.EJEMPLO_B AS
BEGIN

DECLARE @temp_lista TABLE(NOMBRE VARCHAR(500),
IDENT NUMERIC(5))

INSERT INTO @temp_lista execute dbo.EJEMPLO_JULIAN

END

Pd. I just have to capture the dbo.EJEMPLO_A resulset... and I can't modify It

View 1 Replies


ADVERTISEMENT

A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint

Sep 25, 2007

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables.  The SELECT looks like this:
 SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
 The TreatmentStatus column is a tinyint.  When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2.  But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?

View 5 Replies View Related

Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?

Jan 10, 2007

I hvae a stored procedure that has this at the end of it:
BEGIN
      EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
 
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?

View 2 Replies View Related

Returning A Value From SQL

Nov 12, 2006

On   cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom        // Getting the Correct Answer from the Database.        int QuiziD = Convert.ToInt32(Session["QuizID"]);        int QuestionID = Convert.ToInt32(Session["QuestionID"]);        SqlConnection oConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\quiz.mdf;Integrated Security=True;User Instance=True");        SqlCommand cmd = new SqlCommand("usp_Question_Count", oConn);        cmd.CommandType = CommandType.StoredProcedure;        SqlParameter QuizParam = new SqlParameter("@QuizId", SqlDbType.Int);        QuizParam.Value = QuiziD;        cmd.Parameters.Add(QuizParam);        SqlParameter QuestionParam = new SqlParameter("@QuestionID", SqlDbType.Int);        QuestionParam.Value = QuestionID;        cmd.Parameters.Add(QuestionParam);        SqlParameter countParam = new SqlParameter("@CorrectAnswer", SqlDbType.Int);        countParam.Direction = ParameterDirection.Output;        //cmd.Parameters.Add(workParam);        cmd.Parameters.Add(countParam);        oConn.Open();        cmd.ExecuteNonQuery();        // get the total number of products        int iCorrectAnswer = Convert.ToInt32(cmd.Parameters["@CorrectAnswer"].Value);        oConn.Close();Heres the stored ProcedureALTER PROCEDURE dbo.usp_Find_Correct_Answer      @QuizID int,     @QuestionID int,     @CorrectAnswer int OUTPUT      /*     (     @parameter1 int = 5,     @parameter2 datatype OUTPUT     )     */AS     /* SET NOCOUNT ON */      SELECT @CorrectAnswer=CorrectAnswer FROM Question WHERE QuizID=@QuizID AND QuestionOrder=@QuestionID

View 2 Replies View Related

Returning Last Row Only

Apr 21, 2007

I have a web application that is data driven from a SQL 2005 database.  The application needs to prompt the user to enter some information that will be logged into a SQL table.
 It should always be the last row in the table that is being worked on at any one time.    Over a period the user will need to enter various fields.  Once the data is entered into a field they will not have access to amend it.
Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited.
 As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it?
Regards
Clive

View 3 Replies View Related

Not Returning What I Need

Aug 17, 2005

I'm hoping someone can help me w/this query.  I'm gathering data from two tables and what I need to return is the following: An employee who is not in the EmployeeEval table yet at all and any Employee in the EmployeeEval table whose Score column is NULL. I'm So lost PLEASE HELP.  Below is what I have.  CREATE    PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID ( @deptID nvarchar(20), @Period int)ASSELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName, ev.Score  FROM Employee AS  eLEFT JOIN EmployeeEval as ev ON e.EmployeeID = ev.EmployeeIDWHERE e.DeptID = @deptId OR (e.deptid = @deptID AND ev.Score = null AND ev.PeriodID = @Period)GO

View 3 Replies View Related

Returning Value From Sql To C#

Mar 16, 2006

hello,
I have a small problem. i'm adding records into the DB. the primary key
is the company name which is abviously unique. before saving the record
i check in the stored procedure if the company code is unique or not.
if unique then the record is added & an output parameter is set to
2 & should b returned to the data access layer. if not unique then
3 should be returned. but everytime it seems to be returning 2 whether
it is unique or not. can u plz help me? here is the code of the data
access layer:
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Value = ParameterDirection.ReturnValue;

//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters;
cmd.ExecuteNonQuery();
status = (int)cmd.Parameters["@Status"].Value;

here is the stored procedure:
CREATE PROCEDURE spOrganizationAdd(
@OrgCode varchar(10),
@OrgName varchar(50),
@AddressLine1 varchar(30),
@AddressLine2 varchar(30),
@City varchar(15),
@State varchar(15),
@Country varchar(15),
@PinCode varchar(7),
@Phone varchar(20),
@Fax varchar(20),
@Website varchar(30),
@Email varchar(50),
@CreatedBy int,
@LastModifiedBy int,
@Status INTEGER OUTPUT) AS
BEGIN TRAN
IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @OrgCode)
BEGIN
SET @Status = 3

END
ELSE
BEGIN
INSERT INTO tblOrganizationMaster VALUES(
@OrgCode,
@OrgName,
@AddressLine1 ,
@AddressLine2 ,
@City ,
@State,
@Country,
@PinCode,
@Phone,
@Fax ,
@Website,
@Email,
@CreatedBy ,
GETDATE(),
@LastModifiedBy ,
GETDATE())
SET @Status = 2
END
IF @@ERROR = 0 COMMIT TRAN
ELSE ROLLBACK TRAN

plz reply as soon as possible.

View 1 Replies View Related

Sql Not Returning Any Data

Feb 20, 2007

Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there  way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.

View 5 Replies View Related

Returning Two Values

Jul 30, 2007

I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT   COUNT(*) AS TodayCount FROM Counter     WHERE     DATEPART(YEAR, visitdate) = Year(GetDate()) AND    DATEPART(MONTH, visitdate) = Month(GetDate()) AND    DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?  

View 6 Replies View Related

ExecuteScalar() Not Returning Value?

Dec 12, 2007

Okay so here's a wierd one.  I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString)
Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn)
With Cmd.Parameters
.Add(New MySqlParameter("?CurrentPage",thisPage))
End With
Conn.Open()
Exists = Cmd.ExecuteScalar()
End Using
End Using Exists is declared outside of that block so that other logic can access it.  thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar().  I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out.  I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?

View 3 Replies View Related

Coalesce Returning 0

Feb 6, 2008

Hi everybody,
I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated.
devmetz

View 4 Replies View Related

Returning Two Different Reults

May 13, 2008

Hi I have two text boxes, Textbox A and Textbox B, what i am trying to do is when values are entered in the textboxes, a query runs and returns the results in a datagrid. However I am unusre on how to structure the stored procedure. Can anyone lead me in the right direction, thanks

View 6 Replies View Related

Returning No Results

May 19, 2008

I have the following stored procedure that is returning nothing can anyone please help?
 SELECT     job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date,                       customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM         tblOrderWHERE     (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End)
 When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id

View 6 Replies View Related

Returning @@identity As Int

May 29, 2008

 Hi All :-)
 
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function.
Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT
AS
Insert INTO TM_TalentInvites
(
TalentID
)
Values
(
@TalentID
)
SELECT @@IDENTITY AS TalentInviteID
RETURN @@IDENTITY
 
And here is my function -  public static int addTalentInvite(int talentID)
{
// Initialize SPROCstring connectString = "Data Source=bla bla";
SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID);
conn.Open();int talentUnique = (int)cmd.ExecuteScalar();
conn.Close();return talentUnique;
}
Any help you can give me will be greatly appreciated thanks!
 
 

View 3 Replies View Related

Sub Query Returning More Than One Row

Jun 11, 2008

I have the following query.
select top 3 dbo.oncd_incident.open_date,dbo.onca_product.description,dbo.onca_user.full_name,dbo.oncd_incident.incident_id,email, dbo.oncd_contact.first_name,dbo.oncd_contact.last_name,dbo.oncd_contact.contact_id
from dbo.oncd_incident
inner join dbo.oncd_incident_contact on dbo.oncd_incident_contact.incident_id=dbo.oncd_incident.incident_id
inner join dbo.oncd_contact on dbo.oncd_contact.contact_id=dbo.oncd_incident_contact.contact_id
inner join dbo.oncd_contact_email on dbo.oncd_contact_email.contact_id=dbo.oncd_contact.contact_id
inner join dbo.onca_user on dbo.oncd_incident.assigned_to_user_code=dbo.onca_user.user_code
inner join dbo.onca_product on dbo.onca_product.product_code=dbo.oncd_incident.product_code
where dbo.oncd_incident.incident_status_code='CLOSED'
and email is not null
and dbo.oncd_incident.open_date>DateAdd(wk,-2,getdate()) and dbo.oncd_incident.completion_date>=DateAdd(dd,-2,getdate()) and
dbo.oncd_incident.assigned_to_user_code in (select user_code from dbo.onca_user)
order by newid()
I want the query to be executed for each row returned by the sub query.If I use IN keyword it returns top 3 rows for any 3 of the users.But I want top 3 rows to be returned for each of teh user.Please help.
 

View 6 Replies View Related

@@Identity Not Returning

Jun 21, 2008

I have a page that inserts customers into the client database. After the data is inserted it redirects to the customer's policy page using the customer's ID set by @@Identity.
The SQL Command is:
 ALTER PROCEDURE [dbo].[AddBasic]
(
@ln NVarchar(50),
@fn NVarchar(50),
@mAdd NVarchar(50),
@mCity NVarchar(50),
@mState NVarchar(50),
@mZip NVarchar(50),
@pAdd NVarchar(50),
@pCity NVarchar(50),
@pState NVarchar(50),
@pZip NVarchar(50),
@sAdd NVarchar(50),
@sCity NVarchar(50),
@sState NVarchar(50),
@sZip NVarchar(50),
@hPhone NVarchar(50),
@cPhone NVarchar(50),
@wPhone NVarchar(50),
@oPhone NVarchar(50),
@eMail NVarchar(50),
@DOB NVarchar(50),
@SSN NVarchar(50),
@liState NVarchar(50),
@liNum NVarchar(50),
@acctSource NVarchar(50),
@active NVarchar(50),
@County NVarchar(50)
)
AS
DECLARE @custNum int
INSERT basicInfo
(lastName, firstName, mailingAddress, mailingCity, mailingState, mailingZip, physicalAddress, physicalCity, physicalState, physicalZip,
seasonalAddress, seasonalCity, seasonalState, seasonalZip, homePhone, cellPhone, workPhone, otherPhone, email, DOB, SSN, liscenceState,
driverLiscense, acctSource, [status], county)
VALUES (@ln,@fn,@mAdd,@mCity,@mState,@mZip,@pAdd,@pCity,@pState,@pZip,@sAdd,@sCity,@sState,@sZip,@hPhone,@cPhone,@wPhone,@oPhone,@eMail,@DOB,@SSN,@liState,@liNum,@acctSource,
@active, @County)
SET @custNum = @@Identity
SELECT @custNum
 The ASPX page has two parts, the SQL Data Source: <asp:SqlDataSource ID="sqlInsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="AddBasic"
SelectCommand="SELECT acctNumber FROM basicInfo WHERE (acctNumber = 5)" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:FormParameter FormField="lName" Name="ln" />
<asp:FormParameter FormField="fName" Name="fn" />
<asp:FormParameter FormField="mAdd" Name="mAdd" />
<asp:FormParameter FormField="mCity" Name="mCity" />
<asp:FormParameter FormField="mState" Name="mState" />
<asp:FormParameter FormField="mZip" Name="mZip" />
<asp:FormParameter FormField="pAdd" Name="pAdd" />
<asp:FormParameter FormField="pCity" Name="pCity" />
<asp:FormParameter FormField="pState" Name="pState" />
<asp:FormParameter FormField="pZip" Name="pZip" />
<asp:FormParameter FormField="sAdd" Name="sAdd" />
<asp:FormParameter FormField="sCity" Name="sCity" />
<asp:FormParameter FormField="sState" Name="sState" />
<asp:FormParameter FormField="sZip" Name="sZip" />
<asp:FormParameter FormField="hPhone" Name="hPhone" />
<asp:FormParameter FormField="cPhone" Name="cPhone" />
<asp:FormParameter FormField="wPhone" Name="wPhone" />
<asp:FormParameter FormField="oPhone" Name="oPhone" />
<asp:FormParameter FormField="eMail" Name="eMail" />
<asp:FormParameter FormField="DOB" Name="DOB" />
<asp:FormParameter FormField="SSN" Name="SSN" />
<asp:FormParameter FormField="dlState" Name="liState" />
<asp:FormParameter FormField="dlNum" Name="liNum" />
<asp:FormParameter FormField="aSource" Name="acctSource" />
<asp:FormParameter FormField="txtCounty" Name="County" />
<asp:Parameter Name="active" DefaultValue="Active" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
 and the redirect:Public Sub Redirect()
Dim x As Integer
x = SqlDataSource1.Insert()
Response.Redirect("~/Main/Main.aspx?q=" & x)
End Sub 

View 2 Replies View Related

Returning The Primary Key

Apr 15, 2004

Is there a way to get asp.net to return the primary key in SQLServer after INSERT as I need to re-name an uploaded file with the primary key. Thanks.

View 10 Replies View Related

@@Identity Not Returning Value

Jun 23, 2004

I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".

Anyone have any ideas?


CREATE PROCEDURE [iguser].[newticket]
(
@Category nvarchar(80),
@Description nvarchar(200),
@Detail nvarchar(3000),
@OS nvarchar(150),
@Browser nvarchar(250),
@Internet nvarchar(100),
@Method nvarchar(50),
@Contacttime nvarchar(50),
@Knowledge int,
@Importance int,
@Sendcopy bit,
@Updateme bit,
@ClientID int,
@ContactID int,
@TicketID integer OUTPUT
)
AS

INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@Category,
@Description,
@Detail,
@OS,
@Browser,
@Internet,
@Method,
@Contacttime,
@Knowledge,
@Importance,
@Sendcopy,
@Updateme
)
SELECT
@TicketID = @@Identity


INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@ContactID,
@TicketID
)

INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@ClientID,
@TicketID
)

View 2 Replies View Related

Returning N/A Instead Of Null

May 1, 2005

I have null fields in one of the column and want to return "N/A" when the column is null. How can I do that ?

View 2 Replies View Related

Returning Key Value With Insert

Oct 3, 2005

I'm using SQL-MSDE and have a table defined with a 'identity seed' column that automatically gets assigned when a record is added (I do not load this value).  This column is also my KEY to this table.   I'm using INSERT to add a record.  Is there a way to return this KEY value after doing the INSERT?

View 4 Replies View Related

Not Returning What I Thought It Should

Nov 21, 2005

SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullNameFROM Employee e INNER JOIN EmployeeEval ev ON  ev.PeriodID = @Period WHERE (ev.Approved = 0) AND (e.DeptID = @deptID)GOI want to select everyone from the employee table in a dept (determined by DDL) who has either a) had a review and not been approved yet - or b) has not had a review yet  ---- all employees are in the employee table -- and all reviews are placed in the employeeeval table. 

View 4 Replies View Related

Returning A Specific Row

Jul 31, 2001

I select a couple of rows from a table and sort them by the Date column. Now I would want to return only the 3rd row...

Thanks for any help!

View 1 Replies View Related

Returning > 255 Characters

Nov 15, 2000

We just upgraded to SQL 7.0 SP2. We enlarged one of our fields from varchar(255) to varchar(500), but when I do a SELECT on the field it only brings back 255. I know this was a limitation in ISQL in 6.5. I tried it in query analyzer and also via a command line. Any ideas how to see all the data?

Thanks

View 2 Replies View Related

Using DTS For Returning In The Recordset

Sep 29, 2000

Is possible use DTS for return Data in recordset in the VB ?

I am asking this , why I have access the database INFORMIX, and several things do not work with Stored Procedure in the Informix

thank you in advance

View 1 Replies View Related

Returning Top 20 Accounts?

Mar 1, 2001

Hi,

How can i code a SQL statement that will return the top 20 accounts from a huge client table?

Thanks

View 1 Replies View Related

Returning Decimal

Mar 3, 2001

I need to know a general syntax for inserting user information from an application into 6 Tables using one stored procedure. Is it something like:

Select fieldname, fieldname AS INSERT INTO tablename (@fieldname, @fieldname)

and keep going down the line, or do I need a BEGIN or GO somewhere?

Thanks!

View 2 Replies View Related

Returning HTML Via SQL

Apr 14, 2008

So I have a stored procedure that returns rows that look similer to this


Code:


<a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a>


Unfortunatly when it is bound to the gridview it seems to convert the value to HTML safe.

The value it prints on the page is


Code:


<a href="~/Manage/ManageVersion.aspx?viewstate=MTgwNywzMTc0LDguOSAgICAgICA=">DPF-13</a>



Hit quote to look at the actual values

How can I fix the output to the gridview to print the actual link and the the html safe value?

Can I accomplish this in SQL or am I going to have to do it all in the c# code (which will be a less clean solution and alot more work).


tia

View 1 Replies View Related

Returning One Of Three Recordsets

Jun 21, 2007

Hi all,

I've written a query which needs to return different records depending on its argument.

I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?


Code:


ALTER PROC [dbo].[spFilteredContractListByUser]

@UserID varchar(7) --The user's 'T' number

AS

PRINT 'User ID is' + @UserID

--Get this user's filter type from tbl

DECLARE @FilterType int

SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID

-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END



The VBA code is:


Code:


rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")



Me.cmbUserFilter.Value contains the argument.

Thanks all

Dave

View 2 Replies View Related

Getdate() Not Returning A Value

Sep 10, 2004

I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

For example,

select * from polldates
where (pollbegindate >= getdate() and pollenddate <= getdate())


This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

Any help would be greatly appreciated!

View 7 Replies View Related

Returning Cluase ?

Oct 20, 2004

Hi,

In Oracle, the below query is valid :

insert into test(sno,sno1) values(1,2) returning id into val_ret;

// The above query in Oracle returns the affected row id's to the PL/SQL or host vriable(val_ret)

Is there any equivalent for the above in SQL Server ?

Please advice,

Thanks,
Sam

View 3 Replies View Related

Sp Not Returning Output

Mar 8, 2007

i have the following stored procedure which does a hit to a server and returns the response

it is hitting the server but the problem is the response is null


any ideas what i'm doing wrong

/****** Object: Stored Procedure dbo.http_geturl Script Date: 2/12/2007 2:00:40 PM ******/
ALTER procedure [dbo].[http_geturl]( @sUrl varchar(8000), @response varchar(8000) out)
As
Declare @obj int ,@hr int ,@status int ,@msg varchar(255)

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0
begin
/* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */
return
end

exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
if @hr <0
begin
set @msg = 'sp_OAMethod Open failed'
goto eh
end

exec @hr = sp_OAMethod @obj, 'send'
if @hr <0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <0
begin
set @msg = 'sp_OAMethod read status failed'
goto eh
end
if @status <> 200
begin set @msg = 'sp_OAMethod http status ' + str(@status)
goto eh
end

exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <0
begin
set @msg = 'sp_OAMethod read response failed'
goto eh
end
exec @hr = sp_OADestroy @obj
return

--Error handling code
eh:
exec @hr = sp_OADestroy @obj
/*Raiserror(@msg, 16, 1) */
return
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

View 2 Replies View Related

Returning Values

Feb 18, 2008

Hi All

I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!

Any help out there?

Thanks

Xo

View 9 Replies View Related

Returning More Than One Record From SP...

Feb 11, 2004

Hi ,

Is there a way in SQL Server stored procedure to return multiple records/ more than one records....?

Thanks in Advance...

-Mohit.

View 1 Replies View Related







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