general DB query

Hi! How do you query an entire db for a value?
I know how to do table queries from the SQL Analyzer, but not a full DB search.
Like, if I wanted to find a value '137.51 in a DB, what would I use?
Many thanks!
JJDynamic SQL
A Cursor
A temp table
And a lot of hours

Fuzzy grouping matching nulls to empty strings/spaces

Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?

(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).

Thanks in advance.

Ben

Not sure, but building a quick package and adding a data viewer should give you the answer.

I would hope that a NULL wouldn't match to spaces...

Couldn't you run an update query to "fix" the data?|||

Computer says "no"

<cough>

No duplicate matching between null and empty string/spaces, as you suspected. Thanks for the idea of the quick test - seems obvious in retrospect, if only it had been beforehand Unfortunately I can't fix the data, other than rerunning the grouping task, but good to know the source of the problem - thanks Phil!

Fundamental Functions MS SQL Server 7 or 2000 users VALUE MOST

Hi all,

What are the fundamentals (fundamental functions) that most small to
medium sized organizations that use MS SQL Server 7 or 2000 value
most?
What's your insight?

OK, here's my biased definition of small to medium sized organization,
annual revenue from 20m to 300m.

Thanks.

DLdaaa@.rock.com (DaaaDaaa) wrote in message news:<2193afb.0406011507.37ee4f6d@.posting.google.com>...
> Hi all,
> What are the fundamentals (fundamental functions) that most small to
> medium sized organizations that use MS SQL Server 7 or 2000 value
> most?
> What's your insight?
> OK, here's my biased definition of small to medium sized organization,
> annual revenue from 20m to 300m.
> Thanks.
> DL

http://www.microsoft.com/sql/evalua...ies/default.asp

Apart from that, I would guess that MSSQL is like most software -
people are using it to do more or less anything and everything you can
do with it.

Simon|||> http://www.microsoft.com/sql/evalua...ies/default.asp
> Apart from that, I would guess that MSSQL is like most software -
> people are using it to do more or less anything and everything you can
> do with it.
> Simon

Thanks, Simon.

Don

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Function with local parameters

I am trying to write a function that returns different values regdaring to a
input value.
Why can't I write like this. What shoudl I do instead.
I want to be able to write a select Item from GetPermission ('user', 're')
Regards
/Magnus
alter function [dbo].[GetPermissions3]
(
@.UserName varchar(50),
@.ItemName varchar(50),
)
RETURNS TABLE
AS
declare @.objecttype int
if @.ItemType='RE' set @.objecttype=1
RETURN
(
If
select i.Item, o.[name] from ItemGroups i
full outer join Groups g
on g.ItemGroup = i.ItemGroup
full outer join Users u
on u.GroupName = g.GroupName
join Control.dbo.Object o on i.item = o.object and o.no=@.objecttype
where (UserName = @.UserName or i.ItemGroup = @.UserName) and ItemName =
@.ItemName
)Please have a look at the syntax used to create functions. You are
attempting to create an inline function which must not have a function body
and which must contain a single select query within the returns clause. If
you want to include logic that cannot be handled within a single select
statement, then you will need to use the more complex table-valued function
syntax.|||Thanks!
After some searching for complex table-valued function I found the syntax
necessary!
My simple test (if someone wants to know) became:
alter FUNCTION MBTest
( @.FirstColNumber int )
RETURNS @.MyTable TABLE
( FirstCol varchar(50),
SecondCol varchar(50) )
AS BEGIN
declare @.mynum varchar(50)
select @.mynum = case @.FirstColNumber
when 0 then 'zero'
when 1 then 'one' end
insert @.MyTable (FirstCol,SecondCol) select @.mynum,FirstName from
BookingsApril2006
RETURN END
Best Regards
/Magnus
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZhVjdhbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Please have a look at the syntax used to create functions. You are
> attempting to create an inline function which must not have a function
> body and which must contain a single select query within the returns
> clause. If you want to include logic that cannot be handled within a
> single select statement, then you will need to use the more complex
> table-valued function syntax.
>

Function to lookup row in dataset?

Is there a function to lookup a row in a dataset? Say, find the row where
column x is equal to 10?
Find(Fields!ProductNumber.Value, 10, "Category")
--
McGeeky
http://mcgeeky.blogspot.comNo, but what is it that you are trying to do. There might be another way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Is there a function to lookup a row in a dataset? Say, find the row where
> column x is equal to 10?
> Find(Fields!ProductNumber.Value, 10, "Category")
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||I will be returning a small data set with a known number of rows in it. I
don't want to display the data in a table but rather selectively pick values
out of the data set and display them in various parts of my report.
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:#5jSf8ldFHA.3184@.TK2MSFTNGP15.phx.gbl...
> No, but what is it that you are trying to do. There might be another way
to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> > Is there a function to lookup a row in a dataset? Say, find the row
where
> > column x is equal to 10?
> >
> > Find(Fields!ProductNumber.Value, 10, "Category")
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>

Function Return Value

I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @.MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "\" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQL\Data" instead of "D:\MSSQL\Data" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

Any suggestions? Thank you.

HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

BEGIN

--Extract the file path for the database MDF physical file.
SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END

HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON

DECLARE
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.

SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATHAny suggestions?

YEah, rethink what you're doing...

MOO|||If you didn't have any information that would possibly be helpful in my question, please leave future posts to those who would be more intelligent in their responses.

If you see something that I am doing wrong, then why not offer a suggestion instead of either keeping the answer to yourself or acting more intelligent than what you are. After all, this is what this forum is intended for.

Thank you.|||Sorry you feel that way...

If you can supply us with what you're doing, I'm sure the people here can assist...if you don't like what I say, I'm sure someone will step...need more details though...

This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Any "AUTO-ADMIN" stuff is always risky...(my own opinion) MOO

Why do you have to do this? Are you releasing hundreds of databases?

Also, unless there are performance issues involved, why deviate from standard practices...

AND HOW DARE YOU ACCUSE ME OF BEING INTELLIGENT!

The nerve...|||CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

The return value of the function is declared as NVARCHAR, a single character. You need to declare the return value as a character array.

RETURNS NVARCHAR(1000)|||USE Northwind
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS

BEGIN

SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END
GO

SET NOCOUNT ON
DECLARE @.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATH
SET NOCOUNT OFF
GO

DROP FUNCTION fn_sqlmgr_get_mdf_filepath
GO|||Yeah .. but in spite of all the help from BK ... you need to rethink it anyway ;)|||I think that Brett saw you doing something potentially VERY dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP|||I think that Brett saw you doing something potentially [i[VERY[/i] dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP

You know the funny thing about this?

Trying to build a rocket ship, but can't get past retrun nvarchar|||And i dont remember exactly ... but isnt there a registry key we could read to get the default location where a file would be created ...

Hmmm ... what the hell I am thinking .. the files would be created int the default location anyway ... if you do not specify ... so whats with the function ... i am confused (once again ;))|||Thank you Homer37 for your assistance. Your answer worked just fine.|||Improper setting at the time of server installation leads to situations like this, where there is a need to write extra code. However, it also appears that you ARE trying to auto-create databases (I already picture a scene where something in other parts of your code got missed and you are sitting at a non-responsive server because your code ended up creating databases), unless I am misreading the post. To alter the default location you need to use xp_instance_regwrite rather than for every database to be created to reference the same server, following a call to xp_instance_regread.

Function results

I am trying to convert a string value, from a parameter field, into a date t
o
insert into a SQL2005 db smalldatetime field. The function returns a bit
value, which I test by using the isdate function, and if true convert this t
o
a smalldatetime value, else I set to null.
I get error that "Syntax incorrect at @.test".
What is incorrect about what I am trying to do'
...
DECLARE @.RecNumber bigint,
@.AGM1 smalldatetime,
@.AGM2 smalldatetime,
@.SDt smalldatetime,
@.Test bit
SET NOCOUNT ON;
@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
if @.test = 1 then
@.AGM1 = convert(varchar(10),@.Adv_Met1,101)
else
@.AGM1 = null
end if
Any help is appreciated.@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
Yes, there is! This is not VBScript, you need to use SET or SELECT
SET @.test = dbo.fn_testdate(@.Adv_Met1);
SELECT @.test = dbo.fn_testdate(@.Adv_Met1);
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message
news:ABD935E6-40A8-4F73-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
>to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
> to
> a smalldatetime value, else I set to null.
> I get error that "Syntax incorrect at @.test".
> What is incorrect about what I am trying to do'
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
> Any help is appreciated.|||> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
Also a bunch of problems here. I'm going to assume you come from a VB or
VBScript background?
IF @.Test = 1 -- there is no THEN with IF in T-SQL
SET @.AGM1 = ... -- again, need SET or SELECT
ELSE -- this line was okay!
SET @.AGM1 = NULL -- you don't really need to do this though, it is
already NULL!
-- END IF -- there is no such thing as END IF in T-SQL
A more structured way, and my prefered way, if you really needed the ELSE
clause, would be:
IF @.Test = 1
BEGIN
SET @.AGM1 = ...
END
ELSE
BEGIN
SET @.AGM1 = NULL
END
While it makes the code more verbose, I see two benefits:
(1) you really know where the boundaries of the conditional are, unless your
indenting / coding conventions are so bad that a BEGIN/END struct don't
help.
(2) if you need to add more statements to the result of a conditional,
you're already set. I find the lazier syntax leads to unexpected behavior,
not sure if people coming from lisp or cobol expect indentation and white
space to mean more than the code itself, but they can't seem to understand
why the following always prints 'bar' no matter what the value of @.foo:
DECLARE @.foo TINYINT;
SET @.foo = 2;
IF @.foo = 1
PRINT 'foo';
PRINT 'bar'; -- they expect this to run
-- with BEGIN / END it would be much more intuitive
IF @.foo = 2
PRINT 'blat';|||>> I am trying to convert a string value, from a parameter field, into a date to insert int
o a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I te
st by using the isdate function, and if true [sic] convert this to a smal
ldatetime value, else I set to null. <<
Your whole approach is wrong. Columns are not fields -- nothing alike.
Formatting of data is done in the front end and not in the database.
Good SQL do not write with BIT data since it is too low-level, avoid
BIGINT since it is absurdly large and avoid the proprietary
SMALLDATETIME type. SQL has no BOOLEAN data types. The ISO-11179
Standard prohibits that silly "fn-" affix on name. The use of numbers
in a data element name are a sign of repeating groups and 1NF
violations.
And you are full of syntax and conceptual errors in your effort to
mimic a non-SQL procedural language. Why did you use CONVERT()? To
get a string!! Temporal datatypes do not exist in the 3GL language you
are trying to mimci and you do not understand them. That is too
abstract, so you nee to see a picture.
Why did you use IF-THEN_ELSE constructs? Because you do not understand
CASE expressions. You do not understand declarative programming, so
you avoid it with the proprietary 20+ year old T-SQL 4GL instead.
If you must do this kind of kludging, try something like this:
SET @.agm = CAST (@.adv_met1 AS DATETIME);
Let the CAST() catch the errors then handle them. I also hope you know
about ISO-8601 formats for temporal data.|||It seems like your functions 'should' be doing the test and the conversion a
nd handing you back the date value you desire. Something like this:
CREATE FUNCTION dbo.fn_testdate
( @.StringDateToConvert as varchar(25) )
RETURNS datetime
AS
IF isdate( @.StringDateToConvert )
RETURN convert( datetime, @.StringDateToConvert, 101 )
ELSE
RETURN NULL
GO
This eliminates the entire 'If @.Test' block.
Check in Books on Line for the proper 'Style' value (the 101 above.) Look up
CAST and CONVERT and note the 'Style' indicators.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message news:ABD935E6-40A8-4F7
3-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
to
> a smalldatetime value, else I set to null.
>
> I get error that "Syntax incorrect at @.test".
>
> What is incorrect about what I am trying to do'
>
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
>
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
>
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
>
> Any help is appreciated.|||>> A more structured way, and my prefered way, if you really needed the ELSE
clause, would be: <<
A more **declarative** way and my prefered way, if you really needed
the ELSE
clause, would be:
SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
Why encourage newbies to mimic a 3GL in SQL when you do not have to?|||Thank you
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also a bunch of problems here. I'm going to assume you come from a VB or
> VBScript background?
> IF @.Test = 1 -- there is no THEN with IF in T-SQL
> SET @.AGM1 = ... -- again, need SET or SELECT
> ELSE -- this line was okay!
> SET @.AGM1 = NULL -- you don't really need to do this though, it is
> already NULL!
> -- END IF -- there is no such thing as END IF in T-SQL
> A more structured way, and my prefered way, if you really needed the ELSE
> clause, would be:
> IF @.Test = 1
> BEGIN
> SET @.AGM1 = ...
> END
> ELSE
> BEGIN
> SET @.AGM1 = NULL
> END
> While it makes the code more verbose, I see two benefits:
> (1) you really know where the boundaries of the conditional are, unless yo
ur
> indenting / coding conventions are so bad that a BEGIN/END struct don't
> help.
> (2) if you need to add more statements to the result of a conditional,
> you're already set. I find the lazier syntax leads to unexpected behavior
,
> not sure if people coming from lisp or cobol expect indentation and white
> space to mean more than the code itself, but they can't seem to understand
> why the following always prints 'bar' no matter what the value of @.foo:
> DECLARE @.foo TINYINT;
> SET @.foo = 2;
> IF @.foo = 1
> PRINT 'foo';
> PRINT 'bar'; -- they expect this to run
> -- with BEGIN / END it would be much more intuitive
> IF @.foo = 2
> PRINT 'blat';
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151678883.305430.63220@.p79g2000cwp.googlegroups.com...
> clause, would be: <<
> A more **declarative** way and my prefered way, if you really needed
> the ELSE
> clause, would be:
> SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
> Why encourage newbies to mimic a 3GL in SQL when you do not have to?
Because unlike you, I don't expect them to become SQL gurus overnight. :-)
Let's let them get the syntax figured out, then the optimal way to do
things. Much of this they'll find out on their own, because as you and I
both know, the "experts" don't always agree on the best way to do something.
A

function for absolute day value

In other words, from whenever the calendar begins, a serial number
representing the day.
if 1/1/1904 is 1, then 1/1/1905 is 366, and so on.
is there a function in tsql to get this number from a datetime field value?billdebug wrote:
> In other words, from whenever the calendar begins, a serial number
> representing the day.
> if 1/1/1904 is 1, then 1/1/1905 is 366, and so on.
> is there a function in tsql to get this number from a datetime field value?[/color
]
SELECT DATETDIFF(DAY, /* your base date */, /* some other date */);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Function call in Insert Statment

Hi

i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@.value, dbo.function(@.value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @.value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db

thx for help

Hi,

seems that you only provided 1 paramters within your query statement / parameter collection. The statement expects 2 value / value1 which both have to be supplied, if this is the same paramter you can just use the same name for them

Insert Into (value, value1)

Value(@.value, dbo.function(@.value)) --> There was also a closing parant. missing

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||supply a default value for parameter @.value in the front end. Just in case the function would not return one.

function

Hello
I use ms sql2000
If i would like to do a function
that gives me a value of "*"
if a specific field is empty
how will i do please helpThis is how it looks

two tables
Activity
Actparttime

i try whith this function

CREATE FUNCTION dbo.Actact (@.ID AS int)
RETURNS varchar(1)
AS
BEGIN
DECLARE @.Aact AS varchar(1)
SELECT DISTINCT @.Aact = "*" WHERE (SELECT dbo.Activity.ID
FROM dbo.Activity INNER JOIN
dbo.Actparttime ON dbo.Activity.ID = dbo.Actparttime.Actid
WHERE (dbo.Actparttime.Actpartend IS NULL) AND (dbo.Activity.ID = @.ID)) >""


RETURN @.Aact
END

and a view

SELECT ID, ActStart, ActEnd, Activity, dbo.Actact(ID) AS Aact
FROM dbo.Activity

i get that subquery returned more than 1 reply

please help|||another way to check:

CREATE FUNCTION dbo.IsEmpty ( @.COL sql_variant )
RETURNS varchar(1)
AS
BEGIN
DECLARE @.Ret AS varchar(1)
SELECT @.Ret = case when convert(varchar,IsNull(@.COL,''))='' then '*' else '' end
RETURN @.Ret
END
go

select dbo.sEmpty( <ColumnName> ), ...|||Thanks

But how do i do when i will have
it sorted on activity table
If there is more than one field i get an error for
many rows|||Originally posted by u31115057
Thanks

But how do i do when i will have
it sorted on activity table
If there is more than one field i get an error for
many rows

something like this ?

CREATE FUNCTION dbo.IsEmpty (
@.COL1 sql_variant ,
@.COL2 sql_variant ,
@.COL3 sql_variant ,
@.COL4 sql_variant ,
@.COL5 sql_variant
) RETURNS varchar(1)
AS BEGIN
DECLARE @.Ret AS varchar(1)
SELECT @.Ret = case when
convert(varchar,IsNull(@.COL1,''))+
convert(varchar,IsNull(@.COL2,''))+
convert(varchar,IsNull(@.COL3,''))+
convert(varchar,IsNull(@.COL4,''))+
convert(varchar,IsNull(@.COL5,''))
='' then '*' else '' end
RETURN @.Ret
END
go

AND

select dbo.IsEmpty( <colname1>, <colname2>, null, null, null ), ...

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?

THANKSHi

There is no space function but you can compare directly or something like

RTRIM(<fld>) = ''

RTRIM can be affected by the version or compatibility level of SQL Server
you are using.

The SPACE(n) function will create a string of n spaces.

For all available string functions check out books online or
http://msdn.microsoft.com/library/d..._fa-fz_7oqb.asp

John

"Killer" <roninkaiser@.tiscali.it> wrote in message
news:hFBxc.99850$Qc.3858785@.twister1.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS|||"Killer" <roninkaiser@.tiscali.it> wrote in message news:<hFBxc.99850$Qc.3858785@.twister1.libero.it>...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS

It looks like you got an answer in another group - please do not post
to multiple groups independently.

Simon

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKS
To substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
--
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>