stored procedures in sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

C#

DataSet ds = GetDataSet("select VehicleId,Datetime,Location from vehiclepoints where vehicleid='" + Sno + "' and (Datetime between '" + txtstart.Text + "' and '" + txtend.Text + "') group by VehicleId,Datetime,Location order by Datetime");
 
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{

 DateTime dt1 = Convert.ToDateTime(ds.Tables[0].Rows[i]["Datetime"]);
 int j = i + 1;
 DateTime dt2 = Convert.ToDateTime(ds.Tables[0].Rows[j]["Datetime"]);
 TimeSpan tspan = (dt2 - dt1);

 int intMinutes = Convert.ToInt32(tspan.TotalMinutes);
 string vid = ds.Tables[0].Rows[i]["VehicleId"].ToString();
 string loc = ds.Tables[0].Rows[i]["Location"].ToString();
 
RunQueryWOCheck("insert into IdleReport(VehicleNo,Startdate,Enddate, Duration,Location)values('" + vid + "','" + dt1 + "','" + dt2 + "','" + intMinutes + "','" + loc + "')");

}

this is coding for c# how to convert in stored procedure by using sql server 2008

Posted 13-Aug-13 20:17pm

SivaleelaS

Updated 13-Aug-13 20:18pm

Code-Hunt

v2

Add a Solution

Comments

venkateshCST 14-Aug-13 3:56am

create procedure [dbo].[vechile_name]
@Sno nvarchar(10),
@startdate datetime,
@Enddate datetime
as
begin

select VehicleId,Datetime,Location from vehiclepoints where vehicleid=@Sno and (Datetime between @startdate and @enddate ) group by VehicleId,Datetime,Location order by Datetime

end

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mukesh Ghosh · Answer 1 · 2013-08-13T20:33:00

If you don't know how to create stored procedure then check for bellow link
http://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx[^]

Because if you can write Store procedure then later you can create as per your wish.

Here is a sample of your code, please modify as per your requirement

SQL

-- =============================================
-- Author:		MUKESH GHOSH
-- Create date: 14-08-2013
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[stp_Save_Vehicle]
	@VehicleID INT
AS
BEGIN
	DECLARE @Vehicle TABLE (
	VehicleId INT,DatetimeV Datetime,Location NVARCHAR(100));
	
	DECLARE @VehicleFilter TABLE (
	VehicleId INT,DatetimeV Datetime,Location NVARCHAR(100));
	
	DECLARE @IdleReport TABLE
	(VehicleNo INT,Startdate DATETIME,Enddate DATETIME,Duration NVARCHAR(50),Location NVARCHAR(50))
	
	DECLARE @count INT, @rowCount INT,@Location1 NVARCHAR(100) 
	INSERT INTO @Vehicle (VehicleId,DatetimeV,Location)
	VALUES(1,GETDATE(),'DELHI')
	INSERT INTO @Vehicle (VehicleId,DatetimeV,Location)
	VALUES(1,GETDATE(),'BOMBAY')
	INSERT INTO @Vehicle (VehicleId,DatetimeV,Location)
	VALUES(2,GETDATE(),'CHENNI')
	
	INSERT INTO @VehicleFilter
	SELECT * FROM @Vehicle V WHERE V.VehicleId=@VehicleID
	
	
	SET @count = 1
	SELECT @rowCount = COUNT(1) FROM @VehicleFilter
	
	WHILE @count <= @rowCount
	BEGIN
		SELECT @Location1=Location FROM @VehicleFilter
		
		INSERT INTO @IdleReport(VehicleNo,Startdate,Enddate, Duration,Location)VALUES
		(1,GETDATE(),GETDATE(),DATEDIFF(MINUTE,GETDATE(),GETDATE()),@Location1)
	SET @count = @count + 1
	END
END

[edit]Code block added[/edit]

macmacmacmac · Answer 2 · 2013-08-13T22:26:00

Solution 2

this is a good starter:

Sql Server - How to write a Stored procedure in Sql server[^]

by the way: your conventional code (the one that uses (...where column='"+TextBox1.Text+"') is vulnerable to sql injection. Try to use stored procedure, segregate database dependents into a class.

Posted 13-Aug-13 22:26pm

macmacmacmac

Updated 13-Aug-13 22:29pm

v2