October 4, 2012 at 9:00 am
Below is a script to create a table, and one to create and execute a stored procedure on the table, I am trying to build a report that passes multiple values in one parameter, but the stored procedure takes the inputs as a comma delimited list. I came across some sites that gave script for a splitlist function that will individually delimit the list but when I put that into the stored procedure it is not recognized. Does anybody see anything wrong with what I have here and if so, have a solution?
Thanks a bunch!
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL
)
GO
INSERT INTO dbo.Customer
Values( 'John','M.','Lynch'),
('Jason','TE','Whitten'),
('Jay','OldSchool','Novachek')
GO
------------------------Create stored procedure
CREATE PROCEDURE Example
-- Add the parameters for the stored procedure here
@CustomerID int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT CustomerID, LastName
FROM dbo.Customer
WHERE CustomerID IN (select * FROM dbo.Splitlist(@CustomerID, ',')) -------Here is the problem, I cannot get the sp to recognize the function unless I delimit the query and set it as a variable
-----and append the where statement to the end. How do I do this?
END
GO
-----Execute stored procedure for the table
DECLARE@return_value int
DECLARE@CustomerId int
EXEC@return_value = [dbo].[Example]
@CustomerId = 1---------Would like to be able to execute for IN ('1','2','3')
GO
October 4, 2012 at 9:07 am
What you need to do is either use a table valued parameter or change your datatype to a varchar and then split the variable inside the proc.
If your Split function is using a while loop or xml, I suspect you would gain some good knowledge by reading the article in my signature about splitting strings. You will most likely replace your split function after reading that article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 9:32 am
Here is an example of doing this both ways:
First is the Delimited string method. The code for the DelimitedSplit8K function can be found at the link in my signature about splitting strings. I added the delimiter parameter to provide flexibility. If you want, you could eliminate that and just use a comma (or whatever delimiter you prefer).
CREATE PROCEDURE Example
(
@CustomerID varchar(8000),
@Delimiter char(1)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT CustomerID, LastName
FROM dbo.Customer c
join dbo.DelimitedSplit8K(@CustomerID, @Delimiter) s on c.CustomerID = s.Item
END
GO
exec Example @CustomerID = '1,2,3', @Delimiter = ','
The table valued parameter approach takes a little more setup and initially looks like overkill. However, it can provide an incredible easy way to use for developers.
The first thing you have to understand about TVP is that you have to declare the type as user defined table datatype. Because you are only needing to use a list of ints I made this generic so it could be reused by other processes. You can make your tables as complex as needed but when just a simple datatype I like to keep it generic.
create type IntTable as Table
(
val int null
)
You can now declare variables and parameters of type IntTable.
Here is the proc with the table parameter.
CREATE PROCEDURE ExampleTableParam
(
@CustomerID IntTable readonly
) as begin
SELECT CustomerID, LastName
FROM dbo.Customer c
join @CustomerID s on c.CustomerID = s.val
end
This one is a little more complicated to use from just straight sql because you need your newly defined table type. You also need to populate the rows but this is still pretty straight forward.
declare @MyTable IntTable
insert @MyTable
select 1 union all
select 2 union all
select 3
exec ExampleTableParam @CustomerID = @MyTable
Now I mentioned how easy this is for developers. Let's say you have datagrid that allows multiple row selection or anything else that can be directly converted to an array of ints. All you have to do is pass list as your parameter. In .NET you can pass a DataTable, combobox items or just about anything. I have been working on an article discussing this but have never found the time to finish it.
Hope this helps.
Oh and btw...thanks for posting ddl and sample data in your first post. It makes helping infinitely easier. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 11:36 am
Thank you guys very much for the help and the additional reading! Good Stuff!
October 4, 2012 at 11:38 am
You're welcome. Hope my posts helped. Post back if you run into any issues or need a little help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 11:40 am
That was what I needed to get it to run!
June 6, 2013 at 12:09 am
sir can u help me to find out?????how can i pass more values in the below parameter???
SET @cursemcredits = ( SELECT SUM(credit)
FROM ( SELECT rs.sub_code,
sm.sem_attended,
SUM(rs.credit_points) credit
FROM student_details sd
INNER JOIN dbo.student_marks sm ON sd.roll_no = sm.roll_no
INNER JOIN grade g ON g.grade_id = sm.grade_id
INNER JOIN dbo.regulation_subject rs ON rs.regulation_sub_id=sm.regulation_sub_id
INNER JOIN dbo.regulation_info ri ON ri.regulation_no = rs.regulation_no
AND rs.regulationinfo_id = ri.regulationinfo_id
WHERE sd.roll_no = @roll_no
AND sm.sem_attended <= @cur_sem
AND sm.sem_attended = ri.semester
AND sm.sem_attended <> -1
AND sm.sem_attended <> 0
AND rs.sub_name NOT LIKE '%#'
GROUP BY rs.sub_code,
sm.sem_attended
) a
)
sir,it shows all semester credits for that student like 50 but what i need is 3 values like 17,17,16 means single semester credits...what should i add here to gt a o/p like dat?????:-)
June 6, 2013 at 7:56 am
nitha jen (6/6/2013)
sir can u help me to find out?????how can i pass more values in the below parameter???SET @cursemcredits = ( SELECT SUM(credit)
FROM ( SELECT rs.sub_code,
sm.sem_attended,
SUM(rs.credit_points) credit
FROM student_details sd
INNER JOIN dbo.student_marks sm ON sd.roll_no = sm.roll_no
INNER JOIN grade g ON g.grade_id = sm.grade_id
INNER JOIN dbo.regulation_subject rs ON rs.regulation_sub_id=sm.regulation_sub_id
INNER JOIN dbo.regulation_info ri ON ri.regulation_no = rs.regulation_no
AND rs.regulationinfo_id = ri.regulationinfo_id
WHERE sd.roll_no = @roll_no
AND sm.sem_attended <= @cur_sem
AND sm.sem_attended = ri.semester
AND sm.sem_attended <> -1
AND sm.sem_attended <> 0
AND rs.sub_name NOT LIKE '%#'
GROUP BY rs.sub_code,
sm.sem_attended
) a
)
sir,it shows all semester credits for that student like 50 but what i need is 3 values like 17,17,16 means single semester credits...what should i add here to gt a o/p like dat?????:-)
Two things.
1) You should start your own thread for this instead of jumping onto another thread.
2) In order to help we will need a few things:
-- Sample DDL in the form of CREATE TABLE statements
-- Sample data in the form of INSERT INTO statements
-- Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply