February 23, 2011 at 11:44 am
Hi Experts,
I wanted to:
1. make a SQL query box on a page so that it links to the database and users can make their own queries from ASP. Is that possible?
2. I have a dropdown box where I specify parameters for a SQL stored procedure. Do you know how to include "select all" from the dropdown list? For example I want to output the revenue for Jan, Feb and March. I can select Jan, Feb and March from the dropdown list and output the revenue monthly. However I wanted to create a option "all" where the output is the sum of revenue for the 3 months.
I am having trouble making these happen and would appreciate any help! Thank you so so much in advance!!!
TR
February 25, 2011 at 9:44 am
1. make a SQL query box on a page so that it links to the database and users can make their own queries from ASP. Is that possible?
Be VERY careful if you try this. You need to read up on sql injection. This is incredibly dangerous. I would recommend not doing this unless it is used only internally by qualified people and only on an intranet. Even then I would suggest finding another way to get the users the info they need.
2. I have a dropdown box where I specify parameters for a SQL stored procedure. Do you know how to include "select all" from the dropdown list? For example I want to output the revenue for Jan, Feb and March. I can select Jan, Feb and March from the dropdown list and output the revenue monthly. However I wanted to create a option "all" where the output is the sum of revenue for the 3 months.
This will most likely require some changes to your stored proc. You are going to have some aggregate data and grouping if the user wants this additional info.
_______________________________________________________________
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/
February 25, 2011 at 11:32 am
ichiyo85 (2/23/2011)
1. make a SQL query box on a page so that it links to the database and users can make their own queries from ASP. Is that possible?
Since we are not very clear whether you want them to write the "entire" sql query or just construct the where clause; I would second @sean Lange. However, I've done some code where my data fields, and which tables they come from, were fixed and user could create the where condition. And to be clear, my where clause construction was never in user typing in clear text. It has to be through a set of options that you give -- this prevents you from simple SQL injections.
ichiyo85 (2/23/2011)
2. I have a dropdown box where I specify parameters for a SQL stored procedure. Do you know how to include "select all" from the dropdown list? For example I want to output the revenue for Jan, Feb and March. I can select Jan, Feb and March from the dropdown list and output the revenue monthly. However I wanted to create a option "all" where the output is the sum of revenue for the 3 months.
Not sure how your fields are arranged but for things like that I would do something like
WHERE myMonthColumn = ISNULL(@monthVariable, myMonthColumn) And when you user selects the option "All", just pass null as the parameter value.
February 25, 2011 at 2:39 pm
Hi experts,
Thank you very much for the reply!
My code looks like this:
create proc Exit_Rate_by_SBU_month_seg_reg2
@month nvarchar(200),
@Region nvarchar(255)
as
select [Exit Rate (USD)] as [Sum of Exit Rate],Region
from Exit_Rate_RR$
where Period =ISNULL(@month,Period )
and Region=isnull(@Region,Region)
group by Region
exec Exit_Rate_by_SBU_month_seg_reg2 '','','','','Americas'
ISNULL isn't working for me as I need to use "group". Any further advice will be greatly appreciate it!
March 2, 2011 at 9:14 am
Your query doesn't seem right and it's invocation too. It has nothing to do with ISNULL or the GROUPBY clause.
Some discrepancies I can see from your post
a.) Number of parameters in the create procedure and procedure call do not match.
b.) The select query in the procedure is ill formed. The aggregate clause is missing on the ExitRate column.
Correct those and try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply