Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure and Parameters Table Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
To all:

I am creating a paramaters table to store values for mulitple sctipts.

For example, I will create a Table called ScriptParameters with the fields ID, scriptname, ScriptValue, and active.

Sample data may be:

ID, scriptname, scriptvalue,active
1, fruit, apple,1
2, fruit, grapes,0
3, fruit, oranges,1
4, veggies, peas,1
5, veggies, corn,1
6, veggies, spinach,1
7, veggies, carrots,1
8, candy, chocolate,1
9, candy, lollipops,0

I want to create a query that will allow me to check and return all values with active fruit dynamically.

If I did this hardcoded, it would look like this:

SELECT * FROM ScriptParameters WHERE scriptvalue='apple' OR scriptvalue='grapes' OR scriptvalue='oranges' AND Active='1'

How can I make the scriptvalue dynamic and maintain the proper syntax?

Any advice would be greatly appreciated!



Post #1516525
Posted Thursday, November 21, 2013 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
rayh 98086 (11/21/2013)
To all:

I am creating a paramaters table to store values for mulitple sctipts.

For example, I will create a Table called ScriptParameters with the fields ID, scriptname, ScriptValue, and active.

Sample data may be:

ID, scriptname, scriptvalue,active
1, fruit, apple,1
2, fruit, grapes,0
3, fruit, oranges,1
4, veggies, peas,1
5, veggies, corn,1
6, veggies, spinach,1
7, veggies, carrots,1
8, candy, chocolate,1
9, candy, lollipops,0

I want to create a query that will allow me to check and return all values with active fruit dynamically.

If I did this hardcoded, it would look like this:

SELECT * FROM ScriptParameters WHERE scriptvalue='apple' OR scriptvalue='grapes' OR scriptvalue='oranges' AND Active='1'

How can I make the scriptvalue dynamic and maintain the proper syntax?

Any advice would be greatly appreciated!



Why do you have to list all the scriptvalues? Can't you use the scriptname?

SELECT * FROM ScriptParameters WHERE scriptname = 'fruit' AND Active = 1

I suspect you might also get a lot of benefit from reading this article about catch all type queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1516540
Posted Thursday, November 21, 2013 12:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
Hi,

Thank you. The part I am getting hung up on or just don't understand is having the parameters for a where statement be variable. How do I make sure the sytax stays complete.

Thank you for article will readh now.
Post #1516551
Posted Thursday, November 21, 2013 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
rayh 98086 (11/21/2013)
Hi,

Thank you. The part I am getting hung up on or just don't understand is having the parameters for a where statement be variable. How do I make sure the sytax stays complete.

Thank you for article will readh now.


Like this?

declare @scriptname varchar(10) = 'fruit'

SELECT * FROM ScriptParameters WHERE scriptname = @scriptname AND Active = 1

Or if you are wanting to make this into a stored procedure...

create procedure GetScriptStuff
(
@scriptname varchar(10)
) as
SELECT * FROM ScriptParameters
WHERE scriptname = @scriptname AND Active = 1


Then you could call your stored proc like this:

exec GetScriptStuff @scriptname = 'fruit'



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1516555
Posted Thursday, November 21, 2013 12:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:20 PM
Points: 47, Visits: 126
Yeah, that's what Im looking for! Thank you very much!
Post #1516556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse