Partitions and FileGroups: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.
Your code above should work when done like this:

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).
|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

sql

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Passing parameter in LIKE statement with '%'

How do i handle this code:

CREATE PROCEDURE sp_Test

@.pchrTest1

SELECT

fldTest1,

fldTest2

FROM

tblTest1

WHERE fldTest1 LIKE '%' + @.pchrTest1

This codes seems it does not work.

Thanks in advance

CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)

Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)

It does work like this (without dynamic SQL).|||

Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

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.

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

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 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

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

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 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!

sql

Passing 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

)

...

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.

Wednesday, March 28, 2012

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

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 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
> 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
>|||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...
> > 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
> >
>
>|||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
> 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...
> > > 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
> > >
> >
> >
> >|||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...
> > 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...
> > > > 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
> > > >
> > >
> > >
> > >
>|||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.)
>
> > 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
>
>

Friday, March 30, 2012

Passing parameter in LIKE statement with '%'

Passing parameter in LIKE statement with '%'

Passing parameter in LIKE statement with '%'

passing parameter

Passing output parameter from procedure to variable

PASSING ORDER BY AS PARAMETERS

Passing null value parameter to a data-driven subscription

Passing null value parameter to a data-driven subscription

Passing NULL value not working

Passing Null to Stored Procedure in Reporting Services

Wednesday, March 28, 2012

Passing multi-select params to a stored procedure

passing multiple values to a stored procedure

passing multiple values to a stored procedure

passing multiple values to a stored procedure

Partitions and FileGroups

Blog Archive

About Me