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.
| Author |
Topic |
|
potn1
Starting Member
33 Posts |
 Posted - 2011-04-29 : 20:11:58
|
| I am looking to run a query off of a temp table and pass some parameters. I needed to create some sort of temporary table that included an aggregate function with the group by. This needed to be done because I only wanted specific records displayed and I then want to create another query to grab data from this temporary table or virtual table.I first attempted to use a view and everything worked like I had planned. I was then going to JOIN this table in my other query. But then I discovered that you cannot pass parameters in a view. So I then attempted a stored procedure. I created my temporary table with the 2 date parameters I needed and then totaled up user records by month based on these dates. I then did a JOIN on this table and was able to display all of the records I needed. So currently right now by stored procedure is working with 2 parameters (star and end date). My next question is that I will most likely need a couple more parameters here. My query will run correctly but then on my actual report that I am doing I have a couple drop down lists. The first drop down having 2 options: start_amount and end_amount do not equal. The other option is the start_amount is less than the end_amount. So based on the users selection in the drop down one of these needs to compile within my stored procedure. I was wondering if I needed to make some sort of if/else in the stored procedure like if the user selects this option then show my values where they do not equal or that one is less than the other. Within my actual code this drop down selected is stored and passed as a boolean. Is there a way I can pass a boolean parameter in from my code into the stored procedure? I then know I will need to create a parameter for this within my code.So essentially at the end of my query I need some parameter like: (start_amount <> end_amount) to equal the parameter based on the user selection.Any help is appreciated, thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-29 : 20:21:37
|
| you can pass the two values @p1, @p2 andwhere (@p1=1 and start_amount <> end_amount)or (@p2 = 1 and start_amount < end_amount)As to the dtatypes of the parameters - it depends on the front end - could be int an 1/0 or varchar and y/n or something else. Try it and see.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
potn1
Starting Member
33 Posts |
Posted - 2011-04-29 : 20:39:01
|
| So @p1 = 1 or @p1 = Yes (need to see what works in my environment) would basically be the value of the drop down being selected and based on that the amounts will either not be equal or less than?And when I call this stored procedure within my code I will include both of these parameters and that should work based on the drop down selection?I may have also forgotten to message the start_amount and end_amount are columns that are displayed in my table but maybe that was just assumed. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-04-30 : 17:41:28
|
| >> I am looking to run a query off of a temp table and pass some parameters. <<No. Tell us what you want and not how you want to do it. Temp tables are usually a bad idea; they mimic scratch tapes and destroy the advantages of the Relational model. >> I needed to create some sort of temporary table that included an aggregate function with the group by. This needed to be done because I only wanted specific records [sic: rows are not records] and I then want to create another query to grab data from this temporary table or virtual table. <<Yep! You said “record” and that along tells us that you have no idea how RDBMS works. You are thinking in sequential files –- magnetic tapes and punch cards. >> I first attempted to use a view and everything worked like I had planned. I was then going to JOIN this table in my other query. But then I discovered that you cannot pass parameters in a VIEW. <<In the words of Homer Simpson – “Doh!” VIEWs are virtual tables; tables do not have parameters. >> So I then attempted a stored procedure. I created my temporary table with the 2 date parameters I needed and then totaled up user records [sic] by month based on these dates. I then did a JOIN on this table and was able to display all of the records [sic] needed. <<Yep! Step by step procedural mindset! You are locking your mindset into the 1950's and not RDBMS. >> So currently right now by stored procedure is working with 2 parameters (start and end date). My next question is that I will most likely need a couple more parameters here. My query will run correctly but then on my actual report that I am doing I have a couple drop down lists. <<Drop-down list? SQL has no such thing. That is the front end. SQL is a TIERED ARCHITECTURE. This is not just SQL; this is fundamental IT. The “database stuff” is done on one side of the wall; the user application is on the other side of the wall. Please post DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. >> The first drop down having 2 options: start_amount and end_amount do not equal. The other option is the start_amount is less than the end_amount. So based on the users selection in the drop down one of these needs to compile within my stored procedure. <<Whose code we cannot see!!>> I was wondering if I needed to make some sort of if/else in the stored procedure like if the user selects this option then show my values where they do not equal or that one is less than the other. <<Looking at the invisible DDL you posted, I will guess that you meant to use CASE expressions and not IF-THEN-ELSE procedural code. >> Within my actual code this drop down selected is stored and passed as a Boolean. Is there a way I can pass a Boolean parameter in from my code into the stored procedure? I then know I will need to create a parameter for this within my code.<<Boolean flags? Like Assembly language? As someone who has written eight books, 1000+ articles and has a history in SQL, I am telling you, that you are doing it (to quote Ed Dijkstra) completely wrong.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Question about VIEWS and Stored Procedures