Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year

May 15, 2007

hi,

i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:

i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:

[<<Prev]  [Selected Month]  [Next>>]

 the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).  

i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.

i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.

i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!

View 2 Replies


ADVERTISEMENT

Sql Query To Retrieve Comments Based On Selected Month And Data

Jan 4, 2008

hi,
i am generating report for my project.
my table goes like
ID   VASID     VASSID     JanMail   JanVisit    JanPhone    JanComment    FebMail   FebVisit    FebPhone   FebComment................. DecPhone  
1      25            4              True         False         False            Mail me             False       True           False            Visit me                  
 
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. 
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
 
if any one know the solution for the above problem,Please help me
 
Thanks & Regards,
 

View 2 Replies View Related

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

Apr 30, 2007

Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.

View 4 Replies View Related

Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row

Apr 5, 2008

Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show Last Year YTD Month With Procedure

Feb 22, 2015

I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels

in level2:

my procedure display the output of (Actuals in lakhs)

RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)

(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)

Materials .... ... ..
production ..... ............ ........

i made a procedure for above

create procedure Pl_level2

@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1

[code]....

like i m inserted so many accounts in temp tables .

IF (@category IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp

[code]....

here i can display only current YTD only how to display previous year (13-14) YTD in level1

How to do?

View 1 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

Grouping My Month/year Desending By Month/year

Dec 11, 2006

i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View 9 Replies View Related

Sql For Financial Reporting Periods This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year

Oct 26, 2006

Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine  for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas? 

View 8 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

Select Month/year When Range Spans Year

Feb 25, 2004

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View 5 Replies View Related

Getting Date In Day/month/year Instead Of Month/day/year

Feb 2, 2004

I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:


CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END


This returns te american version, can anyone help me to get this to convert the UK way.

Thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

Passing A Selected Row Column Value To The Stored Procedure

Feb 17, 2006

I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine.  If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP.   Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"                    AutoGenerateColumns="False" DataSourceID="SqlDataSource2">                    <Columns>                        <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"                            ReadOnly="True" SortExpression="member_id" />                        <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />                        <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />                        <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />                        <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />                        <asp:CommandField ShowEditButton="True" />                        <asp:CommandField ShowDeleteButton="True" />                    </Columns>                </asp:GridView>                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"                    SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"                    DeleteCommand="renMemberDelete"  DeleteCommandType="StoredProcedure"                      OldValuesParameterFormatString="original_{0}"                     >                                         <DeleteParameters>                                             <asp:Parameter  Name="member_id" Type="Int32"  />                                              </DeleteParameters>                                            </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id  as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO

View 1 Replies View Related

Using Datagrid Selected Value To Input To Parameter In Stored Procedure

Dec 9, 2005

could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE.  I receive my 3 column headers in my datagrid but the parameter isn't doing anything
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@Machcusid nvarchar  OUTPUTAS
SELECT     dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM         dbo.Machine INNER JOIN                      dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN                      dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlDataAdapter2.Fill(DsMachcusidsearch1)
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
DGstatussearch.DataBind()
End Sub
End Class

View 4 Replies View Related

Help Writing A Stored Procedure...

Dec 20, 2007

I'm developing a library and want to display the alphabets across the screen.  When a user clicks on one of the alphabets I want all titles beginning with that letter to appear on the screen.
How would I write this stored procedure?
My table is called Titles
Fields: Title ID Titles
Thanks!

View 2 Replies View Related

Need Help Writing A Stored Procedure

Feb 2, 2008

How would I write a stored procedure to get * where duedate is lessthan and not equal to today's date. Is this right?select * from librarywhere duedate < != getdate()Thanks

View 2 Replies View Related

Get Last Day Of Month For Agiven Month And Year

Aug 2, 2002

Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie

View 1 Replies View Related

Unable To Execute Stored Procedure When New Item Is Selected In Dropdown.

Mar 30, 2006

I'm using a form that has a dropdown control.  This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call.  I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected.  Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback.  Any ideas on resolving this issure?  Your help is much appreciated.

View 1 Replies View Related

Problem In Writing Stored Procedure

Oct 30, 2007

hi,
i am new to this.
in my aspx page, i am having a dropdown which has two value
Name
EmpID

if user clicks on name from drop down, user has enter the name of employee in a textbox. And if user clicks on EmpID, he has to enter the employee ID in textbox.

Based on selection the data will be displayed in grid view.

In my database, i have a table called EmpInfo, which has three fields - EmpName, EmpID and State.


can anybody help me in writing a stored procedure for this??

Thanks
Jaimin

View 3 Replies View Related

How To Activate Only The Selected Year In The Calender

Apr 25, 2008

I have calenders activated in our reports.So, I want only those date values to be enabled for the year I am selecting . Is there any option for that?????

View 1 Replies View Related

Trouble With Stored Procedure

Feb 4, 2008

When I try to run these queries in my stored procedure by hardcoding officeids, they run fine, but when I use the parameter @officeid and then try to run the procedure by using
getEmployeeInfo_proc '001'   I get no results. Can anyone see what I could be doing wrong?  Thanks Here is my code:
 
 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7 ALTER PROCEDURE getEmployeeInfo_proc (@officeid varchar(10))AS
8 select distinct
9 a.emplid, a.name,a.last_name, a.first_name, a.status, a.officeid,
10 b.deptid_child_node, b.dept_child_descr, a.job_descr,
11 d.officename
12 from employeeinfo_vie a,
13 dept_all_nodes_tbl b,
14 office_tbl d
15 where
16 a.status in ('A','L','P') and
17 a.officeid = d.officeid and
18 a.deptid = b.deptid_child_node and
19 a.officeid = '@officeid'
20
21 UNION
22
23 select distinct
24 e.emplid, e.name, e.last_name, e.first_name, e.status, e.officeid,
25 b.deptid_child_node, b.dept_child_descr, f.job_descr,
26 d.officename
27 from phone_admin_tbl e,
28 dept_all_nodes_tbl b,
29 office_tbl d,
30 jobs_tbl f
31 where
32 e.status in ('A','L','P') and
33 e.officeid = d.officeid and
34 e.deptid = b.deptid_child_node and
35 e.job_code = f.job_code and
36 e.officeid = '@officeid'
37 order by name
38
39 return
40
41 GO
42 SET QUOTED_IDENTIFIER OFF
43 GO
44 SET ANSI_NULLS ON
45 GO
46
47
48
49
50
 

View 4 Replies View Related

Trouble With Stored Procedure

Mar 11, 2008

I'm having trouble with this stored procedure, it gets down to the insert and then times out.
Can anyone see why the insert might be wrong?
I did check and at least one of the workshops is open and the @Status is showing it as Open on the client.ALTER PROCEDURE [dbo].[AddNewWorkshopAssigned]

@Workshop_ID int,
@Client_ID int,
@Wait_list bit,
@DateEntered datetime,
@EnteredBy nvarchar(50),

@Status nvarchar(10) OUTPUT

AS

BEGIN

SET @Status = (SELECT
CASE
WHEN (tblWorkshop.Workshop_Status) = 'Canceled' THEN 'Canceled'
WHEN (tblWorkshop.Workshop_Size - COALESCE(COUNT(tblWorkshopsAttended.client_ID),0)) >= 1 THEN 'Open'
ELSE 'Closed' END AS Current_Status
FROM tblWorkshop INNER JOIN
tblWorkshopsAttended ON tblWorkshop.Workshop_ID = tblWorkshopsAttended.workshop_id
WHERE tblWorkshopsAttended.Workshop_ID=@Workshop_ID AND tblWorkshopsAttended.Wait_list = 0
GROUP BY tblWorkshop.Workshop_ID,tblWorkshop.Workshop_Size,tblWorkshop.Workshop_Status)

--Change Workshop Status if NOT canceled.
If @Status <> 'Canceled'
UPDATE tblWorkshop SET
Workshop_Status=@Status
WHERE tblWorkshop.Workshop_ID=@Workshop_ID

IF @Status = 'Open'

INSERT INTO tblWorkshopsAttended (Workshop_ID,Client_ID,Wait_list,DateEntered,EnteredBy)

Values (@Workshop_ID,@Client_ID,@Wait_list,@DateEntered,@EnteredBy)

END 

View 3 Replies View Related

Writing Stored Procedure In .NET 1.1 And Using In SQL Server 2005

Jul 7, 2006

Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani

View 1 Replies View Related

Writing A Monthly Report With Stored Procedure

Aug 13, 2007

Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
            1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
            2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
            3. There are around 500 employees data im dealing with
            4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
            5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
            6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
            7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...

View 7 Replies View Related

Need Help Writing Stored Procedure Involving Dates

Mar 7, 2005

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @StartDate and @EndDate

@StartDate Datetime = null
@EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null


AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)

View 7 Replies View Related

Writing To Text File From Stored Procedure

Sep 26, 2013

Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."

1. Is xp_cmdshell a bad idea to use even if I get permissions ?
2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?

View 2 Replies View Related

Writing To Text File From Stored Procedure

Mar 29, 2006

hi

Writing to text file from table/view is done using osql,bcp etc. How do we write output of stored procedure into text file??

Thank you

View 4 Replies View Related

Writing Code Vs Using Stored Procedure Within SSIS

Aug 7, 2007



Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?

Thanks

View 3 Replies View Related

Using Sp_columns Stored Procedure Trouble

May 12, 2005

Hello,

I'm trying to use the "sp_columns" stored procedure to pull in some
information about a table in my db, but I'm continuing to get the same
error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_columns' expects parameter '@table_name', which was not supplied.

I'm not sure why, but I've re-written my code several times and can't
figure out just why this error is happening.  Here's a snippet of
the code:

Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String
        Dim con As New
OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection"))
        Dim com As New OdbcCommand("sp_columns", con)
        com.CommandType = CommandType.StoredProcedure

        Dim param As New OdbcParameter("@table_name", table)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        param = New OdbcParameter("@column_name", columnName)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        Dim dr As OdbcDataReader
        Dim name As String

        con.Open()
        dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR
        While dr.Read
            name = dr("TYPE_NAME")
        End While
        dr.Close()
        con.Close()

        Return name
    End Function

Any suggestions would be appreciated.

View 3 Replies View Related

Syntax Trouble With Int In Stored Procedure

May 9, 2006

Dear Forum,
I am adding a new column name to my Stored Procedure called HeadlinerID.  It is an Int that is 4 characters long.  I seem to be putting this in incorrectly in my stored procedure.  I have tried it like: @HeadlinerID int(4), and @HeadlinerID int,  and both ways I get the error below:
Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’.
Is there a trick to putting in integers in a stored procedure?
 
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert(   @Title varchar(50),   @_Date datetime,   @Venue varchar(50),   @HeadlinerID int(4),   @Opener varchar(150),   @Doorstime varchar(50),   @Showtime varchar(50),   @Price varchar(50),   @Onsaledate datetime,   @Ticketvendor varchar(50),   @TicketURL varchar(150),   @Description varchar(1000),
)AS
INSERT INTO shows(   Title,   _Date,   Venue,   HeadlinerID,   Opener,   Doorstime,   Showtime,   Price,   Onsaledate,   Ticketvendor,   TicketURL,   Description)VALUES(   @Title,   @_Date,   @Venue,   @HeadlinerID,   @Opener,   @Doorstime,   @Showtime,   @Price,   @Onsaledate,   @Ticketvendor,   @TicketURL,   @Description    )GO

View 3 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

SQL Server 2012 :: Run Stored Procedure Without Writing To Transaction Log?

Feb 26, 2015

Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.

The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.

Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?

View 6 Replies View Related







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