Passing multiple rows of data to a code function

Is there a way to pass multiple rows to a function on the report? Here's
what I'm trying to do:
I have field in my detail section, "customer", that is shows each customer:
[Customer1]
[Customer2]
[Customer3]
[etc.]
I'd like to wrap these into a single field at the parent group so I get the
following in a single field:
[Customer1, Customer2, Customer3, etc.]
Does that make sense?
I was thinking I might be able to write a VB function to take in a group of
records, itereate through them, and return the reformated string.
Of course if there's another way to accomplish this, I'm completely open to
other ideas.hi,i think it's better 2 do it on the sql side,so u get it in the ds as one
field.
"Greg S" wrote:
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get the
> following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group of
> records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open to
> other ideas.
>
>|||Take a look at the matrix control and see if that will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg S" <gregslistacct@.hotmail.com> wrote in message
news:e2lvbHHIGHA.532@.TK2MSFTNGP15.phx.gbl...
> Is there a way to pass multiple rows to a function on the report? Here's
> what I'm trying to do:
> I have field in my detail section, "customer", that is shows each
> customer:
> [Customer1]
> [Customer2]
> [Customer3]
> [etc.]
> I'd like to wrap these into a single field at the parent group so I get
> the following in a single field:
> [Customer1, Customer2, Customer3, etc.]
> Does that make sense?
> I was thinking I might be able to write a VB function to take in a group
> of records, itereate through them, and return the reformated string.
> Of course if there's another way to accomplish this, I'm completely open
> to other ideas.
>|||Follow up solution to my own thread:
Well, I did find some way to do this
concatenation/aggregation/rows-to-a-column on the SQL side. Here's a good
example using CROSS APPLY and leveraging FOR XML in sql 2005
http://www.aspfaq.com/show.asp?id=2529
I found a number of other examples as well - some using UDF functions,
orthers using customer CRL assemblies. Most threads had someone commenting
to the effect of "... this is usually needed for some kind of reporting and
should be handled in the presentation layer... doing it via SQL is breaking
the idea of pure relational databases..." Just thought this was funny as
my presentation layer (reporting services) can't do it. :^)
"'" <@.discussions.microsoft.com> wrote in message
news:4D2617C3-804E-4A10-AE87-53C8CB077DD4@.microsoft.com...
> hi,i think it's better 2 do it on the sql side,so u get it in the ds as
> one
> field.
> "Greg S" wrote:
>> Is there a way to pass multiple rows to a function on the report? Here's
>> what I'm trying to do:
>> I have field in my detail section, "customer", that is shows each
>> customer:
>> [Customer1]
>> [Customer2]
>> [Customer3]
>> [etc.]
>> I'd like to wrap these into a single field at the parent group so I get
>> the
>> following in a single field:
>> [Customer1, Customer2, Customer3, etc.]
>> Does that make sense?
>> I was thinking I might be able to write a VB function to take in a group
>> of
>> records, itereate through them, and return the reformated string.
>> Of course if there's another way to accomplish this, I'm completely open
>> to
>> other ideas.
>>

passing multi value parameter to stored procedure

I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.

In the dataset , I set command type as Text and typed the following statement.

EXEC spMultiValue " & Parameters!Month.Value &"

its not returning anything.

can anyone tell me how to pass multivalue parameter to stored procedure.

Thanks for your help.

Change the command type to Stored Procedure and type

SPMultiValue

Now click OK and hit the refresh button. Now go back to Edit the dataset and go to parameters tab and map it with the correct parameter.

For multi value parameter within stored procedure you need to decode the multi values to be able to use in the query.

|||

Thanks for u'r reply. I have GroupDataBy parameter. Based on that value I need to call different stored procedure.

I have to use commond type As text. Is there any way to pass Multi value parameter for the following statement

"Exec spMultiValue '" & Parameters!Month.Value & "'"

|||Change Parameters!Month.Value to Parameters!Month.IsMultiValue

Passing MS SQL2005 query result into Javascript function

I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.

This is how I retrieve the longitude:

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"

SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:

var map = new GMap2(document.getElementById("map"));

var lat = <%=lat%>;

var lon = <%=lon%>;

var center = new GLatLng(lat,lon);

map.setCenter(center, 13);

However, lat & long do not contain the retrieved result but rather a useless System.something string.

How do I assign the retrieved results to these variables and port them over to Javascript as required?

Many thanks!

Could you show the code that you use to apply the results from the query(ies) to the variableslon andlat? Only it seems the fault is at that part.|||

I think the problem we are having is that we're not sure how to assign the results to a variable.

Do you know how we pass the returned value into a variable in ASP first?

|||

SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)

When we execute that query in SQL Management Studio we get the latest longitude entry from our database no problem (we also do it for latitude).

However, we assumed that this was passed into the ID of the piece of ASP code where we have it and hence accessible to the javascript, but it's not. How do we assign the result of this SQL execution to a string variable and have it accessible to javascript?

I know it sounds simple but I'm just finding rubbish in Google.
Thanks!

|||

You need to assign the two values you get from the database to string variables then write those strings to the page as part of your javascript code block.

Something like this:

SqlCommand cmd = new SqlCommand("SELECT lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)", conn);
string lon = (string)cmd.ExecuteScalar();

lon is now the variable containing a string representaion of the value, which you should be able to use in the way you did before:

<%= lon %>

Have a look at this link for more on the command object and retrieving scalar values:http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

|||

Thanks Mike.

However, when I put this code between the...

-><asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"
AND
-> </asp:SqlDataSource>

..tags, I get an error that "SqlCommand" is not a valid attribute of the element SQqlDataSource.

|||Am I just to put this C# code between percentage brackets?<% like these?? %>|||

<asp:SqlDataSourceID="lon"runat="server"ConnectionString="<%$ ConnectionStrings:LocateThis %>"SelectCommand="SELECT @.lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"></asp:SqlDataSource>

Should I dump this method of connecting to the database altogether? Your method seems to be just using pure c#, whereas we are placing this in a page of html/ASP.

Sorry for sounding completely remedial!

|||Probably easier not to use the Sqldatasource in this instance. Try the C# in the code behind. The sqlDatasource is fine for quickly binding data to a GridView or something.|||

Thanks!I've managed to get the code working in a .cs file and have referenced it at the top of my Routes.aspx file asCodeFile="Routes.aspx.cs"

I set breakpoints and stepped through the code and have a while loop to read the last two longitude and latitude entries from the database.

In the .cs file I set up the variables as doubles.

while (rdr.Read())
{
Double lon=rdr.GetDouble(0);
Double lat=rdr.GetDouble(1);
}

Back in the main aspx file I set::

var lat = <%=lat%>
var lon = <%=lon%>

However I get the error "lat" and "lon" do not exist in the current context.
AFAIK, the .cs code should execute before the aspx file is run?

|||

The cs file and the aspx file both form (partial) parts of the same page class. It all gets executed at the same time. Anyway, it maybe better if you used literal controls:

public string lon;
public string lat;

while (rdr.Read())
{
lon=rdr.GetDouble(0).ToString();
lat=rdr.GetDouble(1).ToString();

}

litLon.Text = lon;
litLat.Text = lat;

Then in your aspx:

var lat = <asp:Literal ID="litLat" runat="server" />
var lon =<asp:Literal ID="litLon" runat="server" />

|||Thank you! This is now working..|||

Hi Guys,

Im now trying to use an array from the C# page and pass it to Javascript on the aspx page.

Is this similar to what we have above?

Thanks for all your help.

|||

Im still not sure how to do this exactly.

Ive been searching forums for a while now.

Ive read a couple of post that say its not possible to pass a C# array back to the Javascript. Can some one confirm this?

Any tips at all?

Thanks

|||

If I were you, I'd create a new post for this. The original question was answered, so this thread is marked as resolved. That means not many people will be looking at it. Also, a new thread with a relevant subject line will be easier for people to search in the future.

When you post, mention the nature of the array - whether it's one dimensional etc, and how you are generating it.

sql

Passing int parameter to stored procedure question.

Hi all,

I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the C# code.
My code is showing below
----------- -------
thisConnection.Open();
SqlParameter param = DeleteRow.Parameters.Add("@.recordI D", SqlDbType.Int, 4);
param.Value = key;
SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this line
The stored procedure code which is showing below:
--------------------
CREATE PROCEDURE dbo.deleteRow @.recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @.recordID
Can anyone give me some ideal why this happen.
Thank alot.
wing

I don't know exactly, maybe you made a mistake here:
"@.recordI D",
see the whitespace

|||You should be using ExecuteNonQuery. ExecuteReader is designed to return data, not update.
|||omg, I've overlooked that. :$
You can also use an ExecuteScalar. In contract to ExecuteNonQuery, itwill get the first field of the first row. Usefull when using a returnvalue in a stored procedure...

Passing columns to CLR function

Hello,

I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:

Code Snippet

public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)

{

//Do Something...

x = ....

y=...

z=...

}

I want to call this code from SQL code:

Code Snippet

create table #Temp (float a,float b)

declare @.x float

declare @.y float

declare @.z float

exec dbo.DoSomething(a,b,@.x,@.y,@.z) ?

Do someone have an idea?

Hi Shlomi,

There are many tutorials for achieving this on the net and even in BOL. Look for "CLR User-Defined Functions" Books Online for examples.

In short: If you're using Visual Studio, you open the right project and choose Add Function. Sceleton code is presented and you add your code. Now build the Assembly. After building, go to SQL Server and user CREATE ASSEMBY to import the assembly into SQL Server and last use CREATE FUNCTION to import the function from the assembly. Now you can use your function in SQL Server like you want to.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing an array to sql

I haven't done sql in a year so I could use some help here. I've got a
procedure below that uses the Function (listed under it here) that is
supposed to parse a string and pass the parsed string as an array to sql.
I'm not doing something correctly. If I pass in a single Symbol (string) my
procedure returns what it is supposed to, but if I pass in a string like thi
s
'A,B,C' nothing is returned, as though there is no parsing taking place.
The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
works when I run the example, so there must be some mistake in the way I've
writtem my procedure. I think the line in question is my last 'Join'
statement.
Anyone have any ideas?
Thanks,
Paul
===========
--- My Procedure
--
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net
Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
INNER JOIN
a_Financials ON a_Hyperlinks.Yahoo_Main =
a_Financials.Yahoo_Main
INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry
INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector
INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN
iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
a_Name_Symbol.Symbol = s.nstr
WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
ORDER BY a_Name_Symbol.Name
--- iter_charlist_to_table
Function --
List-of-strings
Here is a similar function, but that returns a table of strings.
CREATE FUNCTION iter_charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
Here is an example on how you would use the function:
CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Customers C
JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
= s.nstr
go
EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'I was given the answer.
I just needed to remove the IN function in the WHERE clause.
========================================
=====
"a" wrote:

> I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
my
> procedure returns what it is supposed to, but if I pass in a string like t
his
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way I'v
e
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My Procedur
e --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Peri
od
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerI
D
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>|||> writtem my procedure. I think the line in question is my last 'Join'
> statement.
I suspect you problem is the WHERE clause:

> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
It looks to me that this an artifact of a previous incorrect technique and
is superseded by your join to the table-valued function. Also, it looks
like you are using SQL 2005 since I see varchar(MAX). In that case, you
might consider passing XML. Untested example:
ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols xml)
AS
SELECT
a_Name_Symbol.Name,
a_Name_Symbol.Symbol,
a_Sector.Sector,
a_Industry.Industry,
a_Quarter_Index.Period,
a_Financials.[00_Sales] AS Revenue,
a_Financials.[15_Net_Inc_from_con_ops] AS Income,
a_Financials.[26_EPS_from_con_ops] AS EPS,
a_Financials.[15_Margins_-_NET_con_ops] AS [Net Margin],
a_Financials.PE,
a_Hyperlinks.Yahoo_Main AS Yahoo,
a_Hyperlinks.MSN_10Qs AS Financials,
a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS TA1
FROM a_Hyperlinks
JOIN a_Financials ON
a_Hyperlinks.Yahoo_Main = a_Financials.Yahoo_Main
JOIN a_Industry ON
a_Financials.Industry = a_Industry.Industry
JOIN a_Sector ON
a_Financials.Sector = a_Sector.Sector
JOIN a_Quarter_Index ON
a_Financials.Period = a_Quarter_Index.Period
JOIN a_Name_Symbol ON
a_Financials.Symbol = a_Name_Symbol.Symbol
JOIN (SELECT CAST(PortfolioSymbols.PortfolioSymbol.query('.') AS char(5)) AS
PortfolioSymbol
FROM @.PortfolioSymbols.nodes('/PortfolioSymbols/PortfolioSymbol/text()')
PortfolioSymbols(PortfolioSymbol)) AS PortfolioSymbols ON
a_Name_Symbol.Symbol = PortfolioSymbols.PortfolioSymbol
ORDER BY a_Name_Symbol.Name
GO
EXEC get_company_names_iter '<PortfolioSymbols>
<PortfolioSymbol>ALFKI</PortfolioSymbol>
<PortfolioSymbol>BONAP</PortfolioSymbol>
<PortfolioSymbol>CACTU</PortfolioSymbol>
<PortfolioSymbol>FRANK</PortfolioSymbol>
</PortfolioSymbols>'
Hope this helps.
Dan Guzman
SQL Server MVP
"a" <a@.discussions.microsoft.com> wrote in message
news:B6AF913C-74FE-4214-BBA1-768ED66ADD67@.microsoft.com...
>I haven't done sql in a year so I could use some help here. I've got a
> procedure below that uses the Function (listed under it here) that is
> supposed to parse a string and pass the parsed string as an array to sql.
> I'm not doing something correctly. If I pass in a single Symbol (string)
> my
> procedure returns what it is supposed to, but if I pass in a string like
> this
> 'A,B,C' nothing is returned, as though there is no parsing taking place.
> The function (http://www.sommarskog.se/arrays-in-sql.html#iterative) also
> works when I run the example, so there must be some mistake in the way
> I've
> writtem my procedure. I think the line in question is my last 'Join'
> statement.
> Anyone have any ideas?
> Thanks,
> Paul
> ===========
>
> --- My
> Procedure --
> ALTER PROCEDURE [dbo].[_Portfolios_Basic] (@.PortfolioSymbols
> NvarChar(max))
> AS
> SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Sector.Sector,
> a_Industry.Industry, a_Quarter_Index.Period, a_Financials.[00_Sales] AS
> Revenue,
> a_Financials.[15_Net_Inc_from_con_ops] AS Income,
> a_Financials.[26_EPS_from_con_ops] AS EPS,
> a_Financials.[15_Margins_-_NET_con_ops] AS [Net
> Margin], a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo,
> a_Hyperlinks.MSN_10Qs AS Financials,
> a_Hyperlinks.MSN_events AS Events, a_Hyperlinks.StockCharts AS TA1
> FROM a_Hyperlinks
> INNER JOIN
> a_Financials ON a_Hyperlinks.Yahoo_Main =
> a_Financials.Yahoo_Main
> INNER JOIN
> a_Industry ON a_Financials.Industry = a_Industry.Industry
> INNER JOIN
> a_Sector ON a_Financials.Sector = a_Sector.Sector
> INNER JOIN
> a_Quarter_Index ON a_Financials.Period =
> a_Quarter_Index.Period
> INNER JOIN
> a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
> JOIN
> iter_charlist_to_table(@.PortfolioSymbols
, DEFAULT) s ON
> a_Name_Symbol.Symbol = s.nstr
> WHERE (a_Name_Symbol.Symbol IN (@.PortfolioSymbols))
> ORDER BY a_Name_Symbol.Name
>
> --- iter_charlist_to_table
> Function --
> List-of-strings
> Here is a similar function, but that returns a table of strings.
> CREATE FUNCTION iter_charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> Here is an example on how you would use the function:
> CREATE PROCEDURE get_company_names_iter @.customers nvarchar(2000) AS
> SELECT C.CustomerID, C.CompanyName
> FROM Customers C
> JOIN iter_charlist_to_table(@.customers, DEFAULT) s ON C.CustomerID
> = s.nstr
> go
> EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
>

Passing a subquery as a parameter to a user defined function

I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a table.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>> I have a function which accepts a string as a parameter and returns a
>> table.
>> It is a bit like a split function. It works when I pass the string as a
>> variable. When I try to pass in the string variable as the result of a
>> subquery I get an error.
>> This works
>> declare @.test varchar(50)
>> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
>> This doesn't
>> declare @.test varchar(50)
>> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
>> NS_REPORT_SAVE where savereportid = 8),',')
>> I get
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near '('.
>> Server: Msg 170, Level 15, State 1, Line 2
>> Line 2: Incorrect syntax near ','.
>

Passing a subquery as a parameter to a user defined function

Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>|||I have a function which accepts a string as a parameter and returns a table.
It is a bit like a split function. It works when I pass the string as a
variable. When I try to pass in the string variable as the result of a
subquery I get an error.
This works
declare @.test varchar(50)
set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
This doesn't
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
NS_REPORT_SAVE where savereportid = 8),',')
I get
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.|||Try the following:
declare @.test varchar(50)
select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
NS_REPORT_SAVE where savereportid = 8)',',')
Chris wrote:
> I have a function which accepts a string as a parameter and returns a tabl
e.
> It is a bit like a split function. It works when I pass the string as a
> variable. When I try to pass in the string variable as the result of a
> subquery I get an error.
> This works
> declare @.test varchar(50)
> set @.test = (select projectid from NS_REPORT_SAVE where savereportid = 8)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC(@.test,',')
> This doesn't
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC((select projectid from
> NS_REPORT_SAVE where savereportid = 8),',')
> I get
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near '('.
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.|||The value that is passed as the first parameter is a comma separted field
e.g.'1,34,23' so it is expecting something in that format. That particular
subquery returns an appropriate value. Is the subquery seen as a table and
you can't pass a table to a subquery?
<bharat.gidwani@.gmail.com> wrote in message
news:1151339095.892515.168280@.r2g2000cwb.googlegroups.com...
> Try the following:
> declare @.test varchar(50)
> select * from dbo.CHARLIST_TO_TABLE_NUMERIC('(select projectid from
> NS_REPORT_SAVE where savereportid = 8)',',')
> Chris wrote:
>

Passing a specific cursor record to a function

Hello,

Is it possible? Can I select a specific record of the cursor to be
sent to a seperate function to do all the computations etc.?

Regards,
VSHi

You will have to pass each as a separate variable or possibly use a
(temporary) table. If you can rewrite the cursor to be a set function you
will usually get much better performance.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406151234.3925efae@.posting.google.c om...
> Hello,
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?
> Regards,
> VS|||TinTin (lalalulu24@.yahoo.com) writes:
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?

You can pass a cursor varible to stored procedure, but I am not sure that
functions accept cursor variables.

In any case, cursors is not something you should use that often. As I said
in my other posting, work set-based whenever possible.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
John: I think I will buy the Temporary Table suggestion. Thanks!

Erland: I am not too sure about what you mean by "work set-based".
There might be an easier alternative to what I am doing. Could you
precisely refer to me a specific area; or topic which I should read?

Regards!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns950A2ABF8BDEYazorman@.127.0.0.1>...
> TinTin (lalalulu24@.yahoo.com) writes:
> > Is it possible? Can I select a specific record of the cursor to be
> > sent to a seperate function to do all the computations etc.?
> You can pass a cursor varible to stored procedure, but I am not sure that
> functions accept cursor variables.
> In any case, cursors is not something you should use that often. As I said
> in my other posting, work set-based whenever possible.|||Hi

You would have to post the DDL (Create table statements etc), Example Data
as Insert statements and expected output along with your stored procedure
definition, so that we have a better idea what you are trying to do.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406160504.66185536@.posting.google.c om...
> Hi
> John: I think I will buy the Temporary Table suggestion. Thanks!
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?
> Regards!
>
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
news:<Xns950A2ABF8BDEYazorman@.127.0.0.1>...
> > TinTin (lalalulu24@.yahoo.com) writes:
> > > Is it possible? Can I select a specific record of the cursor to be
> > > sent to a seperate function to do all the computations etc.?
> > You can pass a cursor varible to stored procedure, but I am not sure
that
> > functions accept cursor variables.
> > In any case, cursors is not something you should use that often. As I
said
> > in my other posting, work set-based whenever possible.|||TinTin (lalalulu24@.yahoo.com) writes:
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?

Rather than writing:

DECLARE @.price money,
@.qty int,
@.total money,
@.orderid int,
@.prev_orderid int

DECLARE order_total_cur INSENSITIVE CURSOR FOR
SELECT orderid, price, qty
FROM order_details
ORDER BY orderid

OPEN order_total_cur
SELECT @.total = 0
WHILE 1 = 1
BEGIN
FETCH order_total_cur INTO @.orderid, @.price, @.qty
IF @.@.fetch_status <> 0
BREAK

IF @.prev_orderid IS NOT NULL AND @.orderid <> @.prev_orderid
BEGIN
UPDATE orders
SET total = @.total
WHERE orderid = @.prev_orderid

SELECT @.total = 0
END

SELECT @.total = @.total + @.price * @.qty, @.prev_orderid = @.orderid
END

DEALLOCATE order_total_cur

IF @.orderid IS NOT NULL
BEGIN
UPDATE orders
SET total = @.total
WHERE orderid = @.orderid
END

You write:

UPDATE orders
SET total = od.total
FROM orders o
JOIN (SELECT orderid, total = sum(qty * price)
FROM orderdetails
GROUP BY orderid) AS od ON o.orderid = od.orderid

Not only is this more concise and less error-prone to write, the
difference in performance could be magnirute if there are many
rows in the table.

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

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

Passing a datePart

How can I pass a datepart to my personal function or stored procedure.
In other words I want to allow a user to pass a datepart such as to compute
by day year, etc... The problem is that when I use the variable that has the
datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
sample of what I am trying to do:
DECLARE myDatePart varChar(1)
SET myDatePart = "d"
SELECT DATEADD(myDatePart, 1, GETDATE())
I Am trying to create my own datepart function. I do not want to create an
entire if statement to determine what type of datepart is passed into my
procedure.
Thanks in advanceInstead of creating many UDFs or single function with magic numbers as
enums, (if you have 2005) you may want to create a datetime type instead,
that way you can code methods that operate over that type and call like so
(note how intuitive it then becomes):
declare @.sd SqlDate
set @.sd = '1/1/2005'
select @.sd.Day as Day, @.sd.Month as Month, @.sd.Year as Year, @.sd.Hour as
Hour, @.sd.Minute as Minute, @.sd.Second as Second
I have example of this at:
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to
> compute
> by day year, etc... The problem is that when I use the variable that has
> the
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||"Angel" <Angel@.discussions.microsoft.com> wrote in message
news:64F1E3AC-6540-4BDD-99F3-3809DBCE9936@.microsoft.com...
> How can I pass a datepart to my personal function or stored
procedure.
> In other words I want to allow a user to pass a datepart such as
to compute
> by day year, etc... The problem is that when I use the variable
that has the
> datepart value ie: "d" or "m" or "y" it fails to recognize it.
This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to
create an
> entire if statement to determine what type of datepart is passed
into my
> procedure.
> Thanks in advance
Angel,
I am not sure how your code is running at all.
The above T-SQL returns:
SELECT DATEA.Net SqlClient Data Provider: Msg 155, Level 15, State
2, Line 1
'varChar' is not a recognized CURSOR option.
.Net SqlClient Data Provider: Msg 155, Level 15, State 1, Line 5
'myDatePart' is not a recognized dateadd option.DD(myDatePart, 1,
GETDATE())
For me.
One thing that comes to eye immediately is that there are no "@."
symbols in front of the variable names.
Try:
DECLARE @.myDatePart varChar(1)
SET @.myDatePart = "d"
As for DateAdd, the format is not:
dateadd('d', 1, GetDate())
It is:
dateadd(d, 1, GetDate())
And preferably:
dateadd(d, 1, CURRENT_TIMESTAMP)
Given that DateAdd is looking for an undelimited string literal, I
don't think you can use a variable in this parameter location (but I
don't know that for sure).
Sincerely,
Chris O.|||Security considerations aside, this does the trick:
CREATE PROCEDURE test4 @.dp char(1)
AS
BEGIN
DECLARE @.querystring nvarchar(512)
SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
EXEC sp_executesql @.querystring
END
EXEC dbo.test4 @.dp='d'
What's strange is using sp_executesql another way does not work. For example
execute sp_executesql
N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
N'@.daystoadd int',
@.daystoadd = 4
This will work; passing the second parameter @.daystoadd as an integer. But
execute sp_executesql
N'SELECT DATEADD(@.dp, 4, GETDATE())',
N'@.dp varchar(4)',
@.dp = 'dd'
gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyone
?
"Angel" wrote:

> How can I pass a datepart to my personal function or stored procedure.
> In other words I want to allow a user to pass a datepart such as to comput
e
> by day year, etc... The problem is that when I use the variable that has t
he
> datepart value ie: "d" or "m" or "y" it fails to recognize it. This is a
> sample of what I am trying to do:
> DECLARE myDatePart varChar(1)
> SET myDatePart = "d"
> SELECT DATEADD(myDatePart, 1, GETDATE())
> I Am trying to create my own datepart function. I do not want to create an
> entire if statement to determine what type of datepart is passed into my
> procedure.
> Thanks in advance|||You might not have wanted to use IF statements, but CASE works well enough
for a finite set of ten possibilities
CREATE PROCEDURE test6 @.dp char(2), @.count int, @.dateout datetime OUTPUT
AS
BEGIN
IF (@.dp NOT IN ('yy','qq','mm','dy','wk','dd','hh','mi'
,'ss','ms'))
BEGIN
RAISERROR('wrong date format',16,1)
RETURN 1
END
ELSE
BEGIN
SELECT @.dateout =
CASE
WHEN @.dp = 'yy' THEN DATEADD(yy,@.count,GETDATE())
WHEN @.dp = 'qq' THEN DATEADD(qq,@.count,GETDATE())
WHEN @.dp = 'mm' THEN DATEADD(mm,@.count,GETDATE())
WHEN @.dp = 'dy' THEN DATEADD(dy,@.count,GETDATE())
WHEN @.dp = 'wk' THEN DATEADD(wk,@.count,GETDATE())
WHEN @.dp = 'dd' THEN DATEADD(dd,@.count,GETDATE())
WHEN @.dp = 'hh' THEN DATEADD(hh,@.count,GETDATE())
WHEN @.dp = 'mi' THEN DATEADD(mi,@.count,GETDATE())
WHEN @.dp = 'ss' THEN DATEADD(ss,@.count,GETDATE())
WHEN @.dp = 'ms' THEN DATEADD(ms,@.count,GETDATE())
END
END
END
DECLARE @.dateout datetime
EXEC dbo.test6 'yy', 9, @.dateout OUTPUT
SELECT @.dateout
"Angel" wrote:
> That is a good idea but I need to get the result of the DateAdd into a
> variable within the same stored procedure.
> thanks in advance
> "Mark Williams" wrote:
>|||That is a good idea but I need to get the result of the DateAdd into a
variable within the same stored procedure.
thanks in advance
"Mark Williams" wrote:
> Security considerations aside, this does the trick:
> CREATE PROCEDURE test4 @.dp char(1)
> AS
> BEGIN
> DECLARE @.querystring nvarchar(512)
> SET @.querystring = 'SELECT DATEADD(' + @.dp + ', 1, GETDATE())'
> EXEC sp_executesql @.querystring
> END
> EXEC dbo.test4 @.dp='d'
> What's strange is using sp_executesql another way does not work. For examp
le
> execute sp_executesql
> N'SELECT DATEADD(dd, @.daystoadd, GETDATE())',
> N'@.daystoadd int',
> @.daystoadd = 4
> This will work; passing the second parameter @.daystoadd as an integer. But
> execute sp_executesql
> N'SELECT DATEADD(@.dp, 4, GETDATE())',
> N'@.dp varchar(4)',
> @.dp = 'dd'
> gets the error "Invalid parameter 1 specified for dateadd." Any ideas anyo
ne?
> "Angel" wrote:
>

passing \ as a parameter to a function

I have a UDF for splitting delimiter strings:
CREATE FUNCTION Split
(@.Source varchar (5000)
,@.Delimiter varchar (10) = ','
)
RETURNS @.T table (F1 varchar (100))
AS
--Accepts a source string @.Source and parses it to break it up into single
units
--delineated by @.Delimiter.
--Returns a Single Column Table with each row containing one of the split
chunks
--e.g. @.Source = 'SP,AQ,YD'
-- Returns @.T with three rows:
-- SP
-- AQ
-- YD
-- or @.Source = 'P1=V1, P2=V2'
-- Returns @.T with two rows:
-- P1=V1
-- P2=V2
BEGIN
DECLARE @.w varchar (5000)
DECLARE @.inte int
SET @.W = @.Source + @.Delimiter
WHILE len(@.W) > 0
BEGIN
SET @.inte = patindex('%,%',@.w) - 1
INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
END
RETURN
END
A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
the @.Reps table would have three records with one of the names in each.
e.g. Fred
Joe
Andy
It can also be called "inline":
SELECT s.* FROM tblSales s
INNER JOIN (SELECT * FROM split(@.Reps,',') r
ON s.Rep = r.F1
This all works fine until I try to call it using '\' as the delimiter
parameter, then I just get an error that says "Invalid length parameter
passed to the substring function"
Here is sample code to run this:
DECLARE @.NewPath varchar (100)
--Use this pair and it works
-- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
-- SELECT * FROM split(@.NewPath, ',')
--Use this pair and it fails
SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
SELECT * FROM split(@.NewPath, '\')
Sorry to be so long winded, but does anyone have any ideas?
Regards,
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"Hi
I have modified a little bit the function written by Dejan Sarka.
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000),@.delim VARCHAR(2))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(@.delim,@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(@.d
elim),8000)
END
RETURN
END
GO
--A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
declare @.RepSelect varchar(50)
set @.RepSelect='Fred\Joe\Andy'
INSERT @.Reps (RepIn) SELECT * FROM TsqlSplit(@.RepSelect,'')
select * from @.Reps
"RMarmion" <RMarmion@.Discussions.Microsoft.com> wrote in message
news:76AF1578-99B3-45FE-A24E-4D82B5435CF8@.microsoft.com...
>I have a UDF for splitting delimiter strings:
> CREATE FUNCTION Split
> (@.Source varchar (5000)
> ,@.Delimiter varchar (10) = ','
> )
> RETURNS @.T table (F1 varchar (100))
> AS
> --Accepts a source string @.Source and parses it to break it up into
> single
> units
> --delineated by @.Delimiter.
> --Returns a Single Column Table with each row containing one of the split
> chunks
> --e.g. @.Source = 'SP,AQ,YD'
> -- Returns @.T with three rows:
> -- SP
> -- AQ
> -- YD
> -- or @.Source = 'P1=V1, P2=V2'
> -- Returns @.T with two rows:
> -- P1=V1
> -- P2=V2
> BEGIN
> DECLARE @.w varchar (5000)
> DECLARE @.inte int
> SET @.W = @.Source + @.Delimiter
> WHILE len(@.W) > 0
> BEGIN
> SET @.inte = patindex('%,%',@.w) - 1
> INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
> SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
> END
> RETURN
> END
> A typical use of this would be:
> DECLARE @.Reps table (RepIn varchar (20))
> INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
> Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
> the @.Reps table would have three records with one of the names in each.
> e.g. Fred
> Joe
> Andy
> It can also be called "inline":
> SELECT s.* FROM tblSales s
> INNER JOIN (SELECT * FROM split(@.Reps,',') r
> ON s.Rep = r.F1
> This all works fine until I try to call it using '' as the delimiter
> parameter, then I just get an error that says "Invalid length parameter
> passed to the substring function"
> Here is sample code to run this:
> DECLARE @.NewPath varchar (100)
> --Use this pair and it works
> -- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
> -- SELECT * FROM split(@.NewPath, ',')
> --Use this pair and it fails
> SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
> SELECT * FROM split(@.NewPath, '')
> Sorry to be so long winded, but does anyone have any ideas?
> Regards,
> -Rob
> --
> Robert Marmion
> ITBridges Inc
> 609 844 0949
> "Connecting your Business with your Software"|||Here lies the pain:
> SET @.inte = patindex('%,%',@.w) - 1
You're still looking for the comma. And, BTW, you could just as well use
CHARINDEX.
ML|||Duh!
Thank you both so much. What a stupid error!!
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"
"ML" wrote:

> Here lies the pain:
> You're still looking for the comma. And, BTW, you could just as well use
> CHARINDEX.
>
> ML

pass variable to identity function

is it possible to pass a variable to an identity funtion

example

declare @.max_note int

select @.max_note = max(key ) from notes

select m_key = identity( int, @.max_note, 1),
name

into #prod_note

from prod_note

this one challeged me a lot. heres a solution

insert into #temp

delete from #temp

and do insert again

USE NORTHWIND
declare @.cmd nvarchar(4000)
declare @.max_note int
select @.max_note =max(employeeid ) from employees
SELECT IDENTITY(int, 1,1) AS IDNUM,LASTNAME INTO #TEMP FROM EMPLOYEES
--SELECT * FROM #TEMP
DELETE FROM #TEMP
INSERT #TEMP(LASTNAME)
SELECT LASTNAME FROM EMPLOYEES
SELECT * FROM #TEMP

DROP TABLE #TEMP

not the best solution but works the same

|||

Identity function doesn't support variables for the seed or increment. So you have several options since you are creating a temporary table:

1. Create the temporary table without the identity column and then do an ALTER TABLE to add the identity column using dynamic SQL like:

exec('alter table #prod_note add m_key int identity(' + @.max_note_val + ', 1)')

2. Create an empty table using CREATE TABLE and then use DBCC CHECKIDENT (this accepts variable for seed parameter) to reseed the table before doing the insert. This will be fine if you are dumping small number of rows into the temporary table & performance is not the main criteria. The main reason being that SELECT INTO is the fastest way to create a temporary table with data and doing CREATE followed by INSERT...SELECT will be slower.

|||This answer above is awesome!

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>

Pass Table as a parameter to a function

Hi Friends,
Is it possible to pass a table as a parameter to a funtion.

whos function declaration would look some thing like this...
ALTER FUNCTION TempFunction (@.TempTable TABLE, @.nPId INT)

my problem is: i have to access a temporary table created in an SP in
a function

ALTER PROCEDURE MySP
BEGIN
...
DECLARE @.TmpTable TABLE(...)
...
TempFunction(@.TmpTable)
...
END

Thanks
ArunDhaJArunDhaJ (arundhaj@.gmail.com) writes:

Quote:

Originally Posted by

Is it possible to pass a table as a parameter to a funtion.

It should be in SQL 2008, which currently is in beta. The functionality
is available in the current CTP, but I have not played with it, so I
can't say for sure that it works with functions.

Quote:

Originally Posted by

my problem is: i have to access a temporary table created in an SP in
a function
>
>
ALTER PROCEDURE MySP
BEGIN
...
DECLARE @.TmpTable TABLE(...)
...
TempFunction(@.TmpTable)
...
END

You probably need to rewrite the function as a procedure. See here for
some tips of passing data between stored procedures in current SQL versions.
http://www.sommarskog.se/share_data.html
--
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|||Here you go and it's not wishful thinking -:)

http://beyondsql.blogspot.com/2007/...-parameter.html
www.beyondsql.blogspot.com|||As already suggested a procedure will work. We have a procedure to
drop all default constraints from our tables. I pass in a list of
tables to this procedure (the list of tables are loaded in to a table
variable by calling a function). If you need further help then re-post
and I can hopefully give you an example!

Paul|||On Jul 26, 3:20 pm, Paul <paulwragg2...@.hotmail.comwrote:

Quote:

Originally Posted by

As already suggested a procedure will work. We have a procedure to
drop all default constraints from our tables. I pass in a list of
tables to this procedure (the list of tables are loaded in to a table
variable by calling a function). If you need further help then re-post
and I can hopefully give you an example!
>
Paul

Hi All,
Thanks for your response.. :)

Ya.. the procedure will do fine...
But my question is that is there any performance difference between
using functions and procedure...

Thanks
ArunDhaJ|||ArunDhaJ (arundhaj@.gmail.com) writes:

Quote:

Originally Posted by

Ya.. the procedure will do fine...
But my question is that is there any performance difference between
using functions and procedure...

That all depends on how you implement and use them. You can't say that any
is faster than the other as such.

--
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|||>No; it would much more likely mean that he wants to pass a set of values to his function. <<

Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.

Quote:

Originally Posted by

Quote:

Originally Posted by

> the ability to simply pass a set would make things enormously easier, faster, and cleaner - which is probably why they're including it in SQL 2008. <<

Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I can think of several scenarios in which doing exactly what he is

asking would be necessary - reporting being the most obvious. <<

The most obvious is a system utility program which treats all tables
as tables rather than as part of a logical model. Now you are at the
meta data level, which has no place in an application or RDBMS
schema.|||On Jul 31, 10:00 am, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

No; it would much more likely mean that he wants to pass a set of values to his function. <<

>
Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.

And do you suppose his interest is in the table itself, or the set of
data that the table contains?

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

the ability to simply pass a set would make things enormously easier, faster, and cleaner - which is probably why they're including it in SQL 2008. <<

>
Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.

Clearly you did not understand the example I gave you. I wasn't
talking about passing many parameters, I was talking about passing one
parameter that can have many values. A drop-down list where the user
can select more than one value. In other words, a set. This is an
EXTREMELY common scenario in the real world.

Classroom coders who have little to no development experience in the
real world tend to panic at the thought of examples that are outside
of their limited experience :b|||On Jul 31, 9:00 am, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

No; it would much more likely mean that he wants to pass a set of values to his function. <<

>
Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

the ability to simply pass a set would make things enormously easier, faster, and cleaner - which is probably why they're including it in SQL 2008. <<

>
Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.
>

Procedures with long lists of parameters simply cannot be fully tested
in reasonable time. Just think how many permutations are there for
1000 nullable parameters. Any responsible professional will avoid
using untested code in real life...|||> Procedures with long lists of parameters simply cannot be fully tested in reasonable time. Just think how many permutations are there for 1000 NULL- able parameters. <<

I prefer the "Rule of seven plus or minus two" (http://www.musanim.com/
miller1956/) for a parameter list -- classic Software Engineering.

But you do not test all permutations in a repeated group. I can see
that "p001" to "p999" are all integers, that they are loaded into a
table named "Parts" and are therefore subject to the constraints on
that table. That is simple induction and set-oriented programming.

Quote:

Originally Posted by

Quote:

Originally Posted by

> Any responsible professional will avoid using untested code in real life...<<

Agreed. I wish there were more them than "Agile Programmers" :) That
is why I like Dijkstra, Mana, Gries, et al -- I want my code to be
provably correct.

I did QA for weapons systems in my youth. I was probably shooting at
you :)|||

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I wasn't talking about passing many parameters, I was talking about passing one parameter that can have many values. <<

>
No, it cannot have many values bey definition. Parameters have to be
a scalar value. At one point in ANSI we talked about passing tables
in the SQL/PSM and decided against it. Defining comparisons, the
parameter declarations and constraints, use of VIEWs, etc. made SQL
injection look like a blessing.

The problem with the current method is that it *is* a scalar value.
Reporting Services creates a comma delimited string containing all of
the values selected by the user. This can pose problems when there
are large numbers of values selected. The ability to pass a set would
be a great benefit, and would not require any changes to the way data
is stored.

That aside, your statement that procedures can have many parameters is
meaningless - it doesn't even remotely address the question. Either
you didn't understand the question, or you simply spit out one of your
standard cookie-cutter replies that you felt could best be wedged into
the discussion.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

A drop-down list where the user can select more than one value. <<

>
Gee, I looked all over my SQL Standards and books, but could not find
a drop-down list mentioned. Are you sure that is not part of the
front end and not something which a good programmer would in the
database :)?

The drop-down list *is* in the front end; which is Reporting
Services. Reporting Services uses SQL queries or stored procedures to
pull data. Therefore - stay with me, Joe - those queries/procedures
have to be written to accomidate multiple selections for a drop down
menu.

I strongly suspect that most businesses aren't going to stop using
this functionality just because you say it shouldn't work or that it
isn't standard; nor are they going to wait for Microsoft to create
some additional tier in between Reporting Services and the database;
especially when such a tier is not needed.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

This is an EXTREMELY common scenario in the real world. <<

>
Yes, in the applications side of the real world, not the database.
Hey, there is nothing wrong with being an application programmer. But
it is a different tier.

In this case the only efficient means of getting the data with the
parameters needed is via SQL script or stored procedure; in either
case requiring that SQL be written to handle multiple selections.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Classroom coders who have little to no development experience in the real world tend to panic at the thought of examples that are outside of their limited experience :b <<

>
LOL! I have been gathering "limited experience" for over 35 years
now! And I have had some influence on RDBMS over the last few
decades. Instead of being a "code monkey" any more, I get called in
to train progammers, design DBs and repair disasters. Part of me
misses the programming discipline of a military weapons or medical
records system. If it screws up even a little or if it goes down, the
wrong people die.

How many years has it been since you've done any real work in the
field?

[snip]

Quote:

Originally Posted by

A volunteer programmer did a pull-down list where the package options
were in a comma separated list column in the DB. It made his display
easier. But it messed up the pick list when smaller units were
available. People thought they were asking for 100 units, but it
became 10 units in the backend.
>
His little violation of 1NF and blending of tiers meant that field
medical personnel had to decide which children would and would not get
antibiotics.

What he did has nothing to do with anything I'm currently talking
about. I haven't said anything about storing comma delimited lists in
columns in the database, or anything like that. Nothing like that is
even necessary. In fact, nothing I am talking about requires any
change whatsoever in how the data is stored.

As you so often do, you are now pulling out and emotionally charged
disaster scenario that is at best superficially related to the topic
at hand. I tell you that it'd be nice to be able to pass a set as a
parameter, and you go into a story about how a bunch of kids didn't
get medicine because some bad programmer made an obvious error that
spread across tiers. Sad story, no doubt, but it has nothing to do
with what we're talking about.|||Shuurai wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>> I wasn't talking about passing many parameters, I was talking about passing one parameter that can have many values. <<

>No, it cannot have many values bey definition. Parameters have to be
>a scalar value. At one point in ANSI we talked about passing tables
>in the SQL/PSM and decided against it. Defining comparisons, the
>parameter declarations and constraints, use of VIEWs, etc. made SQL
>injection look like a blessing.

>
The problem with the current method is that it *is* a scalar value.
Reporting Services creates a comma delimited string containing all of
the values selected by the user. This can pose problems when there
are large numbers of values selected. The ability to pass a set would
be a great benefit, and would not require any changes to the way data
is stored.

I tend to agree with you - provided that the procedure can define what
type of set is valid, thus avoiding the Squids objection. On the other
hand, if the user /needs/ to select /large/ numbers of values, then
someone should try to refactor the overall system to eliminate that
need; by adding a few appropriate classifying attributes, the user may
be able to select just one or a few such attributes, and the DB can
compute the large list.|||Comparing the CSV approach with the parameter approach below, which one do
you consider more maintainable and supportable?

Also consider the application will need one line per parameter used on the
stored procedure.

This which takes milliseconds to edit in Management Studio...

create proc getdata_dynamic
@.csv varchar(max)

as
set @.csv = replace( @.csv, '''', ''' )

exec( ' DECLARE @.c int
select @.c = count(*)
from somedata
where avalue in ( ' + @.csv + ' )'
)
go

Or this which takes over two minutes to edit in Management Studio (on a 4GB
dual proc AMD machine)...

create proc [dbo].[getdata_parms] @.p1 int, @.p2 int, @.p3 int, @.p4 int, @.p5
int, @.p6 int, @.p7 int, @.p8 int, @.p9 int, @.p10 int, @.p11 int, @.p12 int, @.p13
int, @.p14 int, @.p15 int, @.p16 int, @.p17 int, @.p18 int, @.p19 int, @.p20 int,
@.p21 int, @.p22 int, @.p23 int, @.p24 int, @.p25 int, @.p26 int, @.p27 int, @.p28
int, @.p29 int, @.p30 int, @.p31 int, @.p32 int, @.p33 int, @.p34 int, @.p35 int,
@.p36 int, @.p37 int, @.p38 int, @.p39 int, @.p40 int, @.p41 int, @.p42 int, @.p43
int, @.p44 int, @.p45 int, @.p46 int, @.p47 int, @.p48 int, @.p49 int, @.p50 int,
@.p51 int, @.p52 int, @.p53 int, @.p54 int, @.p55 int, @.p56 int, @.p57 int, @.p58
int, @.p59 int, @.p60 int, @.p61 int, @.p62 int, @.p63 int, @.p64 int, @.p65 int,
@.p66 int, @.p67 int, @.p68 int, @.p69 int, @.p70 int, @.p71 int, @.p72 int, @.p73
int, @.p74 int, @.p75 int, @.p76 int, @.p77 int, @.p78 int, @.p79 int, @.p80 int,
@.p81 int, @.p82 int, @.p83 int, @.p84 int, @.p85 int, @.p86 int, @.p87 int, @.p88
int, @.p89 int, @.p90 int, @.p91 int, @.p92 int, @.p93 int, @.p94 int, @.p95 int,
@.p96 int, @.p97 int, @.p98 int, @.p99 int, @.p100 int, @.p101 int, @.p102 int,
@.p103 int, @.p104 int, @.p105 int, @.p106 int, @.p107 int, @.p108 int, @.p109 int,
@.p110 int, @.p111 int, @.p112 int, @.p113 int, @.p114 int, @.p115 int, @.p116 int,
@.p117 int, @.p118 int, @.p119 int, @.p120 int, @.p121 int, @.p122 int, @.p123 int,
@.p124 int, @.p125 int, @.p126 int, @.p127 int, @.p128 int, @.p129 int, @.p130 int,
@.p131 int, @.p132 int, @.p133 int, @.p134 int, @.p135 int, @.p136 int, @.p137 int,
@.p138 int, @.p139 int, @.p140 int, @.p141 int, @.p142 int, @.p143 int, @.p144 int,
@.p145 int, @.p146 int, @.p147 int, @.p148 int, @.p149 int, @.p150 int, @.p151 int,
@.p152 int, @.p153 int, @.p154 int, @.p155 int, @.p156 int, @.p157 int, @.p158 int,
@.p159 int, @.p160 int, @.p161 int, @.p162 int, @.p163 int, @.p164 int, @.p165 int,
@.p166 int, @.p167 int, @.p168 int, @.p169 int, @.p170 int, @.p171 int, @.p172 int,
@.p173 int, @.p174 int, @.p175 int, @.p176 int, @.p177 int, @.p178 int, @.p179 int,
@.p180 int, @.p181 int, @.p182 int, @.p183 int, @.p184 int, @.p185 int, @.p186 int,
@.p187 int, @.p188 int, @.p189 int, @.p190 int, @.p191 int, @.p192 int, @.p193 int,
@.p194 int, @.p195 int, @.p196 int, @.p197 int, @.p198 int, @.p199 int, @.p200 int,
@.p201 int, @.p202 int, @.p203 int, @.p204 int, @.p205 int, @.p206 int, @.p207 int,
@.p208 int, @.p209 int, @.p210 int, @.p211 int, @.p212 int, @.p213 int, @.p214 int,
@.p215 int, @.p216 int, @.p217 int, @.p218 int, @.p219 int, @.p220 int, @.p221 int,
@.p222 int, @.p223 int, @.p224 int, @.p225 int, @.p226 int, @.p227 int, @.p228 int,
@.p229 int, @.p230 int, @.p231 int, @.p232 int, @.p233 int, @.p234 int, @.p235 int,
@.p236 int, @.p237 int, @.p238 int, @.p239 int, @.p240 int, @.p241 int, @.p242 int,
@.p243 int, @.p244 int, @.p245 int, @.p246 int, @.p247 int, @.p248 int, @.p249 int,
@.p250 int, @.p251 int, @.p252 int, @.p253 int, @.p254 int, @.p255 int, @.p256 int,
@.p257 int, @.p258 int, @.p259 int, @.p260 int, @.p261 int, @.p262 int, @.p263 int,
@.p264 int, @.p265 int, @.p266 int, @.p267 int, @.p268 int, @.p269 int, @.p270 int,
@.p271 int, @.p272 int, @.p273 int, @.p274 int, @.p275 int, @.p276 int, @.p277 int,
@.p278 int, @.p279 int, @.p280 int, @.p281 int, @.p282 int, @.p283 int, @.p284 int,
@.p285 int, @.p286 int, @.p287 int, @.p288 int, @.p289 int, @.p290 int, @.p291 int,
@.p292 int, @.p293 int, @.p294 int, @.p295 int, @.p296 int, @.p297 int, @.p298 int,
@.p299 int, @.p300 int, @.p301 int, @.p302 int, @.p303 int, @.p304 int, @.p305 int,
@.p306 int, @.p307 int, @.p308 int, @.p309 int, @.p310 int, @.p311 int, @.p312 int,
@.p313 int, @.p314 int, @.p315 int, @.p316 int, @.p317 int, @.p318 int, @.p319 int,
@.p320 int, @.p321 int, @.p322 int, @.p323 int, @.p324 int, @.p325 int, @.p326 int,
@.p327 int, @.p328 int, @.p329 int, @.p330 int, @.p331 int, @.p332 int, @.p333 int,
@.p334 int, @.p335 int, @.p336 int, @.p337 int, @.p338 int, @.p339 int, @.p340 int,
@.p341 int, @.p342 int, @.p343 int, @.p344 int, @.p345 int, @.p346 int, @.p347 int,
@.p348 int, @.p349 int, @.p350 int, @.p351 int, @.p352 int, @.p353 int, @.p354 int,
@.p355 int, @.p356 int, @.p357 int, @.p358 int, @.p359 int, @.p360 int, @.p361 int,
@.p362 int, @.p363 int, @.p364 int, @.p365 int, @.p366 int, @.p367 int, @.p368 int,
@.p369 int, @.p370 int, @.p371 int, @.p372 int, @.p373 int, @.p374 int, @.p375 int,
@.p376 int, @.p377 int, @.p378 int, @.p379 int, @.p380 int, @.p381 int, @.p382 int,
@.p383 int, @.p384 int, @.p385 int, @.p386 int, @.p387 int, @.p388 int, @.p389 int,
@.p390 int, @.p391 int, @.p392 int, @.p393 int, @.p394 int, @.p395 int, @.p396 int,
@.p397 int, @.p398 int, @.p399 int, @.p400 int, @.p401 int, @.p402 int, @.p403 int,
@.p404 int, @.p405 int, @.p406 int, @.p407 int, @.p408 int, @.p409 int, @.p410 int,
@.p411 int, @.p412 int, @.p413 int, @.p414 int, @.p415 int, @.p416 int, @.p417 int,
@.p418 int, @.p419 int, @.p420 int, @.p421 int, @.p422 int, @.p423 int, @.p424 int,
@.p425 int, @.p426 int, @.p427 int, @.p428 int, @.p429 int, @.p430 int, @.p431 int,
@.p432 int, @.p433 int, @.p434 int, @.p435 int, @.p436 int, @.p437 int, @.p438 int,
@.p439 int, @.p440 int, @.p441 int, @.p442 int, @.p443 int, @.p444 int, @.p445 int,
@.p446 int, @.p447 int, @.p448 int, @.p449 int, @.p450 int, @.p451 int, @.p452 int,
@.p453 int, @.p454 int, @.p455 int, @.p456 int, @.p457 int, @.p458 int, @.p459 int,
@.p460 int, @.p461 int, @.p462 int, @.p463 int, @.p464 int, @.p465 int, @.p466 int,
@.p467 int, @.p468 int, @.p469 int, @.p470 int, @.p471 int, @.p472 int, @.p473 int,
@.p474 int, @.p475 int, @.p476 int, @.p477 int, @.p478 int, @.p479 int, @.p480 int,
@.p481 int, @.p482 int, @.p483 int, @.p484 int, @.p485 int, @.p486 int, @.p487 int,
@.p488 int, @.p489 int, @.p490 int, @.p491 int, @.p492 int, @.p493 int, @.p494 int,
@.p495 int, @.p496 int, @.p497 int, @.p498 int, @.p499 int, @.p500 int, @.p501 int,
@.p502 int, @.p503 int, @.p504 int, @.p505 int, @.p506 int, @.p507 int, @.p508 int,
@.p509 int, @.p510 int, @.p511 int, @.p512 int, @.p513 int, @.p514 int, @.p515 int,
@.p516 int, @.p517 int, @.p518 int, @.p519 int, @.p520 int, @.p521 int, @.p522 int,
@.p523 int, @.p524 int, @.p525 int, @.p526 int, @.p527 int, @.p528 int, @.p529 int,
@.p530 int, @.p531 int, @.p532 int, @.p533 int, @.p534 int, @.p535 int, @.p536 int,
@.p537 int, @.p538 int, @.p539 int, @.p540 int, @.p541 int, @.p542 int, @.p543 int,
@.p544 int, @.p545 int, @.p546 int, @.p547 int, @.p548 int, @.p549 int, @.p550 int,
@.p551 int, @.p552 int, @.p553 int, @.p554 int, @.p555 int, @.p556 int, @.p557 int,
@.p558 int, @.p559 int, @.p560 int, @.p561 int, @.p562 int, @.p563 int, @.p564 int,
@.p565 int, @.p566 int, @.p567 int, @.p568 int, @.p569 int, @.p570 int, @.p571 int,
@.p572 int, @.p573 int, @.p574 int, @.p575 int, @.p576 int, @.p577 int, @.p578 int,
@.p579 int, @.p580 int, @.p581 int, @.p582 int, @.p583 int, @.p584 int, @.p585 int,
@.p586 int, @.p587 int, @.p588 int, @.p589 int, @.p590 int, @.p591 int, @.p592 int,
@.p593 int, @.p594 int, @.p595 int, @.p596 int, @.p597 int, @.p598 int, @.p599 int,
@.p600 int, @.p601 int, @.p602 int, @.p603 int, @.p604 int, @.p605 int, @.p606 int,
@.p607 int, @.p608 int, @.p609 int, @.p610 int, @.p611 int, @.p612 int, @.p613 int,
@.p614 int, @.p615 int, @.p616 int, @.p617 int, @.p618 int, @.p619 int, @.p620 int,
@.p621 int, @.p622 int, @.p623 int, @.p624 int, @.p625 int, @.p626 int, @.p627 int,
@.p628 int, @.p629 int, @.p630 int, @.p631 int, @.p632 int, @.p633 int, @.p634 int,
@.p635 int, @.p636 int, @.p637 int, @.p638 int, @.p639 int, @.p640 int, @.p641 int,
@.p642 int, @.p643 int, @.p644 int, @.p645 int, @.p646 int, @.p647 int, @.p648 int,
@.p649 int, @.p650 int, @.p651 int, @.p652 int, @.p653 int, @.p654 int, @.p655 int,
@.p656 int, @.p657 int, @.p658 int, @.p659 int, @.p660 int, @.p661 int, @.p662 int,
@.p663 int, @.p664 int, @.p665 int, @.p666 int, @.p667 int, @.p668 int, @.p669 int,
@.p670 int, @.p671 int, @.p672 int, @.p673 int, @.p674 int, @.p675 int, @.p676 int,
@.p677 int, @.p678 int, @.p679 int, @.p680 int, @.p681 int, @.p682 int, @.p683 int,
@.p684 int, @.p685 int, @.p686 int, @.p687 int, @.p688 int, @.p689 int, @.p690 int,
@.p691 int, @.p692 int, @.p693 int, @.p694 int, @.p695 int, @.p696 int, @.p697 int,
@.p698 int, @.p699 int, @.p700 int, @.p701 int, @.p702 int, @.p703 int, @.p704 int,
@.p705 int, @.p706 int, @.p707 int, @.p708 int, @.p709 int, @.p710 int, @.p711 int,
@.p712 int, @.p713 int, @.p714 int, @.p715 int, @.p716 int, @.p717 int, @.p718 int,
@.p719 int, @.p720 int, @.p721 int, @.p722 int, @.p723 int, @.p724 int, @.p725 int,
@.p726 int, @.p727 int, @.p728 int, @.p729 int, @.p730 int, @.p731 int, @.p732 int,
@.p733 int, @.p734 int, @.p735 int, @.p736 int, @.p737 int, @.p738 int, @.p739 int,
@.p740 int, @.p741 int, @.p742 int, @.p743 int, @.p744 int, @.p745 int, @.p746 int,
@.p747 int, @.p748 int, @.p749 int, @.p750 int, @.p751 int, @.p752 int, @.p753 int,
@.p754 int, @.p755 int, @.p756 int, @.p757 int, @.p758 int, @.p759 int, @.p760 int,
@.p761 int, @.p762 int, @.p763 int, @.p764 int, @.p765 int, @.p766 int, @.p767 int,
@.p768 int, @.p769 int, @.p770 int, @.p771 int, @.p772 int, @.p773 int, @.p774 int,
@.p775 int, @.p776 int, @.p777 int, @.p778 int, @.p779 int, @.p780 int, @.p781 int,
@.p782 int, @.p783 int, @.p784 int, @.p785 int, @.p786 int, @.p787 int, @.p788 int,
@.p789 int, @.p790 int, @.p791 int, @.p792 int, @.p793 int, @.p794 int, @.p795 int,
@.p796 int, @.p797 int, @.p798 int, @.p799 int, @.p800 int, @.p801 int, @.p802 int,
@.p803 int, @.p804 int, @.p805 int, @.p806 int, @.p807 int, @.p808 int, @.p809 int,
@.p810 int, @.p811 int, @.p812 int, @.p813 int, @.p814 int, @.p815 int, @.p816 int,
@.p817 int, @.p818 int, @.p819 int, @.p820 int, @.p821 int, @.p822 int, @.p823 int,
@.p824 int, @.p825 int, @.p826 int, @.p827 int, @.p828 int, @.p829 int, @.p830 int,
@.p831 int, @.p832 int, @.p833 int, @.p834 int, @.p835 int, @.p836 int, @.p837 int,
@.p838 int, @.p839 int, @.p840 int, @.p841 int, @.p842 int, @.p843 int, @.p844 int,
@.p845 int, @.p846 int, @.p847 int, @.p848 int, @.p849 int, @.p850 int, @.p851 int,
@.p852 int, @.p853 int, @.p854 int, @.p855 int, @.p856 int, @.p857 int, @.p858 int,
@.p859 int, @.p860 int, @.p861 int, @.p862 int, @.p863 int, @.p864 int, @.p865 int,
@.p866 int, @.p867 int, @.p868 int, @.p869 int, @.p870 int, @.p871 int, @.p872 int,
@.p873 int, @.p874 int, @.p875 int, @.p876 int, @.p877 int, @.p878 int, @.p879 int,
@.p880 int, @.p881 int, @.p882 int, @.p883 int, @.p884 int, @.p885 int, @.p886 int,
@.p887 int, @.p888 int, @.p889 int, @.p890 int, @.p891 int, @.p892 int, @.p893 int,
@.p894 int, @.p895 int, @.p896 int, @.p897 int, @.p898 int, @.p899 int, @.p900 int,
@.p901 int, @.p902 int, @.p903 int, @.p904 int, @.p905 int, @.p906 int, @.p907 int,
@.p908 int, @.p909 int, @.p910 int, @.p911 int, @.p912 int, @.p913 int, @.p914 int,
@.p915 int, @.p916 int, @.p917 int, @.p918 int, @.p919 int, @.p920 int, @.p921 int,
@.p922 int, @.p923 int, @.p924 int, @.p925 int, @.p926 int, @.p927 int, @.p928 int,
@.p929 int, @.p930 int, @.p931 int, @.p932 int, @.p933 int, @.p934 int, @.p935 int,
@.p936 int, @.p937 int, @.p938 int, @.p939 int, @.p940 int, @.p941 int, @.p942 int,
@.p943 int, @.p944 int, @.p945 int, @.p946 int, @.p947 int, @.p948 int, @.p949 int,
@.p950 int, @.p951 int, @.p952 int, @.p953 int, @.p954 int, @.p955 int, @.p956 int,
@.p957 int, @.p958 int, @.p959 int, @.p960 int, @.p961 int, @.p962 int, @.p963 int,
@.p964 int, @.p965 int, @.p966 int, @.p967 int, @.p968 int, @.p969 int, @.p970 int,
@.p971 int, @.p972 int, @.p973 int, @.p974 int, @.p975 int, @.p976 int, @.p977 int,
@.p978 int, @.p979 int, @.p980 int, @.p981 int, @.p982 int, @.p983 int, @.p984 int,
@.p985 int, @.p986 int, @.p987 int, @.p988 int, @.p989 int, @.p990 int, @.p991 int,
@.p992 int, @.p993 int, @.p994 int, @.p995 int, @.p996 int, @.p997 int, @.p998 int,
@.p999 int, @.p1000 int
as
declare @.c int;
select @.c = count(*)
from somedata
where avalue in ( 1, @.p2, @.p3, @.p4, @.p5, @.p6, @.p7, @.p8, @.p9, @.p10, @.p11,
@.p12, @.p13, @.p14, @.p15, @.p16, @.p17, @.p18, @.p19, @.p20, @.p21, @.p22, @.p23,
@.p24, @.p25, @.p26, @.p27, @.p28, @.p29, @.p30, @.p31, @.p32, @.p33, @.p34, @.p35,
@.p36, @.p37, @.p38, @.p39, @.p40, @.p41, @.p42, @.p43, @.p44, @.p45, @.p46, @.p47,
@.p48, @.p49, @.p50, @.p51, @.p52, @.p53, @.p54, @.p55, @.p56, @.p57, @.p58, @.p59,
@.p60, @.p61, @.p62, @.p63, @.p64, @.p65, @.p66, @.p67, @.p68, @.p69, @.p70, @.p71,
@.p72, @.p73, @.p74, @.p75, @.p76, @.p77, @.p78, @.p79, @.p80, @.p81, @.p82, @.p83,
@.p84, @.p85, @.p86, @.p87, @.p88, @.p89, @.p90, @.p91, @.p92, @.p93, @.p94, @.p95,
@.p96, @.p97, @.p98, @.p99, @.p100, @.p101, @.p102, @.p103, @.p104, @.p105, @.p106,
@.p107, @.p108, @.p109, @.p110, @.p111, @.p112, @.p113, @.p114, @.p115, @.p116, @.p117,
@.p118, @.p119, @.p120, @.p121, @.p122, @.p123, @.p124, @.p125, @.p126, @.p127, @.p128,
@.p129, @.p130, @.p131, @.p132, @.p133, @.p134, @.p135, @.p136, @.p137, @.p138, @.p139,
@.p140, @.p141, @.p142, @.p143, @.p144, @.p145, @.p146, @.p147, @.p148, @.p149, @.p150,
@.p151, @.p152, @.p153, @.p154, @.p155, @.p156, @.p157, @.p158, @.p159, @.p160, @.p161,
@.p162, @.p163, @.p164, @.p165, @.p166, @.p167, @.p168, @.p169, @.p170, @.p171, @.p172,
@.p173, @.p174, @.p175, @.p176, @.p177, @.p178, @.p179, @.p180, @.p181, @.p182, @.p183,
@.p184, @.p185, @.p186, @.p187, @.p188, @.p189, @.p190, @.p191, @.p192, @.p193, @.p194,
@.p195, @.p196, @.p197, @.p198, @.p199, @.p200, @.p201, @.p202, @.p203, @.p204, @.p205,
@.p206, @.p207, @.p208, @.p209, @.p210, @.p211, @.p212, @.p213, @.p214, @.p215, @.p216,
@.p217, @.p218, @.p219, @.p220, @.p221, @.p222, @.p223, @.p224, @.p225, @.p226, @.p227,
@.p228, @.p229, @.p230, @.p231, @.p232, @.p233, @.p234, @.p235, @.p236, @.p237, @.p238,
@.p239, @.p240, @.p241, @.p242, @.p243, @.p244, @.p245, @.p246, @.p247, @.p248, @.p249,
@.p250, @.p251, @.p252, @.p253, @.p254, @.p255, @.p256, @.p257, @.p258, @.p259, @.p260,
@.p261, @.p262, @.p263, @.p264, @.p265, @.p266, @.p267, @.p268, @.p269, @.p270, @.p271,
@.p272, @.p273, @.p274, @.p275, @.p276, @.p277, @.p278, @.p279, @.p280, @.p281, @.p282,
@.p283, @.p284, @.p285, @.p286, @.p287, @.p288, @.p289, @.p290, @.p291, @.p292, @.p293,
@.p294, @.p295, @.p296, @.p297, @.p298, @.p299, @.p300, @.p301, @.p302, @.p303, @.p304,
@.p305, @.p306, @.p307, @.p308, @.p309, @.p310, @.p311, @.p312, @.p313, @.p314, @.p315,
@.p316, @.p317, @.p318, @.p319, @.p320, @.p321, @.p322, @.p323, @.p324, @.p325, @.p326,
@.p327, @.p328, @.p329, @.p330, @.p331, @.p332, @.p333, @.p334, @.p335, @.p336, @.p337,
@.p338, @.p339, @.p340, @.p341, @.p342, @.p343, @.p344, @.p345, @.p346, @.p347, @.p348,
@.p349, @.p350, @.p351, @.p352, @.p353, @.p354, @.p355, @.p356, @.p357, @.p358, @.p359,
@.p360, @.p361, @.p362, @.p363, @.p364, @.p365, @.p366, @.p367, @.p368, @.p369, @.p370,
@.p371, @.p372, @.p373, @.p374, @.p375, @.p376, @.p377, @.p378, @.p379, @.p380, @.p381,
@.p382, @.p383, @.p384, @.p385, @.p386, @.p387, @.p388, @.p389, @.p390, @.p391, @.p392,
@.p393, @.p394, @.p395, @.p396, @.p397, @.p398, @.p399, @.p400, @.p401, @.p402, @.p403,
@.p404, @.p405, @.p406, @.p407, @.p408, @.p409, @.p410, @.p411, @.p412, @.p413, @.p414,
@.p415, @.p416, @.p417, @.p418, @.p419, @.p420, @.p421, @.p422, @.p423, @.p424, @.p425,
@.p426, @.p427, @.p428, @.p429, @.p430, @.p431, @.p432, @.p433, @.p434, @.p435, @.p436,
@.p437, @.p438, @.p439, @.p440, @.p441, @.p442, @.p443, @.p444, @.p445, @.p446, @.p447,
@.p448, @.p449, @.p450, @.p451, @.p452, @.p453, @.p454, @.p455, @.p456, @.p457, @.p458,
@.p459, @.p460, @.p461, @.p462, @.p463, @.p464, @.p465, @.p466, @.p467, @.p468, @.p469,
@.p470, @.p471, @.p472, @.p473, @.p474, @.p475, @.p476, @.p477, @.p478, @.p479, @.p480,
@.p481, @.p482, @.p483, @.p484, @.p485, @.p486, @.p487, @.p488, @.p489, @.p490, @.p491,
@.p492, @.p493, @.p494, @.p495, @.p496, @.p497, @.p498, @.p499, @.p500, @.p501, @.p502,
@.p503, @.p504, @.p505, @.p506, @.p507, @.p508, @.p509, @.p510, @.p511, @.p512, @.p513,
@.p514, @.p515, @.p516, @.p517, @.p518, @.p519, @.p520, @.p521, @.p522, @.p523, @.p524,
@.p525, @.p526, @.p527, @.p528, @.p529, @.p530, @.p531, @.p532, @.p533, @.p534, @.p535,
@.p536, @.p537, @.p538, @.p539, @.p540, @.p541, @.p542, @.p543, @.p544, @.p545, @.p546,
@.p547, @.p548, @.p549, @.p550, @.p551, @.p552, @.p553, @.p554, @.p555, @.p556, @.p557,
@.p558, @.p559, @.p560, @.p561, @.p562, @.p563, @.p564, @.p565, @.p566, @.p567, @.p568,
@.p569, @.p570, @.p571, @.p572, @.p573, @.p574, @.p575, @.p576, @.p577, @.p578, @.p579,
@.p580, @.p581, @.p582, @.p583, @.p584, @.p585, @.p586, @.p587, @.p588, @.p589, @.p590,
@.p591, @.p592, @.p593, @.p594, @.p595, @.p596, @.p597, @.p598, @.p599, @.p600, @.p601,
@.p602, @.p603, @.p604, @.p605, @.p606, @.p607, @.p608, @.p609, @.p610, @.p611, @.p612,
@.p613, @.p614, @.p615, @.p616, @.p617, @.p618, @.p619, @.p620, @.p621, @.p622, @.p623,
@.p624, @.p625, @.p626, @.p627, @.p628, @.p629, @.p630, @.p631, @.p632, @.p633, @.p634,
@.p635, @.p636, @.p637, @.p638, @.p639, @.p640, @.p641, @.p642, @.p643, @.p644, @.p645,
@.p646, @.p647, @.p648, @.p649, @.p650, @.p651, @.p652, @.p653, @.p654, @.p655, @.p656,
@.p657, @.p658, @.p659, @.p660, @.p661, @.p662, @.p663, @.p664, @.p665, @.p666, @.p667,
@.p668, @.p669, @.p670, @.p671, @.p672, @.p673, @.p674, @.p675, @.p676, @.p677, @.p678,
@.p679, @.p680, @.p681, @.p682, @.p683, @.p684, @.p685, @.p686, @.p687, @.p688, @.p689,
@.p690, @.p691, @.p692, @.p693, @.p694, @.p695, @.p696, @.p697, @.p698, @.p699, @.p700,
@.p701, @.p702, @.p703, @.p704, @.p705, @.p706, @.p707, @.p708, @.p709, @.p710, @.p711,
@.p712, @.p713, @.p714, @.p715, @.p716, @.p717, @.p718, @.p719, @.p720, @.p721, @.p722,
@.p723, @.p724, @.p725, @.p726, @.p727, @.p728, @.p729, @.p730, @.p731, @.p732, @.p733,
@.p734, @.p735, @.p736, @.p737, @.p738, @.p739, @.p740, @.p741, @.p742, @.p743, @.p744,
@.p745, @.p746, @.p747, @.p748, @.p749, @.p750, @.p751, @.p752, @.p753, @.p754, @.p755,
@.p756, @.p757, @.p758, @.p759, @.p760, @.p761, @.p762, @.p763, @.p764, @.p765, @.p766,
@.p767, @.p768, @.p769, @.p770, @.p771, @.p772, @.p773, @.p774, @.p775, @.p776, @.p777,
@.p778, @.p779, @.p780, @.p781, @.p782, @.p783, @.p784, @.p785, @.p786, @.p787, @.p788,
@.p789, @.p790, @.p791, @.p792, @.p793, @.p794, @.p795, @.p796, @.p797, @.p798, @.p799,
@.p800, @.p801, @.p802, @.p803, @.p804, @.p805, @.p806, @.p807, @.p808, @.p809, @.p810,
@.p811, @.p812, @.p813, @.p814, @.p815, @.p816, @.p817, @.p818, @.p819, @.p820, @.p821,
@.p822, @.p823, @.p824, @.p825, @.p826, @.p827, @.p828, @.p829, @.p830, @.p831, @.p832,
@.p833, @.p834, @.p835, @.p836, @.p837, @.p838, @.p839, @.p840, @.p841, @.p842, @.p843,
@.p844, @.p845, @.p846, @.p847, @.p848, @.p849, @.p850, @.p851, @.p852, @.p853, @.p854,
@.p855, @.p856, @.p857, @.p858, @.p859, @.p860, @.p861, @.p862, @.p863, @.p864, @.p865,
@.p866, @.p867, @.p868, @.p869, @.p870, @.p871, @.p872, @.p873, @.p874, @.p875, @.p876,
@.p877, @.p878, @.p879, @.p880, @.p881, @.p882, @.p883, @.p884, @.p885, @.p886, @.p887,
@.p888, @.p889, @.p890, @.p891, @.p892, @.p893, @.p894, @.p895, @.p896, @.p897, @.p898,
@.p899, @.p900, @.p901, @.p902, @.p903, @.p904, @.p905, @.p906, @.p907, @.p908, @.p909,
@.p910, @.p911, @.p912, @.p913, @.p914, @.p915, @.p916, @.p917, @.p918, @.p919, @.p920,
@.p921, @.p922, @.p923, @.p924, @.p925, @.p926, @.p927, @.p928, @.p929, @.p930, @.p931,
@.p932, @.p933, @.p934, @.p935, @.p936, @.p937, @.p938, @.p939, @.p940, @.p941, @.p942,
@.p943, @.p944, @.p945, @.p946, @.p947, @.p948, @.p949, @.p950, @.p951, @.p952, @.p953,
@.p954, @.p955, @.p956, @.p957, @.p958, @.p959, @.p960, @.p961, @.p962, @.p963, @.p964,
@.p965, @.p966, @.p967, @.p968, @.p969, @.p970, @.p971, @.p972, @.p973, @.p974, @.p975,
@.p976, @.p977, @.p978, @.p979, @.p980, @.p981, @.p982, @.p983, @.p984, @.p985, @.p986,
@.p987, @.p988, @.p989, @.p990, @.p991, @.p992, @.p993, @.p994, @.p995, @.p996, @.p997,
@.p998, @.p999, @.p1000 )

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1185890406.552369.7620@.57g2000hsv.googlegroup s.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>No; it would much more likely mean that he wants to pass a set of values
>>to his function. <<

>
Not very likely at all. Read the last 5+ years of postings here and
you will see that when they want to pass a list to an IN() predicate,
they explicitly ask about that. When they want to pass a table they
explicitly ask about that, as this guy did.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>> the ability to simply pass a set would make things enormously easier,
>>faster, and cleaner - which is probably why they're including it in SQL
>>2008. <<

>
Right now you can declare a huge number of parameters in a stored
procedure -- more than enough for any practical situation. But
programmers who grew up with BASIC and other interpreted languages
seem to panic at the the thought of a long parameter list.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I can think of several scenarios in which doing exactly what he is

asking would be necessary - reporting being the most obvious. <<
>
The most obvious is a system utility program which treats all tables
as tables rather than as part of a logical model. Now you are at the
meta data level, which has no place in an application or RDBMS
schema.
>
>