November 7, 2008 at 4:23 am
I have a stored procedure that is used to return results to a report.
The original design brief allowed for users to enter a single parameter 'SalesOrderId' which was then passed to the stored proc.
EXEC sp_PaymentsAgainstSalesOrder @SalesOrderId
I modified the report (added additional dataset) to allow users to select from a list of all available SalesOrderId's (that meet the criteria).
SELECT DISTINCT SalesOrders.SalesOrderId
FROM SalesOrderItems INNER JOIN
SalesOrders ON SalesOrderItems.SalesOrder = SalesOrders.SalesOrder INNER JOIN
dbo.Milestones ON SalesOrderItems.SalesOrderItem = dbo.Milestones.SalesOrderItem
Now another request. The user would like the ability to select multiple Sales Order records then display in the report.
Although users can select multiple values (SalesOrderId's) the report returns an error as the stored proc expects a single value to be passed.
Error: Query execution failed for data set 'Sales' Must declare the scalar variable @SalesOrderId
Question: Is it possible modify the stored proc to allow multi select from a report parameter?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 7, 2008 at 4:50 am
you can pass multiple SalesOrderID, see the below example, and re-write you sp accordingly.
Code:
declare@OrderIDvarchar (100)
,@Querynvarchar (2000)
set@OrderID= '10248, 10249, 10260'-- Multiple values
set@Query= 'select * from Northwind.dbo.Orders where OrderID in (' + @OrderID + ')'
execsp_executesql@Query
use Dynamic Query.
November 7, 2008 at 4:51 am
Thanks for the reply. Maybe I do not follow but I would not be able to hard code the SalesOrderId values?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 7, 2008 at 5:04 am
in the previous example just I gave ref. see the below code for more view:
-- Code
-- Procedure Creation
createprocedure#Orders
(@OrderIDvarchar (100)
)
as
declare@Querynvarchar (2000)
set@Query= 'select * from Northwind.dbo.Orders where OrderID in (' + @OrderID + ')'
print@Query
execsp_executesql@Query
go
--usage:
exec#Orders@OrderID = '10248, 10249, 10260'
-- End of Code.
November 7, 2008 at 5:19 am
Thanks for the update, I will see if I can work out how to modify from your example.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 7, 2008 at 6:47 am
You might want to do a search of the scripts over on the left. Look for functions that convert comma delimited lists into a table. That way you don't have to rely on dynamic SQL. That's a potentially problematic way to solve the issue. Instead, use one of those functions to pivot the delimited list to a table and then JOIN to the table. We've found that this approach works better.
If you have really large data sets, sometimes we've found that using an XML file as input and then using OPENXML to make it into a table that you can again join against works well. But that's only outperformed the function method on really large (millions of rows) data sets. This is because the overhead of opening up XML documents far outweighs the benefits at smaller data sets.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2008 at 11:21 am
Thanks guys. All sounds complex 🙂
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 7, 2008 at 3:08 pm
There is a function fn_split().Make use of it.
fn_split(@Orderid)
u can pass multiple values to order id now.
November 8, 2008 at 2:47 am
Thanks to all that have given time to this request.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
November 8, 2008 at 10:06 am
Ramu Reddy (11/7/2008)
There is a function fn_split().Make use of it.
There are many such functions, but none of them are built into SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply