Converting DTS to Stored procedure

Hi,

I created a DTS package for importing a textfile, parse the data and insert it into some tables.

I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...

Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?

First step is to import only row 0 to a table, second step is to import row 1 -> n

Any examples would be nice... :-)bcp or bulk insert...

what's row 0?

Probably a header...

Can you create a "work" table for your own use?

How many rows are we talking about?

Where is the data coming from (it's Access isn't...grrrrrr)

What do you mean by parse (SUBSTRING)?

Does your dog have fleas?

Why is the sky blue?

What's the name of your dba...(just kidding)

Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?

The file contains a header in the first row and then tab separated data...

I have a rawtable that it is imported to now by DTS.

It's about 20000 rows, textfile...

By parsing i mean several queries that translates the data from the rawtable into other tables.

And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...

The sky is blue beacuse God have his blue underwear on...

My dba is named Sven.

--

I have tried to use xp_cmdshell but I'm not allowed too...

Seems like I have to do the import in my client.. written in C++.|||Hi,

You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.

CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

IF @.hr <> 0
BEGIN
RETURN 1
END

GO|||Got a:

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed

--

Seems like I have no permission to run sp_OACreate either...

Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.

Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Converting Datetime from the Varchar value

I am not sure if this is the correct forum to post to for this but,

I have a stored procedure in the code like so:

dim calensql as string = "sp_scheduleworkfromcal '" & sun & "', '" & mon & "', '" & tue & "', '"
& wed & "', '" & thu & "', '" & fri & "', '" & sat & "', '" & Label1.Text & "', '" & Label2.Text & "', '"
& Label3.Text & "', '" & Label4.Text & "', '" & Label5.Text & "', '" & Label6.Text & "', '" & Label7.Text & "', " & "129"

and then in my stored procedure I have

CREATE PROCEDURE sp_scheduleworkfromcal
(@.sun VarChar(50), @.mon VarChar(50), @.tue VarChar(50), @.wed VarChar(50), @.thu VarChar(50),
@.fri VarChar(50), @.sat VarChar(50), @.dsun VarChar(50), @.dmon VarChar(50), @.dtue VarChar(50),
@.dwed VarChar(50), @.dthu VarChar(50), @.dfri VarChar(50), @.dsat VarChar(50), @.userid int)
AS
Declare @.store datetime
If @.sun != '' begin
 Update servicerequests set date_scheduled=(Convert(datetime, @.dsun)) where trackingnumber=@.sun
 Select @.store = rtrim(retailer) + ' ' + rtrim(storeNumber) from servicerequests where trackingnumber = @.sun
 UPDATE CalendarSchedule SET cal_notes=@.store WHERE cal_date=@.dsun AND userid=@.userid
 IF @.@.ROWCOUNT = 0
 INSERT INTO CalendarSchedule (userid, cal_date, cal_notes) VALUES (@.userid, @.dsun, @.store)
End

and I am getting the error something like
Syntax error converting datetime from character string.

If I change the parameters in the stored procedure to datetime or varchar and get rid of the single quotes, I get incorrect sytax near "/".

I am tracking the sql statement to see where I can fix the problem, but cannot come up with a solution.

can anyone help me out with this one??
Thanks
EricI think the date format you are trying to create is wrong ... At the location ... Try investigating the line :
@.store = rtrim(retailer) + ' ' + rtrim(storeNumber)|||I don't know whether I should use datetime in the values or varchar.
In the two tables
The one the field is a datetime field and in the other table it is a Char(15)

why won't it recognize the sql statement
SQL Statement sp_scheduleworkfromcal '897', '', '', '', '903', '', '', '10/19/2003', '10/20/2003', '10/21/2003', '10/22/2003', '10/23/2003', '10/24/2003', '10/25/2003', 129

thats the trace.

E|||I have tried a variation of your code and am not receiving any errors.

Where exactly are you getting the error? Which line number, and what is the error message exactly?

What are the data types and lengths of the following columns?
-- servicerequests.date_scheduled
-- servicerequests.trackingnumber
-- CalendarSchedule.cal_date

Terri|||servicerequests.date_scheduled datetime(8)
servicerequests.trackingnumber bigint(8)
CalendarSchedule.cal_date datetime(8)

I am getting the error on the
cmd.ExecuteNonQuery() line
and the error is as follows:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I changed the values of the textboxes from the page in the sproc from VarChar(50) to datetime as well as the data type in the CalendarSchedule table from Char(15) to datetime and still am getting the same error.

You say you tried a variation of the code? what about it did you change?
are my datatype's wrong?

Thanks
E|||I mean labels from Varchar(50) to datetime - not textboxes

Converting Date Data type in stored procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
@.StDate VARCHAR(16) ,
@.EDate VARCHAR(16),

then I am converting varchar to date with following code

DECLARE @.STARTDATE DATETIME
DECLARE @.ENDDATE DATETIME

SELECT @.STARTDATE = CAST(@.STDATE AS DATETIME)
SELECT @.ENDDATE = CAST(@.EDATE AS DATETIME)

When I try to execute the procedure with following code

execute sp_CalendarCreate @.stdate='12-1-06',@.edate='20-1-06'

but it gives me following error

Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one tell me the solution of that problem (at ur earliest)

regards,

Anas

execute sp_CalendarCreate @.stdate='1-12-06',@.edate='1-20-06'

|||

Thanx for your reply....

yes i had sorted that out b4 u replied me... i.e. sql server uses date format mmddyy or yyyyddmm (american format)

and i was using UK/european standards

btw thanx very much for support

regards,

Anas

|||It is best to use ISO unseparated date format (YYYMMDD) or ISO 8601 (YYYY-MM-DDThh:mm:ss.nnn) datetime format for specifying values. This way you don't have to worry about DATEFORMAT or language settings of the server. Also, using varchar and converting to datetime is not a good and clean approach. Use the correct data type for the values so you can get the best benefit in terms of type checking, domain validations etc. Additionally, with your approach you will get into performance problems because parameter sniffing of the variables used in the SELECT statement will not work due to use of local variables and not parameters. See the whitepaper on compilation, caching in MSDN for more details.

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:
>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
--
Hugo Kornelis, SQL Server MVP|||THANKSsqlsql

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:

>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
Hugo Kornelis, SQL Server MVP|||THANKS

Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))

Convert to Date

I have a variable that I am passing to a stored procedure from a
propritery software application. The variable it passes is the number
of days that have passed since 1/1/1900. I cannot seem to locate a
convert statement that is applicable. Any guidance would be greatly
appreciated.

ThanksDATEADD(DAY,@.var,'19000101')

or

DATEADD(DAY,@.var,'18991231')

depending on whether 1900-01-01 is represented by 0 or 1.

--
David Portas
SQL Server MVP
--

Convert Table in Stored Procedure

I have a flat table from 1 of the clients with a lot of fields (more then 100) like this

Item F1 F2 F3 ... F(N)
--------------
100 X X
101 X

There are more 10000 records , X is the data inside the field.

I need to quickly convert it to this table

Item FieldNumber Value
---------
100 1 X
100 2 X
101 1 X

Any ideas ?

Thanks

MikhailHi,

My suggestion would be export the table into Excel, in Excel using formula, you can change either from horizontal to vertical or likewise.

After that, you can import back to database.

Regards
Ravi|||I am not sure what are you proposing..how will I do it in Excel ?

Any other ideas ?

Mikhail|||You can self join for each column

Select
Item,
FieldNumber.F1,
MyValue.F2
from
Table FieldNumber inner join Table MyValue on FieldNumber.Item = MyValue.Item

get the idea?

convert stored procedures returned output to varchar from int?

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(

Syntax error converting the varchar value 'ABC' to a column of data type int.

SQL file

======================

DECLARE @.S AS VARCHAR(1000)

EXEC @.S = PDT.DBO.SP_RPT
'ABC'

SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email

EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S

======================

Stored Procedure

======================

CREATE procedure sp_RPT
( @.array varchar(1000) )
AS

DECLARE @.content AS VARCHAR(2000)

SET @.content = 'RPT: ' + @.array + ' loaded successfully '

SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)

GO

try

cast( @.array as varchar(1000))

Hope this help

|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Convert Problem

Hello,

I have a stored procedure in SQL Server 2000 and I am trying to do something like this for example:
declare @.s varchar(50), @.i smallint

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

The problem is what can I do if the value of @.s is '12.35'? This generates the 'Syntax error converting the varchar value to a column of data type smallint.' because the numeric value is actually decimal and the error is not caught by the @.@.error check.
The only alternative I can think of is to convert @.s to decimal the convert again but this is not suitable as this is for validation purposes and if the value is decimal I need to identify it and ignore it.

Is there any way to check for or handle this scenario?

Thanks for any help.
ACWhy are you checking to see if a VARCHAR(50) field contains a number?
But hey, I'm sure you know what you're doing...

if isnumeric(@.s) = 1
begin
select @.i = convert(smallint, @.s)
end
if @.@.error <> 0
print 'error'
end
else begin
print 'No error'
end|||declare @.s varchar(50), @.i smallint

set @.s = '12.35'

if isnumeric(@.s) = 1
begin
select @.i = floor(@.s) -- round(@.s, 10, 0)
if @.@.error <> 0
print 'error'
end
else
print 'No error'

select @.i|||Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||Thanks for the input Peso.
However, this is not what I want to do. For validation purposes I want to catch this value if it is invalid and ignore it, not attempt to convert it to a valid value.|||Thanks georgev but this doesn't make a difference.
The error returns before the process gets to checking @.@.error.

Please note that the differences in the statement set up.

I have separated the Isnumeric into it's own If.|||I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||Thanks Pat, I should be able to use some pattern matching.

I'd suggest using:DROP FUNCTION dbo.IsInt
GO
-- ptp 20070913 Test to see if a string could be an MS-SQL integer

CREATE FUNCTION dbo.IsInt(
@.pcArg NVARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE
@.result INT
, @.work BIGINT

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = 0 -- Invalid character(s)
ELSE IF 22 < DataLength(@.pcArg) SET @.result = 0 -- Impossibly long
ELSE IF @.pcArg LIKE '[0-9]' SET @.result = 1 -- Singleton digit
ELSE IF @.pcArg LIKE '[-+]%[-+]%' SET @.result = 0 -- Multiple signs
ELSE
BEGIN -- Clean input
SET @.work = Convert(BIGINT, @.pcArg)
IF @.work BETWEEN -2147483648 AND 2147483647
SET @.result = 1 -- Passed range check
ELSE
SET @.result = 0 -- Out of range
END

RETURN @.result
END
GO

SELECT c, dbo.IsInt(c)
FROM (SELECT '99999999999999999999' AS c
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS zThis probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

-PatP|||I was surprised by your response, then realized that I'd written the function for INT and you wanted SMALLINT. You only need to change one line to:IF @.work BETWEEN -32768 AND 32767and you're "good to go" for SMALLINT testing.

Sorry about that oversight!

-PatP|||I think that I've found a better answer. This function checks a string argument to see if it can be an MS-SQL integer value, and it returns a string with a character for each data type that the argument could be. This won't win any speed prizes, but it avoids at least two of the pitfalls that I found in the prior function and it provides more functionality too.DROP FUNCTION dbo.IntegerTypes
GO
-- ptp 20070913 Return which types of MS-SQL integer the argument could be

CREATE FUNCTION dbo.IntegerTypes(
@.pcArg NVARCHAR(39)
)
RETURNS VARCHAR(8)
AS BEGIN
DECLARE
@.result VARCHAR(8)
, @.work NUMERIC(38)

SET @.pcArg = Rtrim(LTrim(@.pcArg))
IF @.pcArg LIKE '%[^-+0-9]%' SET @.result = '' -- Invalid character(s)
ELSE IF @.pcArg NOT LIKE '%[0-9]%' SET @.result = '' -- No digits
ELSE IF @.pcArg LIKE '[-+0-9]%[-+]%' SET @.result = '' -- Sign after sign or digit
ELSE IF 78 < DataLength(@.pcArg) SET @.result = '' -- Impossibly long
ELSE IF 78 = DataLength(@.pcArg) AND @.pcArg NOT LIKE '[-+]%' SET @.result = ''
ELSE
BEGIN -- Clean input
SET @.result = 'N'
SET @.work = @.pcArg
IF @.work BETWEEN -9223372036854775808 AND 9223372036854775807 SET @.result = @.result + 'B' -- BIGINT
IF @.work BETWEEN -2147483648 AND 2147483647 SET @.result = @.result + 'I' -- INT
IF @.work BETWEEN -32768 AND 32767 SET @.result = @.result + 'S' -- SMALLINT
IF @.work BETWEEN 0 AND 255 SET @.result = @.result + 'T' -- TINYINT
END

RETURN @.result
END
GO

SELECT c, dbo.IntegerTypes(c)
FROM (SELECT '9999999999999999999999999999999999999999' AS c
UNION ALL SELECT '99999999999999999999999999999999999999'
UNION ALL SELECT '+99999999999999999999999999999999999999'
UNION ALL SELECT '-99999999999999999999999999999999999999'
UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
UNION ALL SELECT '2147483647' UNION ALL SELECT '2147483648'
UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
) AS z-PatP|||WHERE Col1 NOT LIKE '%[^0-9]%'|||WHERE Col1 NOT LIKE '%[^0-9]%'SELECT Replicate('9', 99)-PatP

Convert Oracle proc. to SQL Server Procedure

Hi,
Can any one change this oracle proc. to SQL Server procedure.

Any help will be appreciated.

PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);

IF Contr_tot_all = 0 THEN

Select Contr_type into sContractType from ae_contract where db_contract = db_id;

IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;

ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;be patient i'm gonna try to work this tonight

but in the meantime if you could provide some table ddl, that would be good.|||Some information about [http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm] task.

HTH

Convert NTEXT to XML for use in following query.

[Apologies for the cross-post]
Hi,
I have the following query in a stored procedure, where @.In_IDs is of type
XML:
SELECT Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')
However, sometimes I'm sending a lot of IDs (possibly a few thousand) and
the resulting XML document seems to become truncated. i.e. if I send 1,000,
I will get back 950 records. What I would like to do is pass in an NTEXT
field and convert this to XML in order to do the join on the full set. Any
ideas how I do this?
Thanks
RobinHello Robin,

> However, sometimes I'm sending a lot of IDs (possibly a few thousand)
> and the resulting XML document seems to become truncated. i.e. if I
> send 1,000, I will get back 950 records. What I would like to do is
> pass in an NTEXT field and convert this to XML in order to do the join
> on the full set. Any ideas how I do this?
Here's one way to do that.
use scratch
go
create table dbo.objects(id int,descr nvarchar(200))
create table dbo.ids(list ntext)
go
insert into dbo.objects values (1,'apple')
insert into dbo.objects values (2,'banana')
insert into dbo.objects values (3,'cherry')
insert into dbo.objects values (4,'durian')
go
insert into dbo.ids(list) values ('<ids><id>1</id><id>2</id><id>3</id><id>4<
/id></ids>')
go
declare @.x xml
select @.x = list from dbo.ids
;with l(id) as (select t.c.value('.','int')
from @.x.nodes('//id') as t(c))
select l.id,o.descr from l join dbo.objects o on l.id = o.id
go
drop table dbo.ids
drop table dbo.objects
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent, thanks for your response. I think some misunderstanding here
though with your answer - I can't quite see how to make that work. I have
the following (below). My problem is with the truncation of @.In_IDs (the
XML parameter). I want to replace it with an NTEXT parameter and then
somehow prepare it as an XML document in order to perform the query. I'm
passing in an XML list of ID's and want to return the record for each of
those IDs but I think the limit on the size of the XML document prevents the
code below from working when the number of ID's is largeish.
Robin
CREATEPROCEDURE [dbo].[Image_Get_Set_Details]
@.In_IDs XML /* The IDs of the image whose details we want to fetch
*/
/*
Procedure expects XML in the following format:
<query>
<id>1023</id>
<id>1024</id>
<id>1025</id>
</query>
*/
AS
SELECT Images.ID_Adjacency,
Images.IsCompressed,
Images._Timestamp
FROM
Images
CROSS APPLY
@.In_IDs.nodes('//id') AS T(nref)
WHERE
Images.ID_Adjacency = nref.value('.', 'int')|||Hello Robin,
The XML DataType supports instances up to two 2gb so that seems unlikely.
NTEXT does the same thing, but it is depreciated so I'd avoid using it. Here
's
an example that does essentially the same work your looking for. It works
for me for lists from 100 to 1000000 elements.
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.tlist nvarchar(max))
as begin
set nocount on
declare @.list xml
set @.list = @.tlist
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.olist nvarchar(max)
select @.olist = convert(nvarchar(max),( select v as id from dbo.test order
by v for xml path(''),root('query'),type))
exec dbo.GetVs @.olist
go
drop table dbo.test
drop proc dbo.GetVs
go
Note that converting the list to nvarchar(max) isn't required as all. This
also works:
use scratch
go
create table dbo.test(id int identity(1,1) primary key clustered,v int)
go
set nocount on
declare @.l int
set @.l = 100000
while @.l > 0
begin
insert into dbo.test values (@.l)
set @.l = @.l-1
end
go
create procedure dbo.GetVs(@.list xml)
as begin
set nocount on
select @.list.value('count(//id)','int') as [count],datalength(@.list) as
[size]
select t.c.value('.','int') as ID,t1.v
from dbo.test t1
cross apply @.list.nodes('//id') as t(c)
where t1.id = t.c.value('.','int')
end
go
declare @.list xml
select @.list = ( select v as id from dbo.test order by v for xml path(''),ro
ot('query'),type)
exec dbo.GetVs @.list
go
drop table dbo.test
drop proc dbo.GetVs
go
What you might want to do is test that you're getting all the nodes in the
list that you think you are, as I do with the XQuery count select.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent. It turns out I had a bug somewhere which lead me to believe
there was a limit on XML parameter size, totally unrelated to my sproc.
Sorry to have wasted your time.
Robin

Convert Long To DateTime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date

LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400

End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in an SQL stored procedure?

Thanks in advance
Wesley

translation of your vb function should be

CREATE FUNCTION fn_LongToTime (@.lTime int)

RETURNS smalldatetime

AS

BEGIN

DECLARE @.dt smalldatetime

Select @.dt = convert(smalldatetime, floor(@.ts / 10000) / 24 + Floor((@.ts % 10000) / 100) / 1440) + (@.ts % 100) / 86400)

RETURN @.Kg

END

GO

You have to verify smalldatetime conversion is right...

Convert Long to Datetime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date
LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400
End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in SQL?

Thanks
Wes

hi

there is no mod function in sql server

you have to use %

i think you can go ahead from here

regards

% (Modulo)

Provides the remainder of one number divided by another.

Syntax

dividend % divisor

Arguments

dividend

Is the numeric expression to divide. dividend must be any valid Microsoft? SQL Server? expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

Result Types

int

Remarks

The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

Examples

This example returns the book title number and any modulo (remainder) of dividing the price (converted to an integer value) of each book into the total yearly sales (ytd_sales * price).

USE pubs

GO

SELECT title_id,

CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo

FROM titles

WHERE price IS NOT NULL and type = 'trad_cook'

ORDER BY title_id

GO

|||declare @.lTime as int

select @.lTime = 153459

select convert(datetime, stuff(stuff(convert(varchar(6), @.lTime), 3, 0, ':'), 6, 0, ':'))

result : 1900-01-01 15:34:59.000

Convert IP Address to Long

Hi, can I use a SQL server Stored Procedure to convert an IP Address to Long?

In VB.NET I use the following code (hope that helps).

Private Function ConvertToLong(ByVal IPAddress As Object) As Object
Dim x As Integer
Dim Pos As Integer
Dim PrevPos As Integer
Dim Num As Integer
If UBound(Split(IPAddress, ".")) = 3 Then
' On Error Resume Next
For x = 1 To 4
Pos = InStr(PrevPos + 1, IPAddress, ".", 1)
If x = 4 Then Pos = Len(IPAddress) + 1
Num = Int(Mid(IPAddress, PrevPos + 1, Pos - PrevPos - 1))
If Num > 255 Then
ConvertToLong = "0"
Exit Function
End If
PrevPos = Pos
ConvertToLong = ((Num Mod 256) * (256 ^ (4 - x))) + ConvertToLong
Next
End If
End Function

Here's a UDF that should do what you are looking for.

Usage: SELECT dbo.fnStringIPToLongIP('192.168.0.1')

CREATE FUNCTION dbo.fnStringIPToLongIP (@.IPAddress AS varchar(15))
RETURNS bigint AS
BEGIN
DECLARE
@.x Integer,
@.Pos Integer,
@.PrevPos Integer,
@.Num Integer,
@.ConvertToLong bigint
SET @.ConvertToLong = 0
IF LEN(RTRIM(REPLACE(@.IPAddress,'.',''))) = LEN(RTRIM(@.IPAddress))-3
BEGIN
SET @.X = 1
SET @.PrevPos = 0
WHILE @.X <= 4
BEGIN
SET @.Pos = CHARINDEX('.',@.IPAddress,@.PrevPos + 1)
IF @.x = 4
SET @.Pos = Len(@.IPAddress) + 1
SET @.Num = SUBSTRING(@.IPAddress, @.PrevPos + 1, @.Pos - @.PrevPos - 1)
If @.Num > 255
BEGIN
SET @.ConvertToLong = '0'
SET @.X = 5
BREAK
END
SET @.PrevPos = @.Pos
SET @.ConvertToLong = ((@.Num % 256) * CAST(POWER(256,(4 - @.x)) AS bigint)) + @.ConvertToLong
SET @.X = @.X + 1
END
END
RETURN(@.ConvertToLong)
END

Terri|||Thanks a million!!!|||FYI...The EasyWay.NET

Dim lng As Long = System.Net.IPAddress.Parse("192.168.0.1").Address

convert input parameter into field

create procedure [dbo].[findtext]
(
@.fieldname nvarchar(50),
@.searchtext nvarchar(50)
)
AS
SELECT * FROM tablename WHERE @.fieldname = @.searchtext
it doesn't work!Hi Joe,
Use dynamic SQL to build the SQL statement, something like this:
DECLARE @.sql nvarchar(200)
Set @.sql = 'SELECT * FROM tablename WHERE ' + @.fieldname + ' = ''' +
@.searchtext + ''''@.searchtext '''
EXEC(@.sql)
Ray
"joe" wrote:

> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||joe
You will have to use dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||Like rb and Uri said, dynamic SQL. Be careful to validate your data though,
since that could open you up to SQL injection attacks.
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!

Convert Informix to SQL Server

Hello, i need to convert this store procedure from Informix to SQL
Server and only obtain error's. Can anyone help me?
CREATE PROCEDURE atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
DECLARE @.aux_numcarta INT,
@.aux_numcarta1 INT,
@.var_medant LIKE mudmed.medant,
@.var_cartatipo LIKE mudmed.cartatipo,
@.var_ramo LIKE mudmed.ramo,
@.var_apolice LIKE mudmed.apolice,
@.var_dataalter LIKE mudmed.dataalter,
@.var_registo LIKE mudmed.registo,
@.aux_medant LIKE mudmed.medant,
@.aux_cartatipo LIKE mudmed.cartatipo,
@.aux_ramo LIKE mudmed.ramo,
@.aux_apolice LIKE mudmed.apolice,
@.aux_dataenvio LIKE mudmed.dataenvio,
@.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
@.ja_esta_em_trans INT,
@.sql_error_num INT,
@.isam_error_num INT,
@.error_msg VARCHAR(100)
--ON EXCEPTION
-- SET sql_error_num, isam_error_num, error_msg
IF ja_esta_em_trans <> 1
ROLLBACK WORK
RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
END EXCEPTION;
LET ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
LET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF aux_numcarta IS NULL
LET aux_numcarta = 0
IF aux_numcarta1 IS NULL
LET aux_numcarta1 = 0
IF aux_numcarta1 > aux_numcarta
LET aux_numcarta = aux_numcarta1
END IF
LET @.aux_medant = -1;
LET @.aux_cartatipo = -1;
LET @.aux_ramo = -1;
LET @.aux_apolice = -1;
LET @.aux_numcarta1 = -1;
LET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999))
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo
LET aux_medant = var_medant;
LET aux_cartatipo = var_cartatipo;
LET aux_numcarta = aux_numcarta + 1;
LET aux_numcarta1 = aux_numcarta;
IF aux_cartatipo == var_cartatipo AND
aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
LET aux_dataenvio = NULL;
ELSE
LET aux_ramo = var_ramo;
LET aux_apolice = var_apolice;
LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END IF;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
Thanks,
Apaxe2000If you understand t-SQL as well as the Informix's SQL dialect, it shouldn't
be hard. Instead of translating line by line, understand the overall logic
in the stored procedure and re-write using t-SQL.
Anith|||But the problem is i don't know SQL Server dialect and need to convert
this.
This is the only thing i don't know how resolve in the project i have
to do. Programming in VB is easy. Programming in SQL Server not.|||Apaxe2000 wrote:

> But the problem is i don't know SQL Server dialect and need to convert
> this.
> This is the only thing i don't know how resolve in the project i have
> to do. Programming in VB is easy. Programming in SQL Server not.
You need to spec the problem properly and work from a spec rather than
someone else's code. At least that may be what you'll have to do if you
want an answer from this forum.
The code you posted appears to be a cursor and that probably isn't the
best way to accomplish the same thing in SQL Server. But without more
information on keys, constraints and your data it's difficult to give
you a good answer.
The following article explains the best way to describe your problem
here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||It seems that you've tried converting the procedure but you are getting
errors.
Why not show us what you've done and the errors that you're getting.
You would have to be lucky to find someone here that knows both dialects and
is inclined to do all the work for you.
Besides, there are few comments in the procedure showing us what's going on
and we don't know your table structures.
@.var_medant LIKE mudmed.medant - nothing like this in SQL. You'll have to
use the parameter name @.var_medant in the procedure
LET = SET in SQL server
http://www.sommarskog.se/ for error handling in SQL server
FOREACH would be a Cursor in SQL server although you may be able to write
this without one
CURRENT = getdate()
"Apaxe2000" <Apaxe2000@.gmail.com> wrote in message
news:1135096937.302611.60280@.g14g2000cwa.googlegroups.com...
> Hello, i need to convert this store procedure from Informix to SQL
> Server and only obtain error's. Can anyone help me?
> CREATE PROCEDURE atribui_num_carta
> @.dia_env INT,
> @.mes_env INT,
> @.ano_env INT
> DECLARE @.aux_numcarta INT,
> @.aux_numcarta1 INT,
> @.var_medant LIKE mudmed.medant,
> @.var_cartatipo LIKE mudmed.cartatipo,
> @.var_ramo LIKE mudmed.ramo,
> @.var_apolice LIKE mudmed.apolice,
> @.var_dataalter LIKE mudmed.dataalter,
> @.var_registo LIKE mudmed.registo,
> @.aux_medant LIKE mudmed.medant,
> @.aux_cartatipo LIKE mudmed.cartatipo,
> @.aux_ramo LIKE mudmed.ramo,
> @.aux_apolice LIKE mudmed.apolice,
> @.aux_dataenvio LIKE mudmed.dataenvio,
> @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp,
> @.ja_esta_em_trans INT,
> @.sql_error_num INT,
> @.isam_error_num INT,
> @.error_msg VARCHAR(100)
> --ON EXCEPTION
> -- SET sql_error_num, isam_error_num, error_msg
> IF ja_esta_em_trans <> 1
> ROLLBACK WORK
>
> RAISE EXCEPTION sql_error_num, isam_error_num, error_msg;
> END EXCEPTION;
> LET ja_esta_em_trans = 0;
> BEGIN
> ON EXCEPTION IN (-535)
> LET ja_esta_em_trans = 1;
> END EXCEPTION;
> BEGIN WORK;
> END
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
> FROM mudmedhist_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
> IF aux_numcarta IS NULL
> LET aux_numcarta = 0
> IF aux_numcarta1 IS NULL
> LET aux_numcarta1 = 0
> IF aux_numcarta1 > aux_numcarta
> LET aux_numcarta = aux_numcarta1
> END IF
>
> LET @.aux_medant = -1;
> LET @.aux_cartatipo = -1;
> LET @.aux_ramo = -1;
> LET @.aux_apolice = -1;
> LET @.aux_numcarta1 = -1;
> LET aux_dataenvio_comp = CURRENT;
> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
> IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
> OR ((var_medant>=800000 AND var_medant<=899999)
> OR (var_medant>=5003000 AND var_medant<=5003500) OR
> (var_medant>=5012000 AND var_medant<=5012999))
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta1 = -1;
> ELSE
> IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
> aux_cartatipo <> var_cartatipo
> LET aux_medant = var_medant;
> LET aux_cartatipo = var_cartatipo;
> LET aux_numcarta = aux_numcarta + 1;
> LET aux_numcarta1 = aux_numcarta;
> IF aux_cartatipo == var_cartatipo AND
> aux_ramo = var_ramo AND aux_apolice = var_apolice THEN
> LET aux_dataenvio = NULL;
> ELSE
> LET aux_ramo = var_ramo;
> LET aux_apolice = var_apolice;
> LET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
> END IF;
> UPDATE mudmed
> SET numcarta = aux_numcarta1,
> dataenvio = aux_dataenvio,
> dataenvio_comp = aux_dataenvio_comp
> WHERE registo = var_registo;
> END FOREACH ;
> IF ja_esta_em_trans <> 1 THEN
> COMMIT WORK;
> END IF;
> END PROCEDURE;
> Thanks,
> Apaxe2000
>|||Hi.
This is the procedure i used:
CREATE PROCEDURE dbo.atribui_num_carta
@.dia_env INT,
@.mes_env INT,
@.ano_env INT
AS
DECLARE @.aux_numcarta INT
DECLARE @.aux_numcarta1 INT
DECLARE @.var_medant LIKE mudmed.medant
DECLARE @.var_cartatipo LIKE mudmed.cartatipo
DECLARE @.var_ramo LIKE mudmed.ramo
DECLARE @.var_apolice LIKE mudmed.apolice
DECLARE @.var_dataalter LIKE mudmed.dataalter
DECLARE @.var_registo LIKE mudmed.registo
DECLARE @.aux_medant LIKE mudmed.medant
DECLARE @.aux_cartatipo LIKE mudmed.cartatipo
DECLARE @.aux_ramo LIKE mudmed.ramo
DECLARE @.aux_apolice LIKE mudmed.apolice
DECLARE @.aux_dataenvio LIKE mudmed.dataenvio
DECLARE @.aux_dataenvio_comp LIKE mudmed.dataenvio_comp
DECLARE @.ja_esta_em_trans INT
DECLARE @.sql_error_num INT
DECLARE @.isam_error_num INT
DECLARE @.error_msg VARCHAR(100)
IF @.ja_esta_em_trans <> 1 BEGIN
ROLLBACK WORK
RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
END EXCEPTION;
SET @.ja_esta_em_trans = 0;
BEGIN
ON EXCEPTION IN (-535)
SET ja_esta_em_trans = 1;
END EXCEPTION;
BEGIN WORK;
END
SELECT Max(M1.NUMCARTA) INTO aux_numcarta
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
SELECT Max(M1.NUMCARTA) INTO aux_numcarta1
FROM mudmedhist_grupo AS M1
WHERE Year(M1.DATAENVIO) = ano_env;
IF (aux_numcarta) IS NULL BEGIN
SET @.aux_numcarta = 0
END
IF aux_numcarta1 IS NULL BEGIN
SET @.aux_numcarta1 = 0
END
IF aux_numcarta1 > aux_numcarta
SET @.aux_numcarta = @.aux_numcarta1
END
SET aux_medant = -1;
SET aux_cartatipo = -1;
SET aux_ramo = -1;
SET aux_apolice = -1;
SET aux_numcarta1 = -1;
SET aux_dataenvio_comp = CURRENT;
FOREACH
SELECT medant, cartatipo, ramo, apolice, dataalter, registo
INTO var_medant, var_cartatipo, var_ramo, var_apolice,
var_dataalter, var_registo
FROM mudmed_grupo
WHERE (numcarta IS NULL)
ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
registo DESC
IF var_cartatipo < 1 OR var_cartatipo > 3 OR var_medant IS NULL
OR ((var_medant>=800000 AND var_medant<=899999)
OR (var_medant>=5003000 AND var_medant<=5003500) OR
(var_medant>=5012000 AND var_medant<=5012999)) BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta1 = -1;
ELSE
IF aux_numcarta1 = -1 OR aux_medant <> var_medant OR
aux_cartatipo <> var_cartatipo BEGIN
SET aux_medant = var_medant;
SET aux_cartatipo = var_cartatipo;
SET aux_numcarta = aux_numcarta + 1;
SET aux_numcarta1 = aux_numcarta;
END
IF aux_cartatipo == var_cartatipo AND aux_ramo = var_ramo AND
aux_apolice = var_apolice BEGIN
SET aux_dataenvio = NULL;
ELSE
SET aux_ramo = var_ramo;
SET aux_apolice = var_apolice;
SET aux_dataenvio = MDY( mes_env, dia_env, ano_env );
END;
UPDATE mudmed
SET numcarta = aux_numcarta1,
dataenvio = aux_dataenvio,
dataenvio_comp = aux_dataenvio_comp
WHERE registo = var_registo;
END FOREACH ;
IF ja_esta_em_trans <> 1 THEN
COMMIT WORK;
END IF;
END PROCEDURE;
I obtain this errors:
Error 156: Incorrect Syntax near the keyword 'LIKE'
Line 32: Incorrect Syntax near 'EXCEPTION'
Incorrect Syntax near the keyword 'ON'
Incorrect Syntax near the keyword 'END'
Line 75: Incorrect Syntax near ','
Line 82: Incorrect Syntax near '='
Line 87: Incorrect Syntax near '='
Line 94: Incorrect Syntax near '='
Line 95: Incorrect Syntax near '='
Line 108: Incorrect Syntax near 'FOREACH'
Incorrect Syntax near the keyword 'END'
The variable are:
LIKE mudmed.medant -> integer
LIKE mudmed.cartatipo -> smallint
LIKE mudmed.ramo -> smallint
LIKE mudmed.apolice -> smallint
LIKE mudmed.dataalter -> datetime
LIKE mudmed.registo -> integer
LIKE mudmed.dataenvio -> datetime
LIKE mudmed.dataenvio_comp -> datetime
Thanks for your help,
Apaxe2000|||Apaxe2000 (Apaxe2000@.gmail.com) writes:
> This is the procedure i used:
I don't know if you seriously expect someone to rewrite your Informix
code to SQL Server for you for free. Since I don't know Informix, I
don't know how far from Informix you have strayed, but you have quite
some way to go, before you are in SQL Server land.
If you are not interesting in learning T-SQL, and this is just a one-off,
I suggest that you try the local phonebook for consultants.
If you want to learn SQL Server, I will give you some hints.

> DECLARE @.var_medant LIKE mudmed.medant
Oracle has a similar feature, but SQL Server does not. You can only
declare a variable to be of a certain type. You can create your
own data-type alias, so that you can say:
EXEC sp_addtype mytype, 'varchar(12)'
and then you can can use mytype both in tables and for variables. But
I suspect that this does not help you here, as your tables supposedly
already exists. You will have to declare @.var_medant explicitly as
whatever type you need.

> IF @.ja_esta_em_trans <> 1 BEGIN
> ROLLBACK WORK
> RAISERROR (@.sql_error_num, @.isam_error_num, @.error_msg);
Note that if you pass a number to RAISERROR, this must be a number >= 50000,
and should have been defined with sp_addmessage. The most common is to
pass a string to RAISERROR with the error message.
The other two parameters are severity and state. Severity should be >= 11
and <= 16 and state is best set to 1.

> BEGIN
> ON EXCEPTION IN (-535)
> SET ja_esta_em_trans = 1;
> END EXCEPTION;
This syntax is not in SQL Server. You did not say which SQL Server you
are using, and this is an area where there are great difference. In SQL
2005, you can say:
BEGIN TRY
.. statements here
END TRY
BEGIN CATCH
.. error handling here
END CATCH
But in SQL 2000, you have no other choice than checking the global
variable @.@.error after each statement. Notice that this variable changes
value after each statement, so you need to put it into a local variable.
Also, note that in SQL 2000, there are errors you cannot catch at all,
since they abort the batch and rolls back the current transaction.
As for what you should check for in your error handler, I don't know,
but -535 is not an error number in SQL Server.

> SELECT Max(M1.NUMCARTA) INTO aux_numcarta
> FROM mudmed_grupo AS M1
> WHERE Year(M1.DATAENVIO) = ano_env;
I don't know what this statement does in Informnix, but in SQL Server
it creates a table, and it fails since you did not provide a name
for the column. If aux_numcarta is a variable, the syntax is
SELECT @.aux_numcarta = Max(M1.NUMCARTA)
FROM mudmed_grupo AS M1
WHERE Year(M1.DATAENVIO) = @.ano_env;
Assuming that DATAENVIO is indexes, the WHERE clause is best written as
WHERE M1:DATAENVIO BETWEEN ltrim(str(@.ano_env)) + '0101' AND
ltrim(str(@.ano_env)) + '1231'
This is because, if you put an indexed column into an expresson, you
nullify the benefit of the index. (I would suspect that this applies to
Informix as well.)

> SET aux_dataenvio_comp = CURRENT;
This means nothing in SQL Server.

> FOREACH
> SELECT medant, cartatipo, ramo, apolice, dataalter, registo
> INTO var_medant, var_cartatipo, var_ramo, var_apolice,
> var_dataalter, var_registo
> FROM mudmed_grupo
> WHERE (numcarta IS NULL)
> ORDER BY medant, cartatipo, ramo, apolice, dataalter DESC,
> registo DESC
I don't know what this FORACH means, but it looks like a syntax for a
cursor. But looking at the code, I can't see anything that calls for
an iteration at all. You should proably rewrite this as a single
UPDATE statement in Informix first.
The CASE expression can be handy:
SELECT @.x = CASE WHEN @.y > 10 THEN 234
WHEN @.y > 6 AND @.w > 12 THEN 123
ELSE -23
END
This is ANSI, so it should work on Informix as well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||At 15/01/06 i go to another project in Oracle and VB.NET but i need to
do this before, but i continue obtain errors and can anyone helpme only
this time?
Thanks,
Apaxe2000|||Apaxe2000 wrote:
> At 15/01/06 i go to another project in Oracle and VB.NET but i need to
> do this before, but i continue obtain errors and can anyone helpme only
> this time?
> Thanks,
> Apaxe2000
Do you really think that "continue obtain errors" is an adequate
specification of a problem? No-one's likely to be able to offer much
help without a fuller description.
I don't want to give you bad advice based on my wild guesses about the
meaning of your legacy code. That's why I asked for the extra
information I did and gave you the article explaining how to do it. You
still haven't provided any DDL, sample data or even shown us what end
result you want. If you take the time to do that you'll be pretty sure
to get more responses.
David Portas
SQL Server MVP
--

Convert Informix Stored Procedure

I would like to convert a couple informix stored procedures to SQL
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.

drop function mnaf_calc_calendar_quarter;

CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_quarter
-- Description/Notes:
-- Calculates the most recent calendar quarter based on the end date.
--
-- Parms:
-- End Date.
-- Returns:
-- The calculated period start date and end date.
--
--************************************************** ***************************
-- Revisions:
-- PgmrDate # Description
-- HPI05/03/2005
--************************************************** ***************************

RETURNING date, date;

DEFINE dtStartDate date;
DEFINE dtEndDate date;

LET dtStartDate = mdy(12,31,1899);
LET dtEndDate = pEndDate;

-- If the end date parameter is equal to a calendar quarter,
-- calculate the start date by subtracting three months.
IF month(pEndDate) = 3 or month(pEndDate) = 6 or
month(pEndDate) = 9 or month(pEndDate) = 12 then

LET dtEndDate = pEndDate;

ELSE

-- Otherwise find the closest previous calendar quarter end date
-- then calculate the start date.
IF month(pEndDate) = 1 or month(pEndDate) = 4 or
month(pEndDate) = 7 or month(pEndDate) = 10 then

-- Subtract 1 month off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
month);

ELSE

-- Month must be equal to 2, 5, 8, 11
-- Subtract 2 months off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
month);

END IF;

END IF;

-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);

RETURN dtStartDate, dtEndDate;

END FUNCTION;

grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.com...
>I would like to convert a couple informix stored procedures to SQL
> Server stored procedures. I have no idea how to accomplish this. Here
> is an example of one of the procedures I need to convert.
> drop function mnaf_calc_calendar_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_quarter
> -- Description/Notes:
> -- Calculates the most recent calendar quarter based on the end date.
> --
> -- Parms:
> -- End Date.
> -- Returns:
> -- The calculated period start date and end date.
> --
> --************************************************** ***************************
> -- Revisions:
> -- Pgmr Date # Description
> -- HPI 05/03/2005
> --************************************************** ***************************
> RETURNING date, date;
> DEFINE dtStartDate date;
> DEFINE dtEndDate date;
> LET dtStartDate = mdy(12,31,1899);
> LET dtEndDate = pEndDate;
> -- If the end date parameter is equal to a calendar quarter,
> -- calculate the start date by subtracting three months.
> IF month(pEndDate) = 3 or month(pEndDate) = 6 or
> month(pEndDate) = 9 or month(pEndDate) = 12 then
> LET dtEndDate = pEndDate;
> ELSE
> -- Otherwise find the closest previous calendar quarter end date
> -- then calculate the start date.
> IF month(pEndDate) = 1 or month(pEndDate) = 4 or
> month(pEndDate) = 7 or month(pEndDate) = 10 then
> -- Subtract 1 month off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
> month);
> ELSE
> -- Month must be equal to 2, 5, 8, 11
> -- Subtract 2 months off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_quarter to public;

See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
will probably be the ones you're looking for. This article might also be
useful for general background information about manipulating datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon