Creating an expression - how to concatinate

I'm creating an expression in a textbox in a table.
I would like to concatinate two expressions together.
What function can do this?Jim, you can use the ampersand (&) to concatinate items. For example,
fieldname & "/" & fieldname
"Jim" wrote:
> I'm creating an expression in a textbox in a table.
> I would like to concatinate two expressions together.
> What function can do this?sql

Creating an aggregate function

Hi,
Is there a way to create an aggeregate function in MS SQL?
For example, if you wanted to re-implement fucntion SUM(), how would you do
that?
Any ideas would be highly appreciated!
Thank you,
AndreyThis functionality is planned for the upcoming release of SQL Server 2005.
You can write aggregate functions in a CLR language.
Hope this helps.
Dan Guzman
SQL Server MVP
"MuZZy" <leyandrew@.yahoo.com> wrote in message
news:TJCdnXEc-KWYpKffRVn-pA@.comcast.com...
> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you
> do that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||You won't be able to do that with SQL Server 2000, unfortunately. Stay
tuned for SQL Server 2005.
On 3/17/05 9:12 PM, in article TJCdnXEc-KWYpKffRVn-pA@.comcast.com, "MuZZy"
<leyandrew@.yahoo.com> wrote:

> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you d
o
> that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||Andrey,
As others mentioned, no support for creating new custom aggregates, but
there are several techniques that you can implement in SQL Server 2000.
These include Pivoting if the number of elements is small enough, and other
techniques for a large number of elements depending on the specific
calculation you are trying to achieve.
If you have a specific custom aggregate you want to calculate, and would
like to share, maybe we can come up with a solution.
BG, SQL Server MVP
www.SolidQualityLearning.com
"MuZZy" <leyandrew@.yahoo.com> wrote in message
news:TJCdnXEc-KWYpKffRVn-pA@.comcast.com...
> Hi,
> Is there a way to create an aggeregate function in MS SQL?
> For example, if you wanted to re-implement fucntion SUM(), how would you
> do that?
> Any ideas would be highly appreciated!
> Thank you,
> Andrey|||>>For example, if you wanted to re-implement function SUM(), how would
you do that? <<
You can put expressions inside the existing aggregate functions and do
quite a lot. For example, you can google some old posts on how to
write a product aggregate with logs and exponent functions.
If you have a particular one you want to implement, post a spec and we
will see what we can do.

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.
If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with som
e
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:

> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>sql

Creating a VIEW based on other VIEW.

Hi everyone,
Let's say I have a VIEW that is created by some complex joins - This
VIEW is used by many of the application's function. Let's call this
"VIEW_1".
"VIEW_1" select queries are already created using "with (nolock)".
Let's say a new system requirement comes in - I notice that it may be
better if i create another view "VIEW_2" based on "VIEW_1".
eg:
create view VIEW_2
as
(
select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
)
For such cases, what are the performance issues I should watch out
for? WIll there be any performance issues? Should I be doing this in
the first place?
In SQL 2005 mgmt studio, how do I view the execution paths and
timings?
Please advise.
Thanks.If your query completes in some reasonable time and does not take too much
resources that may be needed for other users then it is not a problem.
You can take a look at the performance and resources used by your query by
using all or some of these:
set statistics io on
set statistics time on
set statistics profile on
set statistics xml on
You can also display the graphic execution plan in SSMS by using the buttons
'Include Actual Execution Plan' or 'Display Estimated Execution Plan'. Text
and XML execution plans are also available.
You can also use some DMVs or SQL Server predefined reports (like
Performance - Top Queries by Total CPU Time ) to compare your query with some
other queries running on the instance.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"stevong" wrote:
> Hi everyone,
> Let's say I have a VIEW that is created by some complex joins - This
> VIEW is used by many of the application's function. Let's call this
> "VIEW_1".
> "VIEW_1" select queries are already created using "with (nolock)".
> Let's say a new system requirement comes in - I notice that it may be
> better if i create another view "VIEW_2" based on "VIEW_1".
> eg:
> create view VIEW_2
> as
> (
> select blah blah from VIEW_1 where blah='lala' ....etc etc etc...
> )
> For such cases, what are the performance issues I should watch out
> for? WIll there be any performance issues? Should I be doing this in
> the first place?
> In SQL 2005 mgmt studio, how do I view the execution paths and
> timings?
> Please advise.
> Thanks.
>

Creating a User Defined Aggregate Function

Does SQL Server allow the user to create user defined aggregate functions?
If so:
What doed the syntax look like?
Where can I find more information on creating user defined aggregate
functions?Sorry, I found the answer.
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>|||In SQL 2005 user-defined functions can be created as CLR functions.
http://msdn2.microsoft.com/en-us/library/ms131051.aspx
ML
http://milambda.blogspot.com/
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>

Creating a SQLExecute function in c#

Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;

publicstaticDataSet SQLExecute(string strSQL,string strError)

{

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand();cmd.CommandType =CommandType.Text;

}

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

string strError

}

return dsetResponse;

}

At least I can point out two things:

1). You don't need to pass string error into your function because the error should be caught inside your function;

2). In your two catch blocks, you should throw the error like throw thEx and ex. In my practice, I usually just do error throw in development phase. Before I move to product, I will LOG the error, and return null for the DataSet if error is caught, so that client will not be able to see ugly error.

|||

Hi thanks for responding, i have adjusted it but i dont think what i have done so far is correct;

publicstaticDataSet SQLExecute(string strSQL)

{

string strData ="";string strTableName ="";

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);

cmd.CommandType =CommandType.Text;

cmd.Parameters["@.ColumnName"].Value = strData;

cmd.Parameters["@.TableName"].Value = strTableName;

cmd.Parameters["@.ColumnValue"].Value = strData;

cmd.ExecuteNonQuery;

}

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

clsDHOC objDHOC =newclsDHOC();objDHOC.Write2ErrorLogTable(ex1.Message,"GetDataSetByID","clsDHOC", System.Web.HttpContext.Current.Session["UserFullName"].ToString());

}

return dsetResponse;

}

|||

Hi,

SqlCommand cmd = new SqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters["@.ColumnName"].Value = strData;
cmd.Parameters["@.TableName"].Value = strTableName;
cmd.Parameters["@.ColumnValue"].Value = strData;
cmd.ExecuteNonQuery;

From the code you provided, I think there should be something wrong while you are using Parameters. As you have modify your select command, "SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'"".

There's no parameter holders for ColumnName,TableName and ColumnValue. All the parameter you want to bind should be declared with a "@." prefix which indicates that it's a parameter in your select command.

Besides, only values in condition part can be the parameter. Such value like TableName cannot be the parameter, if you want to select dynamical tables, you may create your select command manually by string.Format() method.

Thanks.

creating a function to be used in select query

I have this select query which returns a date. I would like to be able to
call this from a stored procedure and have the result appear in the result
set from the stored procedure.
i.e. SELECT *, CalcDate
FROM Table
WHERE somedate = @.dte
The CalcDate field would be the c.dt in the following select statement.
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
How can I do this - I think the above will be a UDF with a return statement
but I am not sure of the syntax.
THanksHi
I assume this subquery does not return more than 1 value.Yes you can write
an UDF to return the date as well , so please refer to the BOL for more info
See if this hepls , I could not tested it since you have not provided DDL+
sample data
SELECT *, (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM Table
WHERE somedate = @.dte
in message news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>|||I have tried the following but get the error msg:
The column prefix c does not match with a table name . ..
CREATE FUNCTION dbo.AddWorkDays
(
@.dte smalldatetime,
@.NoDays TINYINT
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day,25, @.dte)
AND @.NoDays = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
))
END
GO
"Newbie" <nospam@.noidea.com> wrote in message
news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>

creating a function for natural alphanumeric sorting

I have built a query to parse the first 4 chunks of data in a string for
natural alphanumeric sorting, and would be extremely and eternally grateful
if someone could show me how to turn this into a function that I could use
like above. Here is the ddl and some sample data:
CREATE TABLE [tblSamples] ([FIELDID] [varchar] (50))
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102D-050215')
INSERT INTO tblsamples (fieldid) VALUES ('B104D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B105D-050525')
INSERT INTO tblsamples (fieldid) VALUES ('B201D-050523')
INSERT INTO tblsamples (fieldid) VALUES ('B401D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050523D')
INSERT INTO tblsamples (fieldid) VALUES ('B103DR-050213')
INSERT INTO tblsamples (fieldid) VALUES ('B101M-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102M-050215')
I would like to be able to feed in the name of the column to be sorted(it is
'fieldid' below) and the name of the database(it is 'tblsamples' below). I
am having trouble in figuring out how to implement/create a function which
would act like this
select * from tblsamples
order by naturalsort('fieldid', 'tblsamples')
Thanks a lot. Here is my query:
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )
AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd'
from tblsamples
order by a,b,c,d
create table tblsamples>> I would like to be able to feed in the name of the column to be sorted(it
Such generic sorting attempts seems to offer nothing beneficial and
introduce unwanted complexity. Write clean queries with simple ORDER BY
clauses. If you have to come up with extra-ordinary string parsing routines
for an ORDER BY clause, perhaps you should reconsider your schema design --
it is quite possible that during logical design, multiple values are jammed
into a single column, due to faulty analysis of the business model.
What exactly are you trying to do? Do you have similarly structured tables?
If so, what is the sorting logic? Your script is too complex to understand
what you are trying to accomplish here.
Anith|||I cannot redesign the scema and I have no control over the naming of the
values in the fieldid column. I work in Environmental Consulting, and all o
f
my data is geochemical and/or hydrogeological data from water and/or soil
sampling. Whatever the field person writes down for a fieldid on the chain
of custody MUST be the value in the database. So, usually I get a set of
wells like this:
mw-1
mw-10
mw-2
mw-3
but, I want a natural order on these sampling locations, i.e.
mw-1
mw-2
mw-3
mw-10
To further complicate things, sometimes they use a dash, sometimes they
dont, sometimes these use emw, not mw, etc. etc.
What I am trying to do is sort text string with numbers and characters the
way a human. So, I am parsing the string into chunks, text chunk, number
chunk, text chunk, number chunk, to achieve a natural alphanumeric order.
Thanks for the help.
Archer
"Anith Sen" wrote:

>
> Such generic sorting attempts seems to offer nothing beneficial and
> introduce unwanted complexity. Write clean queries with simple ORDER BY
> clauses. If you have to come up with extra-ordinary string parsing routine
s
> for an ORDER BY clause, perhaps you should reconsider your schema design -
-
> it is quite possible that during logical design, multiple values are jamme
d
> into a single column, due to faulty analysis of the business model.
> What exactly are you trying to do? Do you have similarly structured tables
?
> If so, what is the sorting logic? Your script is too complex to understand
> what you are trying to accomplish here.
> --
> Anith
>
>|||If the numeric portion is always at the end of the string, then you can use
the following expression to extract them.
CAST( RIGHT( c, PATINDEX( '%[^0-9]%', REVERSE( c ) ) - 1 ) AS INT )
Anith

Creating a dataset

Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message:CS1026: ) expected". And it is coming from the following line;

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";

Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting.

publicstaticvoid SQLExecute(string strTableName,string strData)

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

("INSERT INTO" + strTableName + strData +" VALUES " + strData);

|||

let me know if it is ok

|||

your line should be like below

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName + strData +") VALUES (" + strData +")");

|||

Missing a space after INTO.

Missing a closing parenthesis before the first semi-colon.

Missing a literal space and opening parenthesis between the strTableName and strData -- should be strTableName + ' (' + strData.

Used strData for both the column name list AND the column values.

SqlCommand command =new SqlCommand("INSERT INTO " + strTableName +" (" + strColumnList +") VALUES (" + strData +")");

|||

Hi thanks for responding, the one that worked was Motleys suggestion, however my next task is to return the results in a dataset; this is my code so far, am i on the right track?

publicstaticvoid SQLExecute(string strTableName,string strData)

{

String strConnection =null;

try

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName +" (" + strData +") VALUES (" + strData +")");command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

}

Create view of inline function

Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.

I'm having trouble accessing functions through access. I've built the following function:

CREATE FUNCTION fnSTR_LEASESTATUS(@.TRS nvarchar(12))

RETURNS TABLE

AS

RETURN

(

SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]

FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)

WHERE (((dbo.tblTRACT.STR)=@.TRS))

)

GO

I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?

Thanks for your time.

You function has param @.TRS.

You could use it:

Code Snippet

SELECT * FROM fnSTR_LEASESTATUS('Your param value')

|||

Hi,

Let me try to address your concerns

There's no need to create a view of a table function, unless you want the view results somehow filtered or different than the function results.

To use the your table function simply SELECT * FROM fnSTR_LEASESTATUS(<put value here>)

If you want to create a view you need to have rights on the SQL Server 2005 database.

You are probably using an Access data project? Using these projects you are not able to just save queries as in regular Access database. With these projects you are connected directly to the server and therefore anything you save you are saving on the server.

You may want one project to create server objects like functions. Then have another regualr access database to save your queries (pass- through preferred)

|||

That allowed me to run it, but when I try to save it I get the message "Must declare the scalar variable @.TRS".

|||

Ok. I can run it in SQL Server, but how do I get to it in Access? I was hoping I could save it as a view and then link to it the way I have my other views.

Thanks.

|||

Oh, sorry. Forgot to tell you. I decided against ADP after reading on the Access user's group. So I build my views in SQL Server and then link to them.

Thanks.

|||To save your view in SQL Server you need to replace the variable reference, @.TRS, with an actual value. Once you have it saved in SQL Server, you can link like other view links|||And if you want to be prompted from Access, forget saving it as a view in SQL Server and try saving it as an Access query or some other way in Access

Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are no
functions to choose from. I can select tables and other views.
Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.
|||Yes. I have 8 of them.
"Uri Dimant" wrote:

> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>
>
|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.
|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...[vbcol=seagreen]
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:

> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>
|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.

> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.
|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions to
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:

> It helps to tell us exactly what you are doing. It is also critical to tell
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
>
> A view is effectively a pre-cannned select query. You cannot define a view
> that will accept arguments and pass those arguments to the function.
>
>
|||A view is a different thing compared to a function. A view cannot take parameters, not can it
contain code, except for one SELECT statement. In addition, your function isn't even a table values
function, it is a scalar function. A view exposes a set (a table) where a scalar function returns a
scalar value when executed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...[vbcol=seagreen]
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this way
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Create
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.

Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are no
functions to choose from. I can select tables and other views.Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.|||Yes. I have 8 of them.
"Uri Dimant" wrote:
> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
> >I would like to create a View in SQL Server 2000, based on a user-defined
> > function. However when I try it, the "Functions" tab is empty. There are
> > no
> > functions to choose from. I can select tables and other views.
>
>|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
>> Josh
>> Well , have you already created UDF?
>> "Josh" <Josh@.discussions.microsoft.com> wrote in message
>> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>> >I would like to create a View in SQL Server 2000, based on a
>> >user-defined
>> > function. However when I try it, the "Functions" tab is empty. There
>> > are
>> > no
>> > functions to choose from. I can select tables and other views.
>>|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:
> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.
> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions to
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:
> > Yeah, I've tried that but the function I'm trying to use contains
> > variables
> > and the QA throws errors when I try to do it that way.
> It helps to tell us exactly what you are doing. It is also critical to tell
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
> > My ultimate goal is to use the function in MS Access. I was thinking that
> > I'd need to convert it to a view before I could import or link to it. If
> > there's another way I'd love to hear about it (I've posted this portion of
> > the question in one of the Access newsgroups).
> >
> > Josh
> A view is effectively a pre-cannned select query. You cannot define a view
> that will accept arguments and pass those arguments to the function.
>
>|||A view is a different thing compared to a function. A view cannot take parameters, not can it
contain code, except for one SELECT statement. In addition, your function isn't even a table values
function, it is a scalar function. A view exposes a set (a table) where a scalar function returns a
scalar value when executed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this way
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Create
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
>> > Yeah, I've tried that but the function I'm trying to use contains
>> > variables
>> > and the QA throws errors when I try to do it that way.
>> It helps to tell us exactly what you are doing. It is also critical to tell
>> us exactly what "throws errors" means. When you encounter an error, post
>> the exact text of the error. And get in the practice of specifying which
>> version (and service pack level) of sql server you are using.
>> > My ultimate goal is to use the function in MS Access. I was thinking that
>> > I'd need to convert it to a view before I could import or link to it. If
>> > there's another way I'd love to hear about it (I've posted this portion of
>> > the question in one of the Access newsgroups).
>> >
>> > Josh
>> A view is effectively a pre-cannned select query. You cannot define a view
>> that will accept arguments and pass those arguments to the function.
>>|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.|||On Wed, 27 Sep 2006 09:20:02 -0700, Josh wrote:
>CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
>RETURNS INT
>AS
>BEGIN
>DECLARE @.Result int
>SET @.Result = 0
>WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
>RETURN (@.Result)
>END
Hi Josh,
I know that this is not what you're asking, but exactly how does the
result of this function differ from
CHARINDEX('/', @.Comments)
?
--
Hugo Kornelis, SQL Server MVP

Create View based on User-Defined Function

I would like to create a View in SQL Server 2000, based on a user-defined
function. However when I try it, the "Functions" tab is empty. There are n
o
functions to choose from. I can select tables and other views.Josh
Well , have you already created UDF?
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>I would like to create a View in SQL Server 2000, based on a user-defined
> function. However when I try it, the "Functions" tab is empty. There are
> no
> functions to choose from. I can select tables and other views.|||Yes. I have 8 of them.
"Uri Dimant" wrote:

> Josh
> Well , have you already created UDF?
> "Josh" <Josh@.discussions.microsoft.com> wrote in message
> news:4A014F2F-6429-4564-B0CD-A8896D5C1882@.microsoft.com...
>
>|||The problem is with your tool of choice. Use QA and just type the
appropriate create/alter view query yourself.|||Josh
I have no problems. Perhpas if you are using EM , you need to refresh
thisn tool im order to be able to see the functions
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:433B1DEC-B7AB-4C90-A9B1-932CCDE4EE3F@.microsoft.com...[vbcol=seagreen]
> Yes. I have 8 of them.
> "Uri Dimant" wrote:
>|||Yeah, I've tried that but the function I'm trying to use contains variables
and the QA throws errors when I try to do it that way.
My ultimate goal is to use the function in MS Access. I was thinking that
I'd need to convert it to a view before I could import or link to it. If
there's another way I'd love to hear about it (I've posted this portion of
the question in one of the Access newsgroups).
Josh
"Scott Morris" wrote:

> The problem is with your tool of choice. Use QA and just type the
> appropriate create/alter view query yourself.
>
>|||> Yeah, I've tried that but the function I'm trying to use contains
> variables
> and the QA throws errors when I try to do it that way.
It helps to tell us exactly what you are doing. It is also critical to tell
us exactly what "throws errors" means. When you encounter an error, post
the exact text of the error. And get in the practice of specifying which
version (and service pack level) of sql server you are using.

> My ultimate goal is to use the function in MS Access. I was thinking that
> I'd need to convert it to a view before I could import or link to it. If
> there's another way I'd love to hear about it (I've posted this portion of
> the question in one of the Access newsgroups).
> Josh
A view is effectively a pre-cannned select query. You cannot define a view
that will accept arguments and pass those arguments to the function.|||OK. Hope this will make sense to people unfamiliar with our specific
database. We have a table with a "Comments" field that contains several
pieces of information delimited by slashes (/). I didn't design it this way
and would not have; I'd have put each piece of data in its own field. But
what's done is done and I have to work with it. The DBA created functions t
o
extract the individual portions of this field. One of them has the syntax
below:
CREATE FUNCTION dbo.GetFirstSlash
( @.Comments varchar(300) )
RETURNS INT
AS
BEGIN
DECLARE @.Result int
SET @.Result = 0
WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
BEGIN
IF SUBSTRING(@.Comments, @.Result, 1) = '/'
BEGIN
BREAK
END
SET @.Result = @.Result + 1
END
RETURN (@.Result)
END
When I try to create a view based on this function in QA by changing "Create
Function" to "Create View," I get the following errors:
Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
Line 2: Incorrect syntax near '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
Must declare the variable '@.Comments'.
Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
Must declare the variable '@.Comments'.
Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
A RETURN statement with a return value cannot be used in this context.
"Scott Morris" wrote:

> It helps to tell us exactly what you are doing. It is also critical to te
ll
> us exactly what "throws errors" means. When you encounter an error, post
> the exact text of the error. And get in the practice of specifying which
> version (and service pack level) of sql server you are using.
>
> A view is effectively a pre-cannned select query. You cannot define a vie
w
> that will accept arguments and pass those arguments to the function.
>
>|||A view is a different thing compared to a function. A view cannot take param
eters, not can it
contain code, except for one SELECT statement. In addition, your function is
n't even a table values
function, it is a scalar function. A view exposes a set (a table) where a sc
alar function returns a
scalar value when executed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josh" <Josh@.discussions.microsoft.com> wrote in message
news:C2DF362C-89AA-4F3D-A5CD-BDEE6F1A866B@.microsoft.com...[vbcol=seagreen]
> OK. Hope this will make sense to people unfamiliar with our specific
> database. We have a table with a "Comments" field that contains several
> pieces of information delimited by slashes (/). I didn't design it this w
ay
> and would not have; I'd have put each piece of data in its own field. But
> what's done is done and I have to work with it. The DBA created functions
to
> extract the individual portions of this field. One of them has the syntax
> below:
> CREATE FUNCTION dbo.GetFirstSlash
> ( @.Comments varchar(300) )
> RETURNS INT
> AS
> BEGIN
> DECLARE @.Result int
> SET @.Result = 0
> WHILE @.Result < LEN(LTRIM(RTRIM(@.Comments)))
> BEGIN
> IF SUBSTRING(@.Comments, @.Result, 1) = '/'
> BEGIN
> BREAK
> END
> SET @.Result = @.Result + 1
> END
> RETURN (@.Result)
> END
> When I try to create a view based on this function in QA by changing "Crea
te
> Function" to "Create View," I get the following errors:
> Server: Msg 170, Level 15, State 1, Procedure GetFirstSlash, Line 2
> Line 2: Incorrect syntax near '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 11
> Must declare the variable '@.Comments'.
> Server: Msg 137, Level 15, State 1, Procedure GetFirstSlash, Line 13
> Must declare the variable '@.Comments'.
> Server: Msg 178, Level 15, State 1, Procedure GetFirstSlash, Line 21
> A RETURN statement with a return value cannot be used in this context.
> "Scott Morris" wrote:
>|||Once again - a view does not have arguments. You cannot simply replace
"create function" with "create view" in this case. Personally, I recommend
a different approach. First, write a script that contains a select query
that does what you want. Then, try to convert that to a view.
Seems to me that you probably want a view that is based on the table
containing the comments column and that uses some function (or logic) that
parses the comment column into its individual components.