Stored Procedure? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Stored Procedure?

Author

Topic

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-21 : 14:15:19

I have a base query where I set @ProductCategory. If I have 5 of those, and a long query that needs each to be defined specifically to pass through calculations on each @ProductCategory, how would I write it to cycle through each @ProductCategory.

For simplicity sake:
Select *
FROM Products
WHERE ProductCategory = @ProductCategory

What would be another way to cycle through each @ProductCategory to get the same results as you would have with

Select *
FROM Products

I believe this is tied to a Stored Procedure. Basically, would want similar to a loop statement to run ProductCategory1 through however many ProductCategories exist in the Products table to produce the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 15:43:01

think set base. Pass the category values as a list as below and process them as a batch


CREATE PROCEDURE ProcName
@ProductCategory varchar(5000)
AS
Select p.*
FROM Products p
INNER JOIN dbo.ParseValues(@ProductCategory,',')f
ON f.Val = p.ProductCategory
....
GO

parsevalues can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

Also see
http://www.sommarskog.se/arrays-in-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-21 : 19:31:22

Thanks for the response and helping to continue my SQL Server growth... I will investigate this and write back if there are any issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources