Database

how to create stored procedure?

21-Jun-07 21:30

refer to message below with subject
"Problem with special characters in SqlParameter"
similary usage is explained as what you are looking for,
in different ways ...

Regards
KP

Re: query

Blue_Boy21-Jun-07 21:51

Blue_Boy

21-Jun-07 21:51

if you are using asp.net with C# then try

TextBox1.Text.Replace("'","''");

I Love SQL

Care Career21-Jun-07 20:12

Care Career

21-Jun-07 20:12

Please help

I want to create stored procedure using two tables &
Want to commit data of first table & rollback .

Thnx
CARe CAREER

Re: how to create stored procedure?

Krish - KP21-Jun-07 20:22

Executing Stored Procedure

21-Jun-07 20:22

are the DML operations are done one after the other in batch or jumbled

Regards
KP

M. J. Jaya Chitra21-Jun-07 19:37

M. J. Jaya Chitra

21-Jun-07 19:37

I want to create a stored procedure in which while inserting or updating a record if a flag is set then i have to insert some other values in the parent table and based on the id which is auto generated by the parent table i have to insert that value into the child table How can i perform this.

I have got already two procedures for inserting and updating the data into the tables.

Best Regards,
M. J. Jaya Chitra

Re: Executing Stored Procedure

Krish - KP21-Jun-07 20:00

urgent need max of second record...,

21-Jun-07 20:00

IF @flag = <value>
BEGIN
<insert data into master table>
<insert data into child table>
END

@@IDENTITY returns last generated value in Identity column.

Regards
KP

Member 387988121-Jun-07 18:13

Member 3879881

21-Jun-07 18:13

Hi guys i have doubt in max values...,
See this is my table...,
salary
====
1000
2000
3000
4000
4500
1500

my expectation is produce only the 4000 from the given table..
which means second maximum salary..?
How to do that...,
Plz help me..,

Magi

HemMagesh21-Jun-07 18:39

HemMagesh

21-Jun-07 18:39

select * from tblname where sal=(select max(sal) from tblname

where sal < (select max(
sal) from tblname));

Magesh

Krish - KP21-Jun-07 18:50

21-Jun-07 18:50

DECLARE @nth as bigint
SET @nth = 10

SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)

This will return 10th Max.
change value of the variable @nth to get the required Max.

The same i've given earlier for finding Nth Max value
in your case assign 2 to @nth variable

Regards
KP

Krish - KP21-Jun-07 18:48

21-Jun-07 18:48

Regards
KP

Sathesh Sakthivel21-Jun-07 18:49

Sathesh Sakthivel

21-Jun-07 18:49

hey its very simple man.

select top second salary from table_name order by salary desc.

Since in your table the 4000 is the second top salary.

Regards,

Satips. Rose | [Rose]

your right answer is here

Member 387988121-Jun-07 19:50

Member 3879881

21-Jun-07 19:50

Hi satips plz check ur query in ur query analyzer...,
It willn't work...,

Regards,
Magi

Sylvester george21-Jun-07 20:37

Sylvester george

21-Jun-07 20:37

select max(salary) from mytable where salary < (select max(salary) from mytable)

Regards,
Sylvester G
sylvester_g_m@yahoo.com

Syntax Error in INSERT INTO Statement

Cfer8321-Jun-07 6:08

Cfer83

21-Jun-07 6:08

C# And Access DB.
There is an Autonumber field in the table, 'PP_WF_ID'. Every field is Text Type except Personnel_Id And Company_Id

<br />
    bool InsertWorkFlowPP()<br />
    {<br />
        if (Sel.IsAuthenticated())<br />
        {<br />
            try<br />
            {<br />
                using (OleDbConnection myconn = new OleDbConnection())<br />
                {<br />
                    OleDbCommand mycomm;<br />
                    string sSQL = "INSERT INTO PP_WF(Personnel_ID,Photo_Location,pp_Name,Position,";<br />
                    sSQL += "Highlights,Education,Projects,";<br />
                    sSQL += "Full_Description,Summary,Company_ID,WF_STATUS,Is_CEO)";<br />
                    sSQL += " VALUES(@Personnel_ID,@Photo_Location,@pp_Name,@Position,";<br />
                    sSQL += "@Highlights,@Education,@Projects,@Full_Description,@Summary,@Company_ID,@WF_STATUS,@Is_CEO);";<br />
                    myconn.ConnectionString = ConfigHelper.GetWFString();<br />
                    mycomm = new OleDbCommand(sSQL, myconn);<br />
                    mycomm.Parameters.AddWithValue("@Personnel_ID", System.Convert.ToInt32(Labelcode.Text));<br />
                    mycomm.Parameters.AddWithValue("@Photo_Location", picture.ImageUrl);<br />
                    mycomm.Parameters.AddWithValue("@pp_Name", name.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Position", position.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Highlights", highlights.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Education", education.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Projects", projects.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Full_Description", longbio.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Summary", shortbio.Text.Trim());<br />
                    mycomm.Parameters.AddWithValue("@Company_Id",Sel.GetCookies("Company_id"));<br />
                    mycomm.Parameters.AddWithValue("@WF_STATUS", "NA");<br />
                    mycomm.Parameters.AddWithValue("@is_CEO", chkCEO.Checked.ToString());<br />
                    myconn.Open();<br />
                    mycomm.ExecuteNonQuery();<br />
                    return true;<br />
                }<br />
            }<br />
            catch (Exception)<br />
            {<br />
            }<br />
            finally<br />
            {<br />
            }<br />
        }<br />
        return false;<br />
    }<br />

Re: Syntax Error in INSERT INTO Statement

Cfer8322-Jun-07 4:17

Cfer83

22-Jun-07 4:17

Can someone move this to the ASP.NET Category perhaps?

I would like to solve this today, thanks.

Re: Syntax Error in INSERT INTO Statement

sidbaruah27-Jun-07 22:48

sidbaruah

27-Jun-07 22:48

In this
<< "@Highlights,@Education,@Projects,@Full_Description,@Summary,@Company_ID,@WF_STATUS,@Is_CEO)(this one here) ;"; >>

Wat is the ;(semicolon) doing just before this << "; >>

I guess you need to remove that!!!

I was born dumb!!
Laugh | :laugh:

Programming made me laugh Laugh | :laugh:

!!!
--sid--

Dying on this query

Hulicat21-Jun-07 4:16

21-Jun-07 4:16

Hi All,
1st off what is the best T-SQL book I can get for a novice? obviously I have much to learn.
If I run the query below as is I get this:

Msg 8120, Level 16, State 1, Line 12
Column 'job_ticket.LOCATION_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Okay so I add it to the "group by" and it still does not work.
If I removed the piece in bold the query executes, but does not break down by "client".

Also the AVG should be the AVG of @startdate and @enddate; I know it's incorrect now but I cant get it to work the way I need it to.

I needed help to get where I am now....Thanks Roy

Table Job_ticket
Status_type_id varchar
Location_id varchar

Table Location
Location_ID varchar
Location_name varchar

SELECT Opened, [Total Open at Start], [Total Open at End],
[Total Open at End] - [Total Open at Start] as [Total Closed],
Client, [Average days open]
FROM (SELECT count(*) AS 'Opened',
SUM(CASE WHEN status_type_id LIKE '[1,2,6,7]'
THEN 1 ELSE 0
END) AS 'Total Open at Start',
(select count(*) from dbo.job_ticket as b
where j.location_id = b.location_id
and status_type_id like '[3,4,5]'
and last_updated between @startdate and @enddate)
AS 'Total Open at End',
l.location_name AS 'Client',
AVG(datediff(d,report_date,getdate() ))
AS [Average days open]
FROM job_ticket j
JOIN location l on l.location_id = j.location_id
WHERE (report_date BETWEEN @startdate AND @enddate)
GROUP BY l.location_name) as X

Regards,
Hulicat

Krish - KP21-Jun-07 5:08

21-Jun-07 5:08

report_date is from which table?

also not getting what you are exactly trying for

Is that want location wise
1. count number of rows falls in a date range
2. count number of rows having status_type_id 1, 2, 6 or 7
3. count number of rows having status_type_id 3, 4 or 5
is it some thing like this

Regards
KP

andyharman21-Jun-07 5:19

andyharman

21-Jun-07 5:19

I think what you're after is:

SELECT L.location_name AS Client,
   TotalOpenAtStart,
   TotalOpenAtEnd,
   (TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed,
   AvgDaysOpen
FROM location l
LEFT OUTER JOIN (
   SELECT j.location_id,
      SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END) AS TotalOpenAtStart,
      SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END) AS TotalOpenAtEnd,
      AVG(DATEDIFF(d, j.report_date, GETDATE())) AS AvgDaysOpen
   FROM job_ticket j
   WHERE j.report_date BETWEEN @startdate AND @enddate)
   GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY L.location_name

Regards
Andy

Hulicat21-Jun-07 6:13

21-Jun-07 6:13

Thanks Andy it does not like the group by
"Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'GROUP'."

If it helps the report_date is in the job_ticket table datetime. thanks for taking the time to help me...It's greatly appreciated. I tried tweaking it a bit and no luck........

SELECT L.location_name AS Client, TotalOpenAtStart, TotalOpenAtEnd,
(TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed, AvgDaysOpen FROM location l
LEFT OUTER JOIN ( SELECT j.location_id,
SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END)
AS TotalOpenAtStart, SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END)
AS TotalOpenAtEnd, AVG(DATEDIFF(d, j.report_date, GETDATE()))
AS AvgDaysOpen FROM job_ticket j WHERE j.report_date BETWEEN @startdate AND @enddate)
GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY l.location_name

Regards,
Hulicat

andyharman22-Jun-07 0:40

andyharman

22-Jun-07 0:40

Remove the bracket that follows the @enddate parameter.

Hulicat22-Jun-07 2:02

22-Jun-07 2:02

Thanks I got it to execute, however the math did not come out correct.

Although, I think I got enought here to figure it out from here.

Thanks to everyone that replied and helped.

Regards,
Hulicat

Michael Potter21-Jun-07 10:52

Michael Potter

21-Jun-07 10:52

I am sure there is a better way but, without knowing your data - try this:

SELECT
    Opened, 
    [Total Open at Start], 
    [Total Open at End],
    [Total Open at End] - 
        (SELECT 
            COUNT(*) 
         FROM 
            dbo.job_ticket 
         WHERE 
            location_id = Source.location_id AND
            status_type_id like '[3,4,5]' AND 
            last_updated between @startdate and @enddate) as [Total Closed], 
    Client, 
    [Average days open]
FROM
    (SELECT 
        l.location_id,
        l.location_name AS 'Client', 
        COUNT(*) AS 'Opened',
        SUM(CASE 
                WHEN status_type_id LIKE '[1,2,6,7]' THEN 1 
                ELSE 0 
            END) AS 'Total Open at Start', 
        AVG(datediff(d,report_date,getdate())) AS [Average days open]
     FROM 
        job_ticket j 
     INNER JOIN
        location l 
        ON (l.location_id = j.location_id)
     WHERE 
        report_date BETWEEN @startdate AND @enddate
     GROUP BY
        l.location_id,
        l.location_name) AS Source

Hulicat21-Jun-07 13:23