Im using crystal 11.5.
Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.
Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.
In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.
What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.
How do I get crystal to prompt me when I add the stored proc to the report?
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
passing parameter
I have a stored procedure named "processInventory" like the following.
Depending on the passed in parameters, I would like to add a WHERE
clause for "select" action. For example, if any varchar type of
parameter is passed in, the where clause would use "LIKE" operator. For
example, "Select * from Main where [s/n] like @.Serial. All other types
will use "=" operator. For example, "Select * from Main where MAKE =
@.Make and Type = @.type".
How could this be achieved? Thanks.
Depending on the passed in parameters, I would like to add a WHERE
clause for "select" action. For example, if any varchar type of
parameter is passed in, the where clause would use "LIKE" operator. For
example, "Select * from Main where [s/n] like @.Serial. All other types
will use "=" operator. For example, "Select * from Main where MAKE =
@.Make and Type = @.type".
How could this be achieved? Thanks.
CREATE PROCEDURE processInventory
@.Action varchar(7),
@.ControlNumber int = null,
@.AssetTag int = null,
@.Serial varchar(50) = null,
@.Description varchar(50) = null,
@.Make int = null,
@.Type int = null,
@.Model int = null,
@.Status int = null,
@.Networked bit = null,
@.LoginName varchar(50) = null,
@.Shared bit = null,
@.Org varchar(15) = null,
@.RecordDate datetime = null,
@.LastUpdate datetime = null,
@.ManufactureDate datetime = null,
@.Comment ntext = null
AS
declare @.processError int
set @.processError = 0
if @.Action = 'Select' goto selectInventory
else
If @.Action = 'Update'
begin
if @.ControlNumber = null return(1) --Required parameter value not
specified
else
goto updateInventory
end
else
if @.Action = 'Insert'
begin
if @.Serial = null return(1) --Required parameter value not
specified
else
goto InsertInventory
end
else
if @.Action = 'Delete'
begin
if @.ControlNumber = null return(1) --Required parameter value
not specified
else goto deleteInventory
end
selectInventory:
if @.Serial <> null
begin
select * from Main where [S/N] like @.Serial
if @.@.Error<>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
if @.ControlNumber <> null
begin
select * from Main where ControlNumber = @.ControlNumber
if @.@.Error <>0
begin
set @.processError = @.@.Error
return @.processError
end
end
else
select top 100* from Main
updateInventory:
update MAIN
set [Org Asset Tag] = @.AssetTag, [S/N] = @.Serial, [Description]
= @.Description, Make = @.Make, Type = @.Type,
Model = @.Model, Status = @.Status, Networked = @.Networked,
LoginName = @.LoginName, Shared = @.Shared,
Org = @.Org, [Date Of Record] = @.RecordDate, [Date Last
Updated] = @.LastUpdate, [Manuf Date] = @.ManufactureDate,
Comments = @.Comment
where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else
return(0) -- successful update
insertInventory:
insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,
Model, Status, Networked, LoginName, Shared,
Org, [Date Of Record], [Date Last Updated], [Manuf
Date],Comments)
values(@.AssetTag, @.Serial, @.Description, @.Make, @.Type, @.Model,
@.Status, @.Networked, @.LoginName, @.Shared,
@.Org, @.RecordDate, @.LastUpdate, @.ManufactureDate,
@.Comment)
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful insert
deleteInventory:
delete MAIN where ControlNumber = @.ControlNumber
if @.@.ERROR <> 0
begin
set @.processError = @.@.ERROR
return @.processError
end
else return(0) -- successful delete
GOFirst, I would suggest that you not lump all of your actions together
in one stored procedure; you will suffer from a performance impact,
because SQL Server will be forced to recompile your procedure every
time it runs (for SELECT, UPDATE, or DELETE). This is never a good
idea.
That being said, you could set the default value of the parameter you
wish to use wildcards on as a wildcard ('%'); later, in the body of the
stored procedure, add a wildcard character to the value before you use
it in the query. A simple example is below:
CREATE PROC procTestWildcard @.Param varchar(10) = '%' AS
SET @.Param = @.Param + '%'
SELECT Column
FROM Table
WHERE Column Like @.Param
--
Running the following
exec procWildCardTest
will return all of the data in your table since you've essentially run
the statement
SELECT Column
FROM Table
WHERE Column Like '%%'
The statement
exec procWildCardTest 'S'
will return all of the data in your table that starts with the letter
'S', since the SQL statement is now interpreted as
SELECT Column
FROM Table
WHERE Column Like 'S%'
HTH,
Stu|||Thanks for your suggestion. As you can see, I have more than one
parameter that might be passed into the proc. How do I dermine which
one is passed in? If I use IF..ELSE, there would be many combination
of parameters. I don't think SQL2000 allow concation of partitial
statments, so each combination need to be dealt with like
IF @.Make <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make
ELSE
IF @.Make <> NULL AND @.Model <> NULL
SELECT COL1, COL2 FROM TABLE WHERE MAKE LIKE @.Make and MODEL LIKE
@.Model
ELSE
other paramter combination|||The suggestion I gave above will work for any number of paramater
combinations. Not the most effecient way, but it will work.
CREATE PROC procTestParams (@.Make varchar(10) = '%', @.Model varchar(10)
= '%') AS
SET @.Make = @.Make+'%'
SET @.Model = @.Model+'%'
SELECT COL1, COL2
FROM TABLE
WHERE MAKE LIKE @.Make
and MODEL LIKE @.Model
Another way to do this is to build your SQL string dynamically and use
sp_executeSQL
CREATE PROC procTestParams (@.Make varchar(10) =NULL, @.Model
varchar(10) = NULL) AS
DECLARE @.SQL nvarchar(4000)
/*Return all records by default; need a basic true WHERE condition so
that you can
append AND's to it as needed*/
SET @.SQL = 'SELECT COL1, COL2 FROM TABLE WHERE 1=1 '
IF @.Make IS NOT NULL
SET @.SQL =@.SQL + ' AND Make LIKE @.Make ' --make sure that you are
passing wildcards if needed
IF @.Model IS NOT NULL
SET @.SQL =@.SQL + ' AND Model LIKE @.Model '
exec sp_executeSQL @.SQL, N'@.Make varchar(10), @.Model varchar(10)',
@.Make, @.Model
You'll just have to play around with it to see which is more effecient;
the first version will basically run a search against all parameters,
looking for wildcards (any data) on the columns you don't specify a
value for, whereas the second version will dynamically build a SQL
statement to be executed against only those columns you supply a
parameter for. The effeciency of either approach is going to be
affected by the number and atype of indexes on your table, and the
amount of data to be returned.
Hope that clarifies.
Stu|||[posted and mailed, please reply in news]
js (androidsun@.yahoo.com) writes:
> I have a stored procedure named "processInventory" like the following.
> Depending on the passed in parameters, I would like to add a WHERE
> clause for "select" action. For example, if any varchar type of
> parameter is passed in, the where clause would use "LIKE" operator. For
> example, "Select * from Main where [s/n] like @.Serial. All other types
> will use "=" operator. For example, "Select * from Main where MAKE =
> @.Make and Type = @.type".
> How could this be achieved? Thanks.
I have a longer article on the topic on
http://www.sommarskog.se/dyn-search.html.
Since you are into UPDATE, I would careful with using dynamic SQL
because of the permissions issues.
--
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 Paramenter to SP for Column Name
I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?
such as:
CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS
SET NOCOUNT ON;
SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)
The columns to select on are BIT columns.
The error message on the above SP is:
'Syntax error converting the nvarchar value 'P' to a column of data type
int.'
At this point, the passed in parameter is a string "ProducerStatus"
Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.
create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)
select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go
Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.
"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:
SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'
To understand why dynamic SQL isn't a good idea for this, see:
http://www.sommarskog.se/dynamic_sql.html
--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
To add to the other responses, permit me to point out two other flaws:
1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.
2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Passing output parameter from procedure to variable
I have a stored procedure that runs a query and the result of the query is a
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks
varchar. I would like to use an output parameter to get the value and then
pass that value into a variable to use elsewhere. Is this possible?
ThanksSure.
Example:
use northwind
go
create procedure usp_get_companyname
@.customerid nchar(5),
@.companyname nvarchar(40) output
as
set nocount on
set @.companyname = (select companyname from customers where customerid =
@.customerid)
return @.@.error
go
declare @.cn nvarchar(40)
execute usp_get_companyname @.customerid = 'alfki', @.companyname = @.cn output
print @.cn
go
drop procedure usp_get_companyname
go
AMB
"Andy" wrote:
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and the
n
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks|||Something like this?
use pubs
go
create proc first
@.au_lname varchar(50),
@.au_id varchar(11) OUTPUT
as
SELECT @.au_id = au_id from authors where au_lname = @.au_lname
RETURN (0)
GO
create proc second
@.au_id varchar(11)
as
select * from titleauthor where au_id = @.au_id
RETURN (0)
GO
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'white'
exec first @.lname, @.id output
select @.id
exec second @.id
go
declare @.lname varchar(50), @.id varchar(11)
set @.lname = 'green'
exec first @.lname, @.id output
select @.id
exec second @.id
go
drop proc first
drop proc second
Keith
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:0289E9D3-8AB3-481D-8869-92E6CFD12FF2@.microsoft.com...
> I have a stored procedure that runs a query and the result of the query is
a
> varchar. I would like to use an output parameter to get the value and
then
> pass that value into a variable to use elsewhere. Is this possible?
> Thanks
Passing Out Parameter to Sybase Stored Proc from RS
I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql
PASSING ORDER BY AS PARAMETERS
I have a stored procedure that excepts @.orderby as a parameter
and executes following sql statement:
select * from titles
order by
case when @.orderby = 'title' then
title
end
asc
Is there anyway I can add second parameter @.direction and control order
ASC/DESC based on parameter value , like "case when @.direction then asc"?
Thanks in advance
Programmerhttp://www.aspfaq.com/2501
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||You can use:
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
However, the plan for such a query would result in a table scan plus a sort
operation.
If you want to allow an efficient plan based on the input, you should either
use dynamic execution, which has it's obvious limitations (SQL injection
attacks and other security issues), or the following static approach:
if @.col = 'title' and @.dir = 'asc'
select * from titles order by title
else if @.col = 'title' and @.dir = 'desc'
select * from titles order by title desc
else if @.col = 'price' and @.dir = 'asc'
select * from titles order by price
else if @.col = 'price' and @.dir = 'desc'
select * from titles order by price desc
...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||Hi
You can try as
exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sergey Zuyev" wrote:
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||It's not quite that simple.
Create Procedure MyProcedure
@.OrderBy nvarchar(12)
AS
Select * /*We really should list the field names, but this is an
example*/ ,
OrderMeBy = Case @.OrderBy
When 'title' Then title
When 'Price_Asc' Then cast(Price as nvarchar(20))
When 'Price_Desc' Then cast(Price *-1 as nvarchar(20)) /*negated
to go in descending order*/
When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
'12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
recent dates a lower value, thus descending*/
END
From Titles
Order By OrderMeBy
What I wrote actually has a few sorting problems due to the CAST's, but it
demonstrates the method. I don't have a nice little formula for turning
text around to make it sort backwards like I do for numeric fields and
dates.
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||And what if the user specifies "delete from titles" as the value of @.order ?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
> Hi
> You can try as
> exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sergey Zuyev" wrote:
>|||Daniel, using multiple CASE expressions instead of one, where each deals
with one column only, solves all the issues related to differences in
datatypes.
Check out the solution I proposed. You will also find a treatment for the
direction issue.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> It's not quite that simple.
> Create Procedure MyProcedure
> @.OrderBy nvarchar(12)
> AS
> Select * /*We really should list the field names, but this is an
> example*/ ,
> OrderMeBy = Case @.OrderBy
> When 'title' Then title
> When 'Price_Asc' Then cast(Price as nvarchar(20))
> When 'Price_Desc' Then cast(Price *-1 as nvarchar(20))
> /*negated
> to go in descending order*/
> When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
> When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
> '12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
> recent dates a lower value, thus descending*/
> END
> From Titles
> Order By OrderMeBy
>
> What I wrote actually has a few sorting problems due to the CAST's, but it
> demonstrates the method. I don't have a nice little formula for turning
> text around to make it sort backwards like I do for numeric fields and
> dates.
> hth,
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>|||well if u specify the lenght to be 4, then the string would not be accepted
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"JT" wrote:
> And what if the user specifies "delete from titles" as the value of @.order
?
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
>
>|||Thanks, Itzik.
I saw your solution after I'd posted -- I got the basics of my solution here
on this group about 4 years ago. Perhaps SQL Server 7 did not support that
syntax?
Regardless, your solution is much cleaner & I will be putting it to work in
the future.
dwlison
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%235n$t8OnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Daniel, using multiple CASE expressions instead of one, where each deals
> with one column only, solves all the issues related to differences in
> datatypes.
> Check out the solution I proposed. You will also find a treatment for the
> direction issue.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
more
it
asc"?
>|||> I don't have a nice little formula for turning
> text around to make it sort backwards
Well, the other solutions are better, and this is far from efficient, but it
can be done. This assumes a case insensitive collation.
CREATE FUNCTION dbo.SortReverse
( @.in VARCHAR(32) )
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.i TINYINT, @.out VARCHAR(32)
SELECT @.i = 1, @.out = ''
WHILE @.i <= len(@.in)
BEGIN
SELECT @.out = @.out + CHAR(90+65-ASCII(UPPER(SUBSTRING(@.in, @.i, 1))))
SET @.i = @.i + 1
END
RETURN @.out
END
GO
Now you can do this:
DECLARE @.sortOrder VARCHAR(4)
SET @.sortOrder = 'ASC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
SET @.sortOrder = 'DESC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
and executes following sql statement:
select * from titles
order by
case when @.orderby = 'title' then
title
end
asc
Is there anyway I can add second parameter @.direction and control order
ASC/DESC based on parameter value , like "case when @.direction then asc"?
Thanks in advance
Programmerhttp://www.aspfaq.com/2501
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||You can use:
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
However, the plan for such a query would result in a table scan plus a sort
operation.
If you want to allow an efficient plan based on the input, you should either
use dynamic execution, which has it's obvious limitations (SQL injection
attacks and other security issues), or the following static approach:
if @.col = 'title' and @.dir = 'asc'
select * from titles order by title
else if @.col = 'title' and @.dir = 'desc'
select * from titles order by title desc
else if @.col = 'price' and @.dir = 'asc'
select * from titles order by price
else if @.col = 'price' and @.dir = 'desc'
select * from titles order by price desc
...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||Hi
You can try as
exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sergey Zuyev" wrote:
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||It's not quite that simple.
Create Procedure MyProcedure
@.OrderBy nvarchar(12)
AS
Select * /*We really should list the field names, but this is an
example*/ ,
OrderMeBy = Case @.OrderBy
When 'title' Then title
When 'Price_Asc' Then cast(Price as nvarchar(20))
When 'Price_Desc' Then cast(Price *-1 as nvarchar(20)) /*negated
to go in descending order*/
When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
'12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
recent dates a lower value, thus descending*/
END
From Titles
Order By OrderMeBy
What I wrote actually has a few sorting problems due to the CAST's, but it
demonstrates the method. I don't have a nice little formula for turning
text around to make it sort backwards like I do for numeric fields and
dates.
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||And what if the user specifies "delete from titles" as the value of @.order ?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
> Hi
> You can try as
> exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sergey Zuyev" wrote:
>|||Daniel, using multiple CASE expressions instead of one, where each deals
with one column only, solves all the issues related to differences in
datatypes.
Check out the solution I proposed. You will also find a treatment for the
direction issue.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> It's not quite that simple.
> Create Procedure MyProcedure
> @.OrderBy nvarchar(12)
> AS
> Select * /*We really should list the field names, but this is an
> example*/ ,
> OrderMeBy = Case @.OrderBy
> When 'title' Then title
> When 'Price_Asc' Then cast(Price as nvarchar(20))
> When 'Price_Desc' Then cast(Price *-1 as nvarchar(20))
> /*negated
> to go in descending order*/
> When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
> When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
> '12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
> recent dates a lower value, thus descending*/
> END
> From Titles
> Order By OrderMeBy
>
> What I wrote actually has a few sorting problems due to the CAST's, but it
> demonstrates the method. I don't have a nice little formula for turning
> text around to make it sort backwards like I do for numeric fields and
> dates.
> hth,
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>|||well if u specify the lenght to be 4, then the string would not be accepted
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"JT" wrote:
> And what if the user specifies "delete from titles" as the value of @.order
?
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
>
>|||Thanks, Itzik.
I saw your solution after I'd posted -- I got the basics of my solution here
on this group about 4 years ago. Perhaps SQL Server 7 did not support that
syntax?
Regardless, your solution is much cleaner & I will be putting it to work in
the future.
dwlison
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%235n$t8OnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Daniel, using multiple CASE expressions instead of one, where each deals
> with one column only, solves all the issues related to differences in
> datatypes.
> Check out the solution I proposed. You will also find a treatment for the
> direction issue.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
more
it
asc"?
>|||> I don't have a nice little formula for turning
> text around to make it sort backwards
Well, the other solutions are better, and this is far from efficient, but it
can be done. This assumes a case insensitive collation.
CREATE FUNCTION dbo.SortReverse
( @.in VARCHAR(32) )
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.i TINYINT, @.out VARCHAR(32)
SELECT @.i = 1, @.out = ''
WHILE @.i <= len(@.in)
BEGIN
SELECT @.out = @.out + CHAR(90+65-ASCII(UPPER(SUBSTRING(@.in, @.i, 1))))
SET @.i = @.i + 1
END
RETURN @.out
END
GO
Now you can do this:
DECLARE @.sortOrder VARCHAR(4)
SET @.sortOrder = 'ASC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
SET @.sortOrder = 'DESC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
Passing null value parameter to a data-driven subscription
I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.
When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.
I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.
Has anyone else experienced this issue and do you have a resolution?
Thanks in advance for your assistance.
I am having the same issue, please let me know if you have found a resolution.
Thank you,
Dave
|||
I had the same problem. In another thread someone mentioned that there is a problem with null parameters and data-driven subscriptions. It seems like a major bug in reporting services. My workaround was to use something other than null and have the report query recognize this setting as equivalent to null e.g. if it's a varchar field then set to 'NOTHING' and have the report definition query treat 'NOTHING' as NULL. For a uniqueidentifer field I used a guid with all zeroes.
Hope that helps.
Regards,
Greg
Passing null value parameter to a data-driven subscription
I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.
When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.
I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.
Has anyone else experienced this issue and do you have a resolution?
Thanks in advance for your assistance.
I am having the same issue, please let me know if you have found a resolution.
Thank you,
Dave
Passing NULL value not working
I currently have a stored procedure that looks something like this
SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@.Name+'%', Title)
I have a form that supplies this value. This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not. On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,
SqlDataSource1.SelectParameters.Add("@.Name", TextBox1.Text);
It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why. Thank you in advance
/jcarver
Try this:
WHERE ([Title] Like '%' + @.Name + '%') or ( @.Name is NULL)"
And add this to your SqlDatasource: CancelSelectOnNullParameter="False"
|||The two where clauses are logically equivalent.
I'm a bit curious why you have a column called title and a parameter called @.name instead of @.title.
As for passing in a null, you could check the length of the textbox text, and if it's 0, send DBValue instead of the Text property.
|||
Try
IF @.Name IS NULL SET @.Name = ''
IF DATALENGTH(@.Name) > 0
SELECT * FROM tblQuestions WHERE Title LIKE '%'+@.Name+'%'
ELSE
SELECT * FROM tblQuestions
SELECT * FROM tblQuestions WHERE Title LIKE '%' + @.Name + '%'
ConvertEmptyStringToNull must be set to False!
sqlPassing Null to Stored Procedure in Reporting Services
Hello,
I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.
Thanks.
Since you say that it is Blank, the report is passing a blank '', not a null. In your proc, you could have the statement:
if @.Parm = ''
Begin
Select @.Parm = null
End
In your parameter setup, uncheck the Allow Blank, and select the Allow Nulls...
Then, when running the report, when the NULL check box in the parameter panel is checked, a NULL will be passed to the proc.
hth
BobP
|||Hello,
I have Allow Nulls checked. I do have that safeguard in my procedure. I don't have the option for the null checkbox in the parameter panel though. I am sure I have Allow Nulls checked in my application. How does that get added to the panel?
Thanks.
|||You might want to consider modifying the stored procedure to default the parameter in question. You can set up the last (or last few) arguments of a stored procedure to be optional by providing a default value for the argument. For example, if you have a stored procedure defined as:
Code Snippet
create dbo.myProc
( @.anArgument varchar(20) = null
)
as
...
Since you have provided a default value to your parameter (as designated in red), you are no longer required to pass this argument to your stored procedure. Sometimes this can help.
Kent
|||The null check box only gets added when you check the Allow Nulls box.
Also make sure you uncheck the Allow Blanks box.
BobP
|||Hello,
I have a default value set to null for my stored procedure. I have allow nulls checked. I have allow blanks unchecked. I have verified my stored procedure and it works with nulls and blanks (SP converts blanks to null), and I have run the stored procedure in the RS designer and it works. I am sure of that... But, no matter what, I do not get a nulls checkbox in the parameter area...
Any ideas?
|||BobP,
I do have Allow Nulls checked, and still it does not show.
Any ideas?
|||What version of SSRS are you using? 2000 or 2005, and which SP?|||For some reason, SSRS doesn't seem to support "allow NULL" option for drop-down query-based parameters. You are always required to pick one of the parameter values to proceed with the report.
I created a small test report based on an SP that accepts one optional parameter of type INT. I was able to configure optional drop-down parameter for this report by creating a following dataset for parameter query:
Code Snippet
SELECT NULL party_id, '<all customers>' party_name
UNION ALL
SELECT party_id, party_name FROM party ORDER BY party_name
This will add "<all customers>" option to the list of parameters values and return NULL to the SP if this option is selected.
Hope this helps.
Denis
|||Reporting Services 2000.|||Hey,
I was hoping not to have to do that, but your right I believe that is the only option.
Thanks.
Passing NULL to DataSet parmameter
Hey All,
I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.
I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.
When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:
@.ID = <Null> - ERROR - [BC30201] Expression expected
@.ID= - ERROR - Return statement in function must return a value
@.ID = Null --ERROR - Null constant not supported use System.DBNull instead
@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression
@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.
Does anyone know another magic value I can pass to get this to work?
I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.
Thanks in advance.
Eric Wild
PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!
ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )
Hello Eric,
Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?
Jarret
|||Jarret,
That worked!
I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!
Here is steps on how to fix this.
1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.
2) Go to the Layout tab, and from the menu select Report/Report Parameters...
3) a new Parameter is in the list to the left callled ID.
-Check Allow Nulls
- Check Hidden
-Verify Default Value NULL is bubbled in below.
Thanks again
Eic Wild
Wednesday, March 28, 2012
Passing multivalue in stored procedures
Hi guys,
I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.
Thanks in advance.
Hi,
What is the stored procedure expecting? More than one parameter? Or a list of values concatenated with strings?
-Jessica
|||I want to pass multiple value in the event type. If i pass a single value, it would work but for multiple value it fails.
Here's my storedd procedure:
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE [dbo].[usp_retrieve_audit_data]
-- Add the parameters for the stored procedure here
@.start_date asDateTime=NULL,
@.end_date asDateTime=NULL,
@.event asvarchar(20)=NULL
AS
BEGIN
SETNOCOUNTON;
-- Insert statements for procedure here
SELECT audit_log_id, date_created, event_type, event_name, event_status,
row_affected, updated_by
FROM audit_log
WHERE(date_created >= @.start_date)AND(date_created <= @.end_date)AND
(event_type IN(@.event))
END
|||someone gave me this answer last time... but i've modified the column size
create a table valued function..
CREATEFUNCTION [dbo].[multival_prm]
(@.list ntext,
@.delimiter nchar(1)= N',')
RETURNS @.tbl TABLE(listpos intIDENTITY(1, 1)NOTNULL,
strvarchar(64))AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(64),
@.leftover nvarchar(64),
@.tmpval nvarchar(64)
SET @.textpos = 1
SET @.leftover =''
WHILE @.textpos <=datalength(@.list)/ 2
BEGIN
SET @.chunklen = 64 -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)VALUES(@.tmpval)
SET @.tmpstr =substring(@.tmpstr, @.pos + 1,len(@.tmpstr))
SET @.pos =charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str)VALUES(ltrim(rtrim(@.leftover)))
RETURN
END
from your stored proc,
SELECT audit_log_id, date_created, event_type, event_name, event_status,
row_affected, updated_by
FROM audit_log
WHERE(date_created >= @.start_date)AND(date_created <= @.end_date)AND
(event_type inner join multiprm_val(@.event , default))
HTH|||Thanks thanks. I'll try this approach. Then I'll post my solution if its successful.|||opss... i forgotten something... the inner join part..... gotta join both the fields together ....|||
I get this done by useing dynamic SQL. You will need to add an a parm directly to your report.
Here's what your procedure code would look like:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE [dbo].[usp_retrieve_audit_data]
@.start_date asDateTime=NULL,
@.end_date asDateTime=NULL,
@.event as varchar(20)=NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.sql varchar(8000), @.crlf char(2)
SELECT @.crlf =char(13)+char(10)
SELECT @.sql =
N'SELECT audit_log_id, date_created, event_type, event_name, event_status, '+ @.crlf
+'row_affected, updated_by'+ @.crlf
+'FROM audit_log'+ @.crlf
+'WHERE (date_created between '''+CONVERT(VARCHAR(30), @.start_date, 121)+''' AND '''+CONVERT(VARCHAR(30), @.end_date, 121)+''') AND'+ @.crlf
+'(event_type IN ('+ @.event +'))'+ @.crlf
EXEC(@.sql)
--PRINT @.sql
END
In you report add a parameter and call it @.Event_List of type string. Make it a multi select and add all of you default values. Wrap the values in single ticks ex. '1'
Hide the parm @.event and under "Default values" set it's "Non-queried" value to the following expression:
=JOIN(Parameters!Event_List, ",")
What is passed back in @.event will be a comma delimited list of values selected in @.Event_List and will look like '1','2','3'
This value is substituted in the dynamic sql above and your dataset will be for only the events selected by your user.
Passing multi-select params to a stored procedure
i've already read the MS help file saying this can't be done, but there are
lots of people out there smarter than MS. Has anyone come up with a way to
take a multiple select paramter and pass it into a stored procedure yet? I
noticed there is a built in function string.join which concatonates the
parameters you've chosen, so I'm curious why you could'nt just pass that
string into an "IN" clause. Anyway, let me know if you've got a work around
for this, I'd appreciate it.
MichaelHere is the solution that I came up with. What I did was created a function
that would parse a string based on a delimiter and return the in clause. For
example lets say you were going to pass the string "NY""CA"NC" where the "
symbol is my delimiter into a stored procedure.
Create proc ReturnData
(
@.multivalue varchar(100)
)
as
Decalre @.in varchar(100), @.sqlcmd varchar(500)
set @.in = dbo.RSMultiValue(@.multivalue,'"')
set @.sqlcmd = 'select * from table where state ' + @.in
exec(@.sqlcmd)
The trick is to configure your parameter list correctlly in reporting
services. For example lets say you had a static list of the parameters, what
you want to do is set the value of each parameter value 1 = "NY" parameter
value 2 = "CA" etc...
The string that will be passed = "NY""CA" basically you are just creating
your own list and parsing the data with a function.
I hope this helps
"Michael C" wrote:
> i've already read the MS help file saying this can't be done, but there are
> lots of people out there smarter than MS. Has anyone come up with a way to
> take a multiple select paramter and pass it into a stored procedure yet? I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work around
> for this, I'd appreciate it.
> Michael|||You are missing the point about passing multi-select parameters to a stored
procedure. You can easily do this. What you can't do is in the stored
procedure do this:
select * from some table where somefield in (@.MyParam).
The reason has nothing to do with RS. It has to do with stored procedures.
Stored procedures do not allow this because it is dangerous. It leads you
open to security attacks. Again, absolutely nothing to do with RS.
Now, as the other poster suggested you can create your sql on the fly. The
other possibilty is to use a UDF that takes a comma separated list and turns
it into a table variable which you can join with. If you are interested in
the function I can post it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:97EA5DB7-F6C9-4402-88F3-84315DE094E8@.microsoft.com...
> i've already read the MS help file saying this can't be done, but there
> are
> lots of people out there smarter than MS. Has anyone come up with a way
> to
> take a multiple select paramter and pass it into a stored procedure yet?
> I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work
> around
> for this, I'd appreciate it.
> Michael|||Using a user defined function isn't the only way to achive this with SQL
Server.
The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
performance.
Andrew|||Excellent article. It turns out that I got my code from Erland.
Here is the function I use that I got from him: What you can do is to have a
string parameter that is pass ed as a multivalue parameter and then change
the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION 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
GO
"Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> Using a user defined function isn't the only way to achive this with SQL
> Server.
> The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> performance.
> Andrew
>|||Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
going to take me a bit to get my head around this, but let me see if I
understand this:
Basically your passing the entire list of values as a string
(string.join,",") into the function, then creating a temp table in the
function which adds a row for each parameter, and joining my sql statement
inside my stored procedure to this newly created temp table to select the
required rows?
Again, i'm new to this so thanks for your patience and help!
Michael
"Bruce L-C [MVP]" wrote:
> Excellent article. It turns out that I got my code from Erland.
> Here is the function I use that I got from him: What you can do is to have a
> string parameter that is pass ed as a multivalue parameter and then change
> the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION 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
> GO
>
> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > Using a user defined function isn't the only way to achive this with SQL
> > Server.
> >
> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > performance.
> >
> > Andrew
> >
> >
>
>|||In my example below @.STO was a multi-select parameter. RS automatically is
sending the selection as a comma separated string. You don't need to do
anything to it. Then I pass that to the function I show below. That function
returns a table variable that you can then join to.
Note that you can test this from query analyzer by passing a comma separated
string to your stored procedure.
exec mystoredprocedure 'blah,bleh,etc'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
>> Excellent article. It turns out that I got my code from Erland.
>> Here is the function I use that I got from him: What you can do is to
>> have a
>> string parameter that is pass ed as a multivalue parameter and then
>> change
>> the string into a table.
>> This technique was told to me by SQL Server MVP, Erland Sommarskog
>> For example I have done this
>> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> So note this is NOT an issue with RS, it is strictly a stored procedure
>> issue.
>> Here is the function:
>> CREATE FUNCTION 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
>> GO
>>
>> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > Using a user defined function isn't the only way to achive this with
>> > SQL
>> > Server.
>> >
>> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
>> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
>> > for
>> > performance.
>> >
>> > Andrew
>> >
>> >
>>|||So question... Here is my code. Where would I insert this? the @.Mill, @.Port
can be mulitples (the @.Trip is always a single parameter)
ALTER PROCEDURE [dbo].[LumberLineUp]
@.Mill varchar(100) = '0',
@.Trip varchar(100) = '0',
@.Port varchar(100) = '0'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- INITIAL TABLES
CREATE TABLE [dbo].[#LumberLineUp](
[Vessel] [nvarchar](50) NULL,
[LoadPort][nvarchar](50) NULL,
[Trip][nvarchar](50) NULL,
[OrderID][nvarchar](20) NOT NULL,
[Mill][nvarchar](50) NULL,
[Mark][nvarchar](20) NULL,
[Product][nvarchar](160) NULL,
[Parent][nvarchar](160) NULL,
[FBM][real]NULL,
[Pkgs][real]NULL,
[Stowage][nvarchar](5) NULL,
[Grade][nvarchar](10) NULL,
[Length][nvarchar](10)NULL,
[LengthDesc][nvarchar](10) NULL,
[Port][nvarchar](50)NULL,
[LoadDate][nvarchar](50)NULL,
[ETADate][nvarchar](50)NULL
)
CREATE TABLE [dbo].[#LengthTally](
[OrderID] [varchar](20) NULL,
[Mark] [varchar](20) NULL,
[Tally] [varchar](120) NULL,
[Prod] [varchar](160)NULL,
[OrderPrev] [varchar](20) NULL,
[MarkPrev] [varchar](20) NULL,
[ProductPrev] [varchar](160)NULL,
[Length] [nvarchar](10) NULL,
[LengthPrev] [nvarchar](10) NULL,
[Count] [varchar](10) NULL,
[Test][varchar](10) NULL
)
-- Data for LumberLineUp Table
INSERT INTO dbo.#LumberLineUp
SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
location.location_id AS Mill,
order_detail.Mark, product_master.descrip AS Product,
product_parent.descrip AS parent,
CASE order_detail.qty_unit WHEN 'm3' THEN
order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN order_detail.qty
END AS FBM,
order_detail.alt_qty AS Pkgs, order_detail.Stowage,
product_master.grade, length_master.sort_fact AS Length,
length_master.descrip AS Legnth,
port.port_id AS Port, VesselTrips.LoadingDate,
tripdestinations.ETADate
FROM Vessel AS Vessel INNER JOIN
VesselVoyages AS VesselVoyages ON
VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
VesselTrips AS VesselTrips ON
VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
order_header AS order_header ON
order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
location AS location ON location.location_id =order_header.location INNER JOIN
order_detail AS order_detail ON order_detail.order_id
= order_header.order_id INNER JOIN
product_master AS product_master ON
product_master.product_id = order_detail.product_id INNER JOIN
product_master AS product_parent ON
product_master.product_part = product_parent.product_id INNER JOIN
length_master AS length_master ON
product_master.length_part = length_master.len_id INNER JOIN
TripDestinations AS tripdestinations ON
tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
tripdestinations.Port_ID =order_header.Destination_Port INNER JOIN
port AS port ON tripdestinations.Port_ID =port.port_id INNER JOIN
LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
AND (VesselTrips.ID IN (@.Trip)) OR
(location.location_id IN (@.Mill)) AND (VesselTrips.ID
IN (@.Trip)) AND (@.Port = '0')
ORDER BY OrderID, order_detail.Mark, Product, Length
"Michael C" wrote:
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
> > Excellent article. It turns out that I got my code from Erland.
> >
> > Here is the function I use that I got from him: What you can do is to have a
> > string parameter that is pass ed as a multivalue parameter and then change
> > the string into a table.
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> >
> > For example I have done this
> >
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> >
> > CREATE FUNCTION 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
> >
> > GO
> >
> >
> >
> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > > Using a user defined function isn't the only way to achive this with SQL
> > > Server.
> > >
> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > > performance.
> > >
> > > Andrew
> > >
> > >
> >
> >
> >|||Add another inner join with @.port
inner join charlist_to_table(@.port,Default)f on port.port_id = f.str
remove (port.port_id IN (@.Port))
Follow the same pattern as above with your other multi-value parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:48471539-1EA0-489C-B3F6-85240B1FE04E@.microsoft.com...
> So question... Here is my code. Where would I insert this? the @.Mill,
> @.Port
> can be mulitples (the @.Trip is always a single parameter)
> ALTER PROCEDURE [dbo].[LumberLineUp]
> @.Mill varchar(100) = '0',
> @.Trip varchar(100) = '0',
> @.Port varchar(100) = '0'
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- INITIAL TABLES
> CREATE TABLE [dbo].[#LumberLineUp](
> [Vessel] [nvarchar](50) NULL,
> [LoadPort][nvarchar](50) NULL,
> [Trip][nvarchar](50) NULL,
> [OrderID][nvarchar](20) NOT NULL,
> [Mill][nvarchar](50) NULL,
> [Mark][nvarchar](20) NULL,
> [Product][nvarchar](160) NULL,
> [Parent][nvarchar](160) NULL,
> [FBM][real]NULL,
> [Pkgs][real]NULL,
> [Stowage][nvarchar](5) NULL,
> [Grade][nvarchar](10) NULL,
> [Length][nvarchar](10)NULL,
> [LengthDesc][nvarchar](10) NULL,
> [Port][nvarchar](50)NULL,
> [LoadDate][nvarchar](50)NULL,
> [ETADate][nvarchar](50)NULL
> )
> CREATE TABLE [dbo].[#LengthTally](
> [OrderID] [varchar](20) NULL,
> [Mark] [varchar](20) NULL,
> [Tally] [varchar](120) NULL,
> [Prod] [varchar](160)NULL,
> [OrderPrev] [varchar](20) NULL,
> [MarkPrev] [varchar](20) NULL,
> [ProductPrev] [varchar](160)NULL,
> [Length] [nvarchar](10) NULL,
> [LengthPrev] [nvarchar](10) NULL,
> [Count] [varchar](10) NULL,
> [Test][varchar](10) NULL
> )
> -- Data for LumberLineUp Table
> INSERT INTO dbo.#LumberLineUp
> SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
> VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
> location.location_id AS Mill,
> order_detail.Mark, product_master.descrip AS Product,
> product_parent.descrip AS parent,
> CASE order_detail.qty_unit WHEN 'm3' THEN
> order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN
> order_detail.qty
> END AS FBM,
> order_detail.alt_qty AS Pkgs, order_detail.Stowage,
> product_master.grade, length_master.sort_fact AS Length,
> length_master.descrip AS Legnth,
> port.port_id AS Port, VesselTrips.LoadingDate,
> tripdestinations.ETADate
> FROM Vessel AS Vessel INNER JOIN
> VesselVoyages AS VesselVoyages ON
> VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
> VesselTrips AS VesselTrips ON
> VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
> order_header AS order_header ON
> order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
> location AS location ON location.location_id => order_header.location INNER JOIN
> order_detail AS order_detail ON order_detail.order_id
> = order_header.order_id INNER JOIN
> product_master AS product_master ON
> product_master.product_id = order_detail.product_id INNER JOIN
> product_master AS product_parent ON
> product_master.product_part = product_parent.product_id INNER JOIN
> length_master AS length_master ON
> product_master.length_part = length_master.len_id INNER JOIN
> TripDestinations AS tripdestinations ON
> tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
> tripdestinations.Port_ID => order_header.Destination_Port INNER JOIN
> port AS port ON tripdestinations.Port_ID => port.port_id INNER JOIN
> LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
> WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
> AND (VesselTrips.ID IN (@.Trip)) OR
> (location.location_id IN (@.Mill)) AND (VesselTrips.ID
> IN (@.Trip)) AND (@.Port = '0')
> ORDER BY OrderID, order_detail.Mark, Product, Length
>
> "Michael C" wrote:
>> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
>> going to take me a bit to get my head around this, but let me see if I
>> understand this:
>> Basically your passing the entire list of values as a string
>> (string.join,",") into the function, then creating a temp table in the
>> function which adds a row for each parameter, and joining my sql
>> statement
>> inside my stored procedure to this newly created temp table to select the
>> required rows?
>> Again, i'm new to this so thanks for your patience and help!
>> Michael
>>
>> "Bruce L-C [MVP]" wrote:
>> > Excellent article. It turns out that I got my code from Erland.
>> >
>> > Here is the function I use that I got from him: What you can do is to
>> > have a
>> > string parameter that is pass ed as a multivalue parameter and then
>> > change
>> > the string into a table.
>> > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> >
>> > For example I have done this
>> >
>> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> >
>> > So note this is NOT an issue with RS, it is strictly a stored procedure
>> > issue.
>> >
>> > Here is the function:
>> >
>> > CREATE FUNCTION 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
>> >
>> > GO
>> >
>> >
>> >
>> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > > Using a user defined function isn't the only way to achive this with
>> > > SQL
>> > > Server.
>> > >
>> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by
>> > > Erland
>> > > Sommarskog (SQL Server MVP) has a few alternatives trading
>> > > simplicity for
>> > > performance.
>> > >
>> > > Andrew
>> > >
>> > >
>> >
>> >
>> >|||Hello,
This works perfectly for me. I was wondering if there is a way to
incorporate the parameter "All" to select the entire list?
Thanks,
Deb
"Bruce L-C [MVP]" wrote:
> In my example below @.STO was a multi-select parameter. RS automatically is
> sending the selection as a comma separated string. You don't need to do
> anything to it. Then I pass that to the function I show below. That function
> returns a table variable that you can then join to.
> Note that you can test this from query analyzer by passing a comma separated
> string to your stored procedure.
> exec mystoredprocedure 'blah,bleh,etc'
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> > Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> > going to take me a bit to get my head around this, but let me see if I
> > understand this:
> >
> > Basically your passing the entire list of values as a string
> > (string.join,",") into the function, then creating a temp table in the
> > function which adds a row for each parameter, and joining my sql statement
> > inside my stored procedure to this newly created temp table to select the
> > required rows?
> >
> > Again, i'm new to this so thanks for your patience and help!
> >
> > Michael
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Excellent article. It turns out that I got my code from Erland.
> >>
> >> Here is the function I use that I got from him: What you can do is to
> >> have a
> >> string parameter that is pass ed as a multivalue parameter and then
> >> change
> >> the string into a table.
> >> This technique was told to me by SQL Server MVP, Erland Sommarskog
> >>
> >> For example I have done this
> >>
> >> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >>
> >> So note this is NOT an issue with RS, it is strictly a stored procedure
> >> issue.
> >>
> >> Here is the function:
> >>
> >> CREATE FUNCTION 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
> >>
> >> GO
> >>
> >>
> >>
> >> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> >> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> >> > Using a user defined function isn't the only way to achive this with
> >> > SQL
> >> > Server.
> >> >
> >> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> >> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
> >> > for
> >> > performance.
> >> >
> >> > Andrew
> >> >
> >> >
> >>
> >>
> >>
>
>sql
lots of people out there smarter than MS. Has anyone come up with a way to
take a multiple select paramter and pass it into a stored procedure yet? I
noticed there is a built in function string.join which concatonates the
parameters you've chosen, so I'm curious why you could'nt just pass that
string into an "IN" clause. Anyway, let me know if you've got a work around
for this, I'd appreciate it.
MichaelHere is the solution that I came up with. What I did was created a function
that would parse a string based on a delimiter and return the in clause. For
example lets say you were going to pass the string "NY""CA"NC" where the "
symbol is my delimiter into a stored procedure.
Create proc ReturnData
(
@.multivalue varchar(100)
)
as
Decalre @.in varchar(100), @.sqlcmd varchar(500)
set @.in = dbo.RSMultiValue(@.multivalue,'"')
set @.sqlcmd = 'select * from table where state ' + @.in
exec(@.sqlcmd)
The trick is to configure your parameter list correctlly in reporting
services. For example lets say you had a static list of the parameters, what
you want to do is set the value of each parameter value 1 = "NY" parameter
value 2 = "CA" etc...
The string that will be passed = "NY""CA" basically you are just creating
your own list and parsing the data with a function.
I hope this helps
"Michael C" wrote:
> i've already read the MS help file saying this can't be done, but there are
> lots of people out there smarter than MS. Has anyone come up with a way to
> take a multiple select paramter and pass it into a stored procedure yet? I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work around
> for this, I'd appreciate it.
> Michael|||You are missing the point about passing multi-select parameters to a stored
procedure. You can easily do this. What you can't do is in the stored
procedure do this:
select * from some table where somefield in (@.MyParam).
The reason has nothing to do with RS. It has to do with stored procedures.
Stored procedures do not allow this because it is dangerous. It leads you
open to security attacks. Again, absolutely nothing to do with RS.
Now, as the other poster suggested you can create your sql on the fly. The
other possibilty is to use a UDF that takes a comma separated list and turns
it into a table variable which you can join with. If you are interested in
the function I can post it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:97EA5DB7-F6C9-4402-88F3-84315DE094E8@.microsoft.com...
> i've already read the MS help file saying this can't be done, but there
> are
> lots of people out there smarter than MS. Has anyone come up with a way
> to
> take a multiple select paramter and pass it into a stored procedure yet?
> I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work
> around
> for this, I'd appreciate it.
> Michael|||Using a user defined function isn't the only way to achive this with SQL
Server.
The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
performance.
Andrew|||Excellent article. It turns out that I got my code from Erland.
Here is the function I use that I got from him: What you can do is to have a
string parameter that is pass ed as a multivalue parameter and then change
the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION 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
GO
"Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> Using a user defined function isn't the only way to achive this with SQL
> Server.
> The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> performance.
> Andrew
>|||Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
going to take me a bit to get my head around this, but let me see if I
understand this:
Basically your passing the entire list of values as a string
(string.join,",") into the function, then creating a temp table in the
function which adds a row for each parameter, and joining my sql statement
inside my stored procedure to this newly created temp table to select the
required rows?
Again, i'm new to this so thanks for your patience and help!
Michael
"Bruce L-C [MVP]" wrote:
> Excellent article. It turns out that I got my code from Erland.
> Here is the function I use that I got from him: What you can do is to have a
> string parameter that is pass ed as a multivalue parameter and then change
> the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION 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
> GO
>
> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > Using a user defined function isn't the only way to achive this with SQL
> > Server.
> >
> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > performance.
> >
> > Andrew
> >
> >
>
>|||In my example below @.STO was a multi-select parameter. RS automatically is
sending the selection as a comma separated string. You don't need to do
anything to it. Then I pass that to the function I show below. That function
returns a table variable that you can then join to.
Note that you can test this from query analyzer by passing a comma separated
string to your stored procedure.
exec mystoredprocedure 'blah,bleh,etc'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
>> Excellent article. It turns out that I got my code from Erland.
>> Here is the function I use that I got from him: What you can do is to
>> have a
>> string parameter that is pass ed as a multivalue parameter and then
>> change
>> the string into a table.
>> This technique was told to me by SQL Server MVP, Erland Sommarskog
>> For example I have done this
>> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> So note this is NOT an issue with RS, it is strictly a stored procedure
>> issue.
>> Here is the function:
>> CREATE FUNCTION 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
>> GO
>>
>> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > Using a user defined function isn't the only way to achive this with
>> > SQL
>> > Server.
>> >
>> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
>> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
>> > for
>> > performance.
>> >
>> > Andrew
>> >
>> >
>>|||So question... Here is my code. Where would I insert this? the @.Mill, @.Port
can be mulitples (the @.Trip is always a single parameter)
ALTER PROCEDURE [dbo].[LumberLineUp]
@.Mill varchar(100) = '0',
@.Trip varchar(100) = '0',
@.Port varchar(100) = '0'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- INITIAL TABLES
CREATE TABLE [dbo].[#LumberLineUp](
[Vessel] [nvarchar](50) NULL,
[LoadPort][nvarchar](50) NULL,
[Trip][nvarchar](50) NULL,
[OrderID][nvarchar](20) NOT NULL,
[Mill][nvarchar](50) NULL,
[Mark][nvarchar](20) NULL,
[Product][nvarchar](160) NULL,
[Parent][nvarchar](160) NULL,
[FBM][real]NULL,
[Pkgs][real]NULL,
[Stowage][nvarchar](5) NULL,
[Grade][nvarchar](10) NULL,
[Length][nvarchar](10)NULL,
[LengthDesc][nvarchar](10) NULL,
[Port][nvarchar](50)NULL,
[LoadDate][nvarchar](50)NULL,
[ETADate][nvarchar](50)NULL
)
CREATE TABLE [dbo].[#LengthTally](
[OrderID] [varchar](20) NULL,
[Mark] [varchar](20) NULL,
[Tally] [varchar](120) NULL,
[Prod] [varchar](160)NULL,
[OrderPrev] [varchar](20) NULL,
[MarkPrev] [varchar](20) NULL,
[ProductPrev] [varchar](160)NULL,
[Length] [nvarchar](10) NULL,
[LengthPrev] [nvarchar](10) NULL,
[Count] [varchar](10) NULL,
[Test][varchar](10) NULL
)
-- Data for LumberLineUp Table
INSERT INTO dbo.#LumberLineUp
SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
location.location_id AS Mill,
order_detail.Mark, product_master.descrip AS Product,
product_parent.descrip AS parent,
CASE order_detail.qty_unit WHEN 'm3' THEN
order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN order_detail.qty
END AS FBM,
order_detail.alt_qty AS Pkgs, order_detail.Stowage,
product_master.grade, length_master.sort_fact AS Length,
length_master.descrip AS Legnth,
port.port_id AS Port, VesselTrips.LoadingDate,
tripdestinations.ETADate
FROM Vessel AS Vessel INNER JOIN
VesselVoyages AS VesselVoyages ON
VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
VesselTrips AS VesselTrips ON
VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
order_header AS order_header ON
order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
location AS location ON location.location_id =order_header.location INNER JOIN
order_detail AS order_detail ON order_detail.order_id
= order_header.order_id INNER JOIN
product_master AS product_master ON
product_master.product_id = order_detail.product_id INNER JOIN
product_master AS product_parent ON
product_master.product_part = product_parent.product_id INNER JOIN
length_master AS length_master ON
product_master.length_part = length_master.len_id INNER JOIN
TripDestinations AS tripdestinations ON
tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
tripdestinations.Port_ID =order_header.Destination_Port INNER JOIN
port AS port ON tripdestinations.Port_ID =port.port_id INNER JOIN
LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
AND (VesselTrips.ID IN (@.Trip)) OR
(location.location_id IN (@.Mill)) AND (VesselTrips.ID
IN (@.Trip)) AND (@.Port = '0')
ORDER BY OrderID, order_detail.Mark, Product, Length
"Michael C" wrote:
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
> > Excellent article. It turns out that I got my code from Erland.
> >
> > Here is the function I use that I got from him: What you can do is to have a
> > string parameter that is pass ed as a multivalue parameter and then change
> > the string into a table.
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> >
> > For example I have done this
> >
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> >
> > CREATE FUNCTION 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
> >
> > GO
> >
> >
> >
> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > > Using a user defined function isn't the only way to achive this with SQL
> > > Server.
> > >
> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > > performance.
> > >
> > > Andrew
> > >
> > >
> >
> >
> >|||Add another inner join with @.port
inner join charlist_to_table(@.port,Default)f on port.port_id = f.str
remove (port.port_id IN (@.Port))
Follow the same pattern as above with your other multi-value parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:48471539-1EA0-489C-B3F6-85240B1FE04E@.microsoft.com...
> So question... Here is my code. Where would I insert this? the @.Mill,
> @.Port
> can be mulitples (the @.Trip is always a single parameter)
> ALTER PROCEDURE [dbo].[LumberLineUp]
> @.Mill varchar(100) = '0',
> @.Trip varchar(100) = '0',
> @.Port varchar(100) = '0'
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- INITIAL TABLES
> CREATE TABLE [dbo].[#LumberLineUp](
> [Vessel] [nvarchar](50) NULL,
> [LoadPort][nvarchar](50) NULL,
> [Trip][nvarchar](50) NULL,
> [OrderID][nvarchar](20) NOT NULL,
> [Mill][nvarchar](50) NULL,
> [Mark][nvarchar](20) NULL,
> [Product][nvarchar](160) NULL,
> [Parent][nvarchar](160) NULL,
> [FBM][real]NULL,
> [Pkgs][real]NULL,
> [Stowage][nvarchar](5) NULL,
> [Grade][nvarchar](10) NULL,
> [Length][nvarchar](10)NULL,
> [LengthDesc][nvarchar](10) NULL,
> [Port][nvarchar](50)NULL,
> [LoadDate][nvarchar](50)NULL,
> [ETADate][nvarchar](50)NULL
> )
> CREATE TABLE [dbo].[#LengthTally](
> [OrderID] [varchar](20) NULL,
> [Mark] [varchar](20) NULL,
> [Tally] [varchar](120) NULL,
> [Prod] [varchar](160)NULL,
> [OrderPrev] [varchar](20) NULL,
> [MarkPrev] [varchar](20) NULL,
> [ProductPrev] [varchar](160)NULL,
> [Length] [nvarchar](10) NULL,
> [LengthPrev] [nvarchar](10) NULL,
> [Count] [varchar](10) NULL,
> [Test][varchar](10) NULL
> )
> -- Data for LumberLineUp Table
> INSERT INTO dbo.#LumberLineUp
> SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
> VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
> location.location_id AS Mill,
> order_detail.Mark, product_master.descrip AS Product,
> product_parent.descrip AS parent,
> CASE order_detail.qty_unit WHEN 'm3' THEN
> order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN
> order_detail.qty
> END AS FBM,
> order_detail.alt_qty AS Pkgs, order_detail.Stowage,
> product_master.grade, length_master.sort_fact AS Length,
> length_master.descrip AS Legnth,
> port.port_id AS Port, VesselTrips.LoadingDate,
> tripdestinations.ETADate
> FROM Vessel AS Vessel INNER JOIN
> VesselVoyages AS VesselVoyages ON
> VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
> VesselTrips AS VesselTrips ON
> VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
> order_header AS order_header ON
> order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
> location AS location ON location.location_id => order_header.location INNER JOIN
> order_detail AS order_detail ON order_detail.order_id
> = order_header.order_id INNER JOIN
> product_master AS product_master ON
> product_master.product_id = order_detail.product_id INNER JOIN
> product_master AS product_parent ON
> product_master.product_part = product_parent.product_id INNER JOIN
> length_master AS length_master ON
> product_master.length_part = length_master.len_id INNER JOIN
> TripDestinations AS tripdestinations ON
> tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
> tripdestinations.Port_ID => order_header.Destination_Port INNER JOIN
> port AS port ON tripdestinations.Port_ID => port.port_id INNER JOIN
> LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
> WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
> AND (VesselTrips.ID IN (@.Trip)) OR
> (location.location_id IN (@.Mill)) AND (VesselTrips.ID
> IN (@.Trip)) AND (@.Port = '0')
> ORDER BY OrderID, order_detail.Mark, Product, Length
>
> "Michael C" wrote:
>> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
>> going to take me a bit to get my head around this, but let me see if I
>> understand this:
>> Basically your passing the entire list of values as a string
>> (string.join,",") into the function, then creating a temp table in the
>> function which adds a row for each parameter, and joining my sql
>> statement
>> inside my stored procedure to this newly created temp table to select the
>> required rows?
>> Again, i'm new to this so thanks for your patience and help!
>> Michael
>>
>> "Bruce L-C [MVP]" wrote:
>> > Excellent article. It turns out that I got my code from Erland.
>> >
>> > Here is the function I use that I got from him: What you can do is to
>> > have a
>> > string parameter that is pass ed as a multivalue parameter and then
>> > change
>> > the string into a table.
>> > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> >
>> > For example I have done this
>> >
>> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> >
>> > So note this is NOT an issue with RS, it is strictly a stored procedure
>> > issue.
>> >
>> > Here is the function:
>> >
>> > CREATE FUNCTION 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
>> >
>> > GO
>> >
>> >
>> >
>> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > > Using a user defined function isn't the only way to achive this with
>> > > SQL
>> > > Server.
>> > >
>> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by
>> > > Erland
>> > > Sommarskog (SQL Server MVP) has a few alternatives trading
>> > > simplicity for
>> > > performance.
>> > >
>> > > Andrew
>> > >
>> > >
>> >
>> >
>> >|||Hello,
This works perfectly for me. I was wondering if there is a way to
incorporate the parameter "All" to select the entire list?
Thanks,
Deb
"Bruce L-C [MVP]" wrote:
> In my example below @.STO was a multi-select parameter. RS automatically is
> sending the selection as a comma separated string. You don't need to do
> anything to it. Then I pass that to the function I show below. That function
> returns a table variable that you can then join to.
> Note that you can test this from query analyzer by passing a comma separated
> string to your stored procedure.
> exec mystoredprocedure 'blah,bleh,etc'
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> > Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> > going to take me a bit to get my head around this, but let me see if I
> > understand this:
> >
> > Basically your passing the entire list of values as a string
> > (string.join,",") into the function, then creating a temp table in the
> > function which adds a row for each parameter, and joining my sql statement
> > inside my stored procedure to this newly created temp table to select the
> > required rows?
> >
> > Again, i'm new to this so thanks for your patience and help!
> >
> > Michael
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Excellent article. It turns out that I got my code from Erland.
> >>
> >> Here is the function I use that I got from him: What you can do is to
> >> have a
> >> string parameter that is pass ed as a multivalue parameter and then
> >> change
> >> the string into a table.
> >> This technique was told to me by SQL Server MVP, Erland Sommarskog
> >>
> >> For example I have done this
> >>
> >> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >>
> >> So note this is NOT an issue with RS, it is strictly a stored procedure
> >> issue.
> >>
> >> Here is the function:
> >>
> >> CREATE FUNCTION 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
> >>
> >> GO
> >>
> >>
> >>
> >> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> >> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> >> > Using a user defined function isn't the only way to achive this with
> >> > SQL
> >> > Server.
> >> >
> >> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> >> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
> >> > for
> >> > performance.
> >> >
> >> > Andrew
> >> >
> >> >
> >>
> >>
> >>
>
>sql
passing multiple values to a stored procedure
hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name lik
e
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
>
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values[vbcol=seagreen]
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)[/vbcol
]
thanks
red
"Simon Worth" wrote:
[vbcol=seagreen]
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
>
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many[vbcol=seagreen]
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name lik
e
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
>
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values[vbcol=seagreen]
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)[/vbcol
]
thanks
red
"Simon Worth" wrote:
[vbcol=seagreen]
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
>
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many[vbcol=seagreen]
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>
passing multiple values to a stored procedure
hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
?
thanks
red
Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> ?
> thanks
> red
|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list[vbcol=seagreen]
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values
>
|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>
|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]
|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,[vbcol=seagreen]
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many
>
|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
?
thanks
red
Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> ?
> thanks
> red
|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list[vbcol=seagreen]
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values
>
|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>
|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]
|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,[vbcol=seagreen]
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many
>
|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>
Subscribe to:
Comments (Atom)