About Sql Statement

I am writing Procedure in SQL Server 2000, and i am giving three inputparameters ie: Account number and from date and to date.but in will give input to procedure as account number or from date and todate.

So in select command how can i write, ie i will give input any one ie accno or ftomdate and to date. i will write sql query which i write but it is giving error

Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
if @.Folio = ''
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Hi Majid,

CREATE PROCEDURE SomeProcedure
(
@.mft_accno INT = NULL,
@.Fdate DATETIME = NULL,
@.tdate DATETIME = NULL
)
AS
Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
and
( mft_procdate between @.Fdate and @.tdate
OR mft_accno= @.Folio
)

As from your description both will not be passed, onyl either the Date or the number. If AccountNumber or dates can be NULL you will have to add the AND columnn is NULL to not give back the Nulled values.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

about select between help pls

well i have 2 input date form and to.

"date_from" varchar variable

and "date_to" varchar variable

where in i want to select only the dates between the two variables only.

example

date_from -date_to contains= "200702"-"200705"

and everything that starts from 200702 to 200705 will be output.

can someone help me or give some similar examples regarding this?

thanks...

Select * from Transaction Where Date_From >= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar)) AND Date_To <= (Cast(Year(TransactionDate) as varchar) + Cast(Month(TransactionDate) as varchar))|||

My suggestion is not to use the BETWEEN operator, but instead use the >= and < operators. This, along with adding 1 day to the upper bounds of the "to" date, will help to avoid the pitfalls you can encounter when trying to work around the time portion of dates.

Try something like this:

DECLARE @.date_from datetime

DECLARE @.date_to datetime

SELECT @.date_from = '20070201', @.date_to = '20070530' -- note the the time portion of the date will default to midnight 00:00:00.0000

SELECT @.date_to = DATEADD(d, 1, @.date_to) -- add 1 day to the ending date

SELECT

someColumns

FROM

someTable

WHERE

myDate >= @.date_from AND myDate < @.date_to

|||

how about this one?

Public Sub read_records(ByVal nyutancd As String, ByVal date_from_new As String, ByVal date_to_new As String)

'配送依頼のデータ取得
'SQL作成
db = New TDataCenter.db.TDataCenter("venus", "venus", "threesupport", "postgres", "postgres")

date_from = ""
date_to = ""
syain_name = ""
product = ""

Dim objReader As PgSqlDataReader

'objReader = db.GetSQLReader(String.Format("Select distinct urr_urinenget,urr_etancd,urr_scd,urr_urikbn,urr_zurgsuu,urr_zurgkin,urr_zsirgkin,urr_curgsuu,urr_curgkin,urr_csirgkin,urr_gurgsuu,urr_gurgkinn,urr_gsirgkin,urr_turgkin,urr_tsirgkin,urr_kousindate from urr")))
objReader = db.GetSQLReader(String.Format("Select distincturr_urinenget,sn_syainnm,sy_snm,urr_urikbn,kt.kt_nm fromurr inner join sy on sy.scd = urr.urr_scd inner join sn on sn.syaincd = urr_etancd inner join kt on kt.kt_cd = urr.urr_urikbn and kt.kt_kbn='cm07'where urr_urinenget between {0} and {1} group by urr_urinenget,sn_syainnm,sy_snm,urr_urikbn, kt.kt_nm ", TDataCenter.db.TDataCenter.SingleQuatedStr(date_from_new), TDataCenter.db.TDataCenter.SingleQuatedStr(date_to_new)))

do you think it will work?

|||

natasha_arriell:

do you think it will work?

That approach is insecure. Do not use placeholders and string substitution; always use parameters.

I have no idea what processing SingleQuatedStr does to those string values. I have no idea what those string values contain. And I have no idea what sort of data is stored in the urr_urineget column. Your best bet is to use some edge case data and try it yourself to see if you are getting the desired result.

About inserting some parameters to a stored procedure.

Hello everyone,

I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance

Errors

'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType'

'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand'

protected void Button1_Click(object sender, EventArgs e) {/* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */string Interview1 = TextBox1.Text;string Interview2 = TextBox2.Text;string Interview3 = TextBox3.Text;string ProdMentioned = TextBox4.Text;string ProdSeen = TextBox5.Text;string Summary = TextBox6.Text;string Compere = TextBox7.Text;string Duration = TextBox8.Text;//Create Sql connection variable that call the connection string SqlConnection SqlConnection =new SqlConnection(GetConnectionString());//Create a sql command to excute SQL statement against SQL server SqlCommand Command =new SqlCommand();// Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure;//Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand ="Summaries";//open the command connection with the connection string Command.Connection = SqlConnection;// Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3); Command.InsertParameters.Add("ProdMentioned", ProdMentioned); Command.InsertParameters.Add("ProdSeen", ProdSeen); Command.InsertParameters.Add("Compere", Compere); Command.InsertParameters.Add("Duration", Duration);int rowsAffected = 0;try { rowsAffected = Command.Insert(); }catch (Exception ex) { Resonse.Redirect("InsertSuccessfull.aspx"); }// open the connection with the command //Command.Connection.Open(); }private static string GetConnectionString() {return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString; }}

not sure about your errors, but I think if you fix those, you will soon get one because

http://peterkellner.net/2006/10/20/loginfailswithiisnotcasini/

Command.InsertParameters.Add("interview1", interview1);

should be

Command.InsertParameters.Add("@.interview1", interview1);

about bcp

Hi all,

I would like to know about bcp. The input file (in parameter) must to has \t to separate the columns? Is there a way to choice the separator simbol?

for example the file:

1;Test1;active;
2;Test2;active;
3;Test3;inactive;

Yes, there is a command-line parameter "-t" which can be used to specify a field terminator. See Books Online for more details on the command-line options of BCP.

http://msdn2.microsoft.com/en-us/ms162802(SQL.90).aspx