Gaps in my reports

I have a report with 10 subreports in it. The initial report only contains 2
parameters which are used to populate the sub-reports.
The subreports are situated under eachother in the designer with nogaps in
between and the tables within the sub-reports have visibility set to hidden
when the RowCount of the dataset is 0... I would expect this to mean hide the
contents of the report when there is no data.
Why does Reporting Services add gaps where my sub-reports should be when the
main report renders ?.. Is there a way to stop this.
Help.. Ive been pulling my hair out over this one!.. ThanksJust an idea, you may want to try applying the expression in the Visibility
Properties for the ROW (not the subreport). Hopefully that will take care of
the blank row(s).
"DecksTerrorT" wrote:
> I have a report with 10 subreports in it. The initial report only contains 2
> parameters which are used to populate the sub-reports.
> The subreports are situated under eachother in the designer with nogaps in
> between and the tables within the sub-reports have visibility set to hidden
> when the RowCount of the dataset is 0... I would expect this to mean hide the
> contents of the report when there is no data.
> Why does Reporting Services add gaps where my sub-reports should be when the
> main report renders ?.. Is there a way to stop this.
> Help.. Ive been pulling my hair out over this one!.. Thanks|||Maybe my post was a litle cryptic.. let me try again.
I have a report.
That report has 10 subreports all underneath each other.
Within the subreports the all items have a visibility to false when RowCount
= 0.
So why does the main report display a gap once rendered where the sub-report
should be ?
Hope this makes it alittle clearer.
Decks.
"Nick P." wrote:
> Just an idea, you may want to try applying the expression in the Visibility
> Properties for the ROW (not the subreport). Hopefully that will take care of
> the blank row(s).
> "DecksTerrorT" wrote:
> > I have a report with 10 subreports in it. The initial report only contains 2
> > parameters which are used to populate the sub-reports.
> >
> > The subreports are situated under eachother in the designer with nogaps in
> > between and the tables within the sub-reports have visibility set to hidden
> > when the RowCount of the dataset is 0... I would expect this to mean hide the
> > contents of the report when there is no data.
> >
> > Why does Reporting Services add gaps where my sub-reports should be when the
> > main report renders ?.. Is there a way to stop this.
> >
> > Help.. Ive been pulling my hair out over this one!.. Thanks|||I had to overcome a similar "gap" problem where there could be a LOT of
hidden lines.... the only way I was able to do this was to :
1. select the entire "table" individually in each report
2. edit the size of each table by dividing the length by 10 (so 2.74
inches bacame .274 inches)
3. This gives a totally scrunched up view in the layout tab (which is a
pain for editing later on ... have to multiply by 10 to be able to view
it and then change it back again...)
4. But now ... when I get to actually view the result ... it looks a
LOT better.
5. This solution did not get rid of all the gaps all the time, but it
REALLY made a huge significant difference.
good luck...

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

Gaining control over report parameters

Hi All,

I have not heard any news on this topic elsewhere so thought I would ask here....

I would like to gain control over parameters in terms of:

1. Parameter placement as in how many parameters on a row, not just 2. It would be nice to be able to specify a row number in the defintiion of a parameter for example.

2. Access to the parameter text via a custom assembly to support multi-langauge reports.

I know other folks have talked about these things and some of us 'hope they are coming one day'...I was wondering if that day might be any closer now?

Thanks

hhhmmm....seems like this is not happening any time soon....

Anyone heard any news about this? It's been 7 months since I asked...

Thanks

Peter

|||

Funny... I was asked if this was possible today , and havent seen anything to date....

|||

Hi Mark,

this topic is discussed at length in the hitch hikers guide to report services...which is a very good book by the way...not sure if they are doing one for 2005.....

The authors said it would be a 6 month job to be able to write something useful to control how many parameters appear on a line....and it would seem obvious that if some clever person did that and it was popular it would just go into the next release of RS...so it seemed to them (and to me) that the most sensible people to write this functionality is MSFT.

What I am looking for is to be able to tell the report how many parameters to place on the line so that parameters that should be grouped together are....and also expose the text of the parameters to custom assemblies so a single report can be written in multiple languages....since the parameters are text, they cannot be written into multiple languages in the 'out of the box' version of the report browser...

From the looks of it..we will just have to wait...

FYI> "Invalid command option D." >Resolved

Just thought I would pass this along in hopes that it can help someone.
When I had changed the startup parameters for SQL so that the databases and
log files would be in different locations. I used CAPITAL LETTERS to
designate the default location. Like -DD:\data and -LL\:logs... that
caused the server to fail to start, no matter what I did, and when I tried
to start from a dos prompt I would get the "Invalid command option D."
error. The solution was to change the registry setting under
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
and remove any capitol letters. Once I changed -DD:\data to -dD:\data,
and -lL:\logs, SQL starts up fine..Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23mMqhwiwGHA.4280@.TK2MSFTNGP04.phx.gbl...
> Just thought I would pass this along in hopes that it can help someone.
> When I had changed the startup parameters for SQL so that the databases
> and log files would be in different locations. I used CAPITAL LETTERS to
> designate the default location. Like -DD:\data and -LL\:logs... that
> caused the server to fail to start, no matter what I did, and when I tried
> to start from a dos prompt I would get the "Invalid command option D."
> error. The solution was to change the registry setting under
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters]
> and remove any capitol letters. Once I changed -DD:\data to -dD:\data,
> and -lL:\logs, SQL starts up fine..
>

FYI> "Invalid command option D." >Resolved

Just thought I would pass this along in hopes that it can help someone.
When I had changed the startup parameters for SQL so that the databases and
log files would be in different locations. I used CAPITAL LETTERS to
designate the default location. Like -DD:\data and -LL\:logs... that
caused the server to fail to start, no matter what I did, and when I tried
to start from a dos prompt I would get the "Invalid command option D."
error. The solution was to change the registry setting under
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\MSSQLServer\Parameter
s]
and remove any capitol letters. Once I changed -DD:\data to -dD:\data,
and -lL:\logs, SQL starts up fine..Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:%23mMqhwiwGHA.4280@.TK2MSFTNGP04.phx.gbl...
> Just thought I would pass this along in hopes that it can help someone.
> When I had changed the startup parameters for SQL so that the databases
> and log files would be in different locations. I used CAPITAL LETTERS to
> designate the default location. Like -DD:\data and -LL\:logs... that
> caused the server to fail to start, no matter what I did, and when I tried
> to start from a dos prompt I would get the "Invalid command option D."
> error. The solution was to change the registry setting under
> & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSSQLServer\MSSQLServer\Paramet
ers]
> and remove any capitol letters. Once I changed -DD:\data to -dD:\data,
> and -lL:\logs, SQL starts up fine..
>

FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model

We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
This does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON
|||Yes. I'm interested too!. Please post details.
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON

FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model

We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONThis does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON|||Yes. I'm interested too!. Please post details.
--
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHt
FHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONsql

Functions in Parameter Definitions

I have a pair of reports that are the same except that one takes date parameters and another runs for the calculated lst full month. Currently they run off of separate stored procedures, but I would like to combine them. I don't know if my reporting tool will handle it or not, but I want to test it, so I need to combine the 2 sp's into one. I thought the first thing to try would be to set the default values to the start and end date of the previous month, like so:

CREATE PROCEDURE [dbo].[usp_blahblah]

(

@.StartDate SmallDateTime = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month

@.EndDate SmallDateTime = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month

)

..but it won't parse ("Incorrect syntax near '('."). So I'm thinking that you can't use a function in the definition of a param, although I can't find any documentation.

I'm sure there are other approaches, but I thought this would be the most straight forward... Does anybody have a really elegant idea?

From the CREATE PROCEDURE documentation - "Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

Just make the default value NULL, then test to see if the parameters are NULL and set them in the body of the proc. Like this

CREATE PROCEDURE [dbo].[usp_blahblah]
(
@.StartDate SmallDateTime = NULL,
@.EndDate SmallDateTime = NULL)
IF @.StartDate IS NULL AND @.EndDate IS NULL
BEGIN
SET @.StartDate = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month
SET @.EndDate = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month
END

|||That's slick. I'll give it a try and hope Crystal can handle it!|||

Be careful using the above approach though if you are using the variables further in a query. The query optimizer can do parameter sniffing to get optimal plan based on the parameter values but if you modify it within the SP then the sniffing cannot happen. So if you need to modify the parameter values then move the actual query into another SP and pass the modified parameters to that instead. See the link below for more details on how plan caching works.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Function with unlimited parameters

Is there any way to write procedure with ulimited number of parameters?

Like in COALESCE function. You can pass one or more parameters.

The short answer would be no, it's not.
(there is a hard limit on # parameters, but if you ever get there, you're in deep trouble most likely)

Why would you need it? Don't you know beforehand what the procedure will do?
There is often a higher cost in reaching for the ultimate in generic, instead of specializing, which will do 'less' but with lower overhead and less test/development/maintenance time.

Keep it simple, and it will work forever =:o)

/Kenneth

|||

Nope, you can't even write a function like that where you can skip parameters. I have a date function that I need to be able to pass "unlimited" values to but I have only 16 works right now. Even worse you would have to default every parameter too.

dbo.function ('value1','value2',null,null,null,null,null,null,null,null,null,null,null,null,null,...)

As an alternative (yet more costly method) you could pass a comma delimited list as a string parameter and split it up into your N values. If that is a reasonable possibility, then you can look here for how to do this: http://www.sommarskog.se/arrays-in-sql.html. XML is another possibility. Both of these require work on your end to compile the string of course, so that might not be a great way to go either.

If that doesn't make sense, someone here can write you a query based on the data you have.

|||I believe you can create an extended stored procedure that can take an unlimited number of parameters.|||Maybe You know how?

Some example?|||

Extended stored procedures are marked for deprecation so don't plan on writing new code since you will have to convert again in couple of releases or so. What is the problem you are trying to solve? Why do you need to pass optional parameters? You could also use a temporary table for example to pass the parameter values as rows. So it depends on what functionality you want and why.

|||My question is purely teoretical. There is no some kind of problem I'am trying to solve with this solution.

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 call function by name given as parameter

I want to write function to call another function which name is
parameter to first function. Other parameters should be passed to
called function.
If I call it function('f1',10) it should call f1(10). If I call it
function('f2',5) it should call f2(5).

So far i tried something like

CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
RETURNS varchar(50) AS
BEGIN
return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
END

When I call it select dbo.formuła('f_test',1000) it returns
'select f_test(1000)', but not value of f_test(1000).

What's wrong?

MariuszMariusz (vd06@.o2.pl) writes:
> I want to write function to call another function which name is
> parameter to first function. Other parameters should be passed to
> called function.
> If I call it function('f1',10) it should call f1(10). If I call it
> function('f2',5) it should call f2(5).
> So far i tried something like
> CREATE FUNCTION [dbo].[func] (@.f varchar(50),@.m money)
> RETURNS varchar(50) AS
> BEGIN
> return(select 'dbo.'+@.f+'('+convert(varchar(50),@.m)+')')
> END
> When I call it select dbo.formuła('f_test',1000) it returns
> 'select f_test(1000)', but not value of f_test(1000).
> What's wrong?

Nothing. Or everything. Just take a step back, and put yourself in
the position of SQL Server. You tell SQL Server to evaluate a string
expression. How on Earth should SQL Server see that the result of this
expression is its turn also an expression that should be evaluated?

Had you been in a stored procedure, you could have used dynamic SQL. Now
you are in a function, and the only way to do this is:

IF @.f = 'that_func'
RETURN (dbo.that_func(@.f))
ELSE @.f = 'this_func'
RETURN (dbo.that_func(@.f))
etc

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Had you been in a stored procedure, you could have used dynamic SQL. Now
> you are in a function, and the only way to do this is:
> IF @.f = 'that_func'
> RETURN (dbo.that_func(@.f))
> ELSE @.f = 'this_func'
> RETURN (dbo.that_func(@.f))
> etc
But I want to call this_func or that_func, or maybe a few other
functions without a need to modify wrapper function. Somehow I managed
to write stored procedure which does what I want:

CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
@.koszt money OUTPUT
AS
BEGIN
declare @.cmd nvarchar(50)
declare @.par nvarchar(50)
set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
set @.par=N'@.koszt money output, @.arg varchar(50)'
execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
END

Now I have onother problem: SPs cannot be used inside functions.
Only functions and extended SPs. Can I write extended SP to execute
SPs from functions?

Mariusz|||Mariusz (vd06@.o2.pl) writes:
> But I want to call this_func or that_func, or maybe a few other
> functions without a need to modify wrapper function. Somehow I managed
> to write stored procedure which does what I want:
> CREATE PROCEDURE [dbo].[f] @.funkcja varchar(50), @.arg1 varchar(50),
> @.koszt money OUTPUT
> AS
> BEGIN
> declare @.cmd nvarchar(50)
> declare @.par nvarchar(50)
> set @.cmd=N'set @.koszt='+@.funkcja+'(@.arg)'
> set @.par=N'@.koszt money output, @.arg varchar(50)'
> execute sp_executesql @.cmd, @.par, @.koszt output, @.arg=@.arg1
> END
> Now I have onother problem: SPs cannot be used inside functions.
> Only functions and extended SPs. Can I write extended SP to execute
> SPs from functions?

Yes, but in such case why call the stored procedure? Why not call the
function from the external stored procedure directly if you really want
to take this road. And I think it would be a very very bad road to take.
There are tons of reasons why you should not go there.

It might be that you already have external stored procedures in the
system (for better reasons than this one), but if you have not, you
have created a deplyoment problem. There is one more component that should
be deployed in production.

And extended stored procedures always incur a risk. An access violation
does not only crash your stored procedure - the whole SQL Server process
is blown away.

Furthermore, apparently this is a scalar function. If you say:

SELECT dbo.f(@.funkcja, @.arg1) FROM tbl

and you call an extended stored proc for each row in tbl, how effeciently
do you that will be?

Rewrite your functions to stored procedures that work on a temptable or
a spid-keyed table where it receives input parameters and return data. You
need a dispatch procedure, as you can say:

EXEC @.sp @.arg1

Where @.sp is the name of your procedure. (The above works for scalar-
values UDF:s also, by the way.)

For more information about sharing data over temp-tables, please see
http://www.sommarskog.se/share_data.html#temptables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Function Parameter

I am writing a function which will take two parameters. One the field
to be returned from a table and second parameter is the ID of the
record to be returned.

Problem is it's not returning the value of the field specified in the
parameter but instead returns the parameter itself. Is there a
function that will get the parameter to be evaluted first?

ALTER FUNCTION [dbo].[getScholarYearData]
(
-- Add the parameters for the function here
@.FieldName varchar(50), @.ScholarID int
)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @.ResultVar varchar(255)

-- Add the T-SQL statements to compute the return value here
SELECT @.ResultVar=EXECUTE(@.FieldName)
FROM dbo.qmaxScholarYearID INNER JOIN
dbo.tblScholarYears ON
dbo.qmaxScholarYearID.ScholarID = dbo.tblScholarYears.ScholarID AND
dbo.qmaxScholarYearID.MaxOfScholarYearID =
dbo.tblScholarYears.ScholarYearID

-- Return the result of the function
RETURN @.ResultVar

ENDHi,

I would do it using a procedure as follows:

CREATE PROCEDURE GetFieldValue
@.strTableNameVARCHAR(250),
@.strFieldNameVARCHAR(250),
@.nIDDECIMAL(9,0),
@.strResultVARCHAR(250) OUTPUT
AS
BEGIN
DECLARE@.strSQLNVARCHAR(4000)

SET @.strSQL = 'SELECT @.strResult = ' + @.strFieldName
+ ' FROM ' + @.strTableName
+ ' WHERE ID = ' + CAST (@.nID AS VARCHAR(9))

EXEC sp_executesql @.strSQL,
N'@.strResult VARCHAR(250) OUTPUT',
@.strResult OUTPUT
END
GO

And then execute it in a way like this:

DECLARE
@.strValueVARCHAR(250)

BEGIN

EXEC dbo.GetFieldValue 'TABLE1', 'COLUMN1', 1, @.strValue OUTPUT

PRINT @.strValue
END
GO

Obviously the print is just to ensure that you have the correct value.
This may not be the best way, but it's a way that works for me.

Hope this helps,

Paul|||SQL Server (alderran666@.gmail.com) writes:

Quote:

Originally Posted by

I am writing a function which will take two parameters. One the field
to be returned from a table and second parameter is the ID of the
record to be returned.
>
Problem is it's not returning the value of the field specified in the
parameter but instead returns the parameter itself. Is there a
function that will get the parameter to be evaluted first?
>
ALTER FUNCTION [dbo].[getScholarYearData]
(
-- Add the parameters for the function here
@.FieldName varchar(50), @.ScholarID int
)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @.ResultVar varchar(255)
>
-- Add the T-SQL statements to compute the return value here
SELECT @.ResultVar=EXECUTE(@.FieldName)

This does not even compile.

The fact that you want to pass a parameter for the column name indicates
that you have a poor table design. Columns should represent unique
attributes, and normally it's not meaningful to sometimes return one
column and sometimes another.

Nevertheless, you can use the CASE expression:

@.ResultVar = CASE @.FieldName
WHEN 'thiscol' THEN thiscol
WHEN 'thatcol' THEN thatcol
WHEN 'thatothercol' THEN thatothercol
END

--
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|||>I am writing a function which will take two parameters. One the field [sic] to be returned from a table and second parameter is the ID of the record [sic] to be returned. <<

You got nothing right in this code. Erland is nicer about it, but
that is real message.

1) fields and records are nothing like columns and rows. That is
basic SQL.

2) You have not looked at ISO-11179 rules for data elements. At least
get rid of that silly "tbl-" and learn why even Microsoft has dropped
camelCase. And things like a "year_id" make no sense; Years are
already unique entities and have a name; they do not need an id. You
might give them a name, like the Chinese Zodiac, but that is
reaching. But a year in the context of a student is an attribute for
attending school, graduation, etc.

Also, never put the data type in a data element name like Paul did;
This is SQL and not 1960's BASIC for a 16KB PC.

3) The whole idea of what you are trying to do is a violation of
coupling and cohesion -- remember them in your first software
engineering class? You should name this nightmare something like
"Britney Spears, Squids or Automobiles" so that people will know that
you have no idea what it does until run time.

Think for a second about how uselessly vague the name
"getScholarYearData" is. Which data?

4) If you REALLY want to learn, first get some books and courses --
you really need that SE course before any SQL or RDBMS course.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

A bad design leads to these insane and impossible to maintain kludges
you are being offered to get you out of the way. It is easier than
trying to fill in an IT education in a posting.|||Also, never put the data type in a data element name like Paul did;

Quote:

Originally Posted by

This is SQL and not 1960's BASIC for a 16KB PC.
>

Please can you explain what you mean by this? This procedure works
fine for me, but If there is a better way of doing something then I
would like to know what it is. I am not sure we actually need to have
a procedure like this in our system, but this is how I would have
written it if so. Any improvements would be appreciated!|||On Apr 23, 6:04 am, paul.wr...@.gmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Also, never put the data type in a data element name like Paul did;
This is SQL and not 1960's BASIC for a 16KB PC.

>
Please can you explain what you mean by this? This procedure works
fine for me, but If there is a better way of doing something then I
would like to know what it is. I am not sure we actually need to have
a procedure like this in our system, but this is how I would have
written it if so. Any improvements would be appreciated!

Don't waste your time with him. He's too busy insulting people to
offer any help. This stored procedure will be used to display
information in a form. Your code worked fine and I appreciate your
help.

In the end I decided to use an SQL statement and a subform, but was
just curious as to how I could manipulate the behavior of that
parameter. For the hate mongers please excuse my trying to learn
anything new.

I love the stuff about my table structure being poorly designed. They
know this without even seeing it. Or the naming conventions
changing. Oh sorry this database is about 10yrs old so I should go in
and start changing all the table names to make these big shots
happy.

Please if you don't have any useful information to share just move
along and keep your hateful statements to yourself.|||SQL Server wrote:

Quote:

Originally Posted by

I love the stuff about my table structure being poorly designed. They
know this without even seeing it.

It is not necessary to see your DDL to know much about the design.

Joe and Erland tried to do you a favor. Others like me just shook
their heads and just let it going knowing there are so many people
out there that think because they can write some syntactically
correct code they are developers.

You owe several people apologies.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||On Apr 28, 2:20 pm, DA Morgan <damor...@.psoug.orgwrote:

Quote:

Originally Posted by

SQL Server wrote:

Quote:

Originally Posted by

I love the stuff about my table structure being poorly designed. They
know this without even seeing it.

>
It is not necessary to see your DDL to know much about the design.
>
Joe and Erland tried to do you a favor. Others like me just shook
their heads and just let it going knowing there are so many people
out there that think because they can write some syntactically
correct code they are developers.
>
You owe several people apologies.
--
Daniel A. Morgan
University of Washington
damor...@.x.washington.edu
(replace x with u to respond)

I can take Erland's suggestion to rethink why I am doing what I am
doing (which I had already done) because it was delivered without
malice. Joe on the other hand is just plain nasty. I can do without
his kind of help.|||>Please can you explain what you mean by this? <<

The ISO-11179 standards for naming data elements are based on the idea
that you name things for what they inherently are. That name is then
used everywhere in the schema.

You do NOT name them for:

1) How they are physically stored -- that means you do not put the
data type into the name. We had to do in the original versions of
BASIC because the interpreters needed that information to allocate
storage on the fly. A lot of programmers never un-learned that.

Physical locators generated by the physical storage are never
attributes in the schema. IDENTITY is never a key. We do not make
the user navigate the tables using track and sector numbers, etc. The
SQL engine is supposed to handle surrogates and not the humans.

It also means no silly "tb-" or "tbl-" to tell us it is a table
(there is only *one* data structure in SQL, duh!). And no vw-" affix
to tell us it is a VIEW (the "vw-" thing always looked like a
"Volkswagen" to me).

2) Where they are located - that means the table name is not part of
the data element name. Do you change your name from place to place as
you move around? Of course not. This practice also screws up the
data dictionary (if you do not have a data dictionary your project is
really screwed).

3) How they are used in one place -- that means no "pk-" or "fk-"
affixes. That is also silly because the same identifier that is a
FOREIGN KEY in the referencing has to be UNIQUE or a PRIMARY KEY in a
second table by definition.

The correct format is "<entity name>_<attribute type>" in lower case
for column names. The attribute types are defined in your data
dictionary, but I have a short list in other postings.

4) A data element name does not have multiple attribute types. That
means you can have "customer_id" or "customer_type" but never
"customer_type_id" because the attribute has to be either an
identifier (unique per customer) or a type (applies to many
customers).

This is usually a newbie confusing data and metadata in his attempt at
a data model.

5) A data element name is not a single attribute type. There is no
such thing as the magical, universal "id" or "date" or "value" etc.
An attribute has to be the identifier of something in particular, the
date of a particular kind of event, the value of a known attribute as
measured on a scale, etc. It is also a sign the project has no data
dictionary because you would quickly see that these magical vague
attributes apply to automobiles, squids and Britney Spears.

"To be is to be something in particular; to be nothing in particular
or everything in general is to be nothing." - Aristotle

6) A data element name is not a dangling entity name. The data
element name "customer" by itself begs the question "what?"
--"customer_id", "customer_type", "customer_name", or what? My
favorite is assuming that "sex" means "sex-frequency" or
"sex_preference" and not "sex_code" when I get a form.

The programmer has confused a table with a file and expects context to
provide the information he was too lazy to put into the table. The
field names in a file are local to the file; the column names are
global to the schema or better yet, are global to a data model that
covers your entire enterprise or industry.

An exception to this is the use of industry standard names that are
well understood in your enterprise. For example, VIN for automobiles,
ISBN for books, etc.

7) Tables are sets and should have collective or plural names, not
singular ones. That is, "Employee" is a bad table name (exception:
you really do have only one employee); "Employee" is better;
"Personnel" is best. Collective nouns imply a set by their nature and
will not be used in attribute names, which have a singular name
because their values apply to an element in the set.

An exception to this is the use of industry standard names that are
well understood in your enterprise. But most of these will be
collective nouns.

8) Relationship tables should use a common name for the relationship
and not an invented hyphenate. For example, "Roster" and not
"StudentClass" or worse.

I you wish, I can also post a quick look at ISO-11179.|||--CELKO-- wrote:

Quote:

Originally Posted by

If you wish, I can also post a quick look at ISO-11179.

You should even though the OP probably thinks reading and
standards are not part of the requirement for the job.

Others would enjoy reading it.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||

Quote:

Originally Posted by

I you wish, I can also post a quick look at ISO-11179.

I'm trying to fight my way through the kludge of data online to get an
overview of this myself - and I happened to come across this thread.

Yes, a quick look at ISO-11179 would be great (an indept look would be
even better ;) )... I've inherated the tblConjoinedName legacy, but
have a chance with a new ap to do things right - if only I knew what
"right" was!

Full-Text Search and Output Parameters

Hi,

I'd like to incorporate search functionality (SQL Server 2005 Full-Text Search) into a web application, so I want to be able to return a paged list of results based on the user's search terms. I already have a parameterized stored procedure that returns a list of products when a category ID is supplied. I modified this procedure to use a different input parameter (@.SearchTerms), but I'd still like to return the number of records, as in the original stored procedure.

However, I'm getting this error: Invalid object name 'ProductEntries'.

Here's the original stored procedure:

ALTER PROCEDURE dbo.GetProductsByCategoryID
(
@.CategoryID INT,
@.PageIndex INT,
@.NumRows INT,
@.CategoryName VARCHAR(50) OUTPUT,
@.CategoryProductCount INT OUTPUT
)
AS

BEGIN
SELECT @.CategoryProductCount = (SELECT COUNT(ProductID)
FROM Products
WHERE Products.CategoryID = @.CategoryID)
SELECT @.CategoryName = (SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @.CategoryID)

DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;

WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM Products
WHERE CategoryID = @.CategoryID
)

SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1

END

And here's the modified one:

ALTER PROCEDURE dbo.GetSearchResults
(
@.SearchTerms VARCHAR(200),
@.PageIndex INT,
@.NumRows INT,
@.ProductCount INT OUTPUT
)
AS

BEGIN
SELECT @.ProductCount = (SELECT COUNT(ProductID)
FROM ProductEntries)

DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;

WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM CONTAINSTABLE (Products, *, @.SearchTerms, 25) AS c, Products p
WHERE c.[KEY] = p.ProductID
)

SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1

END

I thought I might be getting this error because SELECT @.ProductCount occurs before the ProductEntries table is created, but when I move that SELECT statement further down, I still get the same error.

How can I get the value of @.ProductCount in this scenario so that I can display it in the UI of the web app?The first to do would be to directly query the ProductEntries table from both a Stored Procedure and then as an individual statement. In 99% of cases, this solves the problem.

A possible and common reason for your error may have to do with ownership properties of the table and their association, or lack thereof, to the account that you are using to create the stored procedure.

I haven't used SQL Server for a few months now, as I've just recently started working on an Oracle project, and I cannot recall exactly the relationship between CREATE and EXECUTE on stored procedures and which of the above explicitly allows access to the underlying objects that will be used by the stored procedure.

Regards,|||Hi Robert,

Thanks for your suggestions. Unfortunately, I could not get the procedure to work. After further research, I decided not to rely on SQL Server Full-Text Search for my site's search engine because it's really not practical in a shared hosting environment. So, I think I'll put this one to rest and check out the MSN Search SDK (http://search.msn.com/developer).

Anyway, thanks again for your reply -- it was much appreciated.