Query Duration Using Parameters Vrs No Parameters
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
ADVERTISEMENT
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Dec 19, 2006
I am trying to search a SQL database using a TextBox and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search. If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results. While stepping through the code both parameters do get assigned the correct values to make the sql statement valid. Any advice would be appreciated.
.... code that does not work .....
string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString; string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery";
conn = new SqlConnection(connection); comm = new SqlCommand(sqlquery, conn);
String searchQuery = searchBox.Text; String searchItem = DropDownList1.SelectedValue;
comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchQuery"].Value = searchQuery; comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchItem"].Value = searchItem;
......
View 1 Replies
View Related
Jul 11, 2007
Hi all,
I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause.
Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters.
This is what I am trying to do: private DataSet GetSalesDataBas()
{
DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection("...");
SqlCommand command = connection.CreateCommand();
SqlParameter prm_1 = new SqlParameter();
SqlParameter prm_2 = new SqlParameter();
SqlParameter prm_3 = new SqlParameter();
SqlParameter prm_4 = new SqlParameter();
SqlParameter prm_5 = new SqlParameter();
SqlParameter prm_6 = new SqlParameter();
SqlParameter prm_7 = new SqlParameter();
prm_1.ParameterName = "@pNum";
prm_2.ParameterName = "@sNum";
prm_3.ParameterName = "@paramLoc";
prm_4.ParameterName = "@paramDist";
prm_5.ParameterName = "@paramPNum";
prm_6.ParameterName = "@paramDesc";
prm_7.ParameterName = "@paramBuild";
prm_1.Value = TextBox2.Text.Trim();
prm_2.Value = TextBox3.Text.Trim();
prm_3.Value = DropDownList15.SelectedValue;
prm_4.Value = DropDownList12.SelectedValue;
prm_5.Value = DropDownList21.SelectedValue;
prm_6.Value = DropDownList13.SelectedValue;
prm_7.Value = DropDownList18.SelectedValue;
command.Parameters.Add(prm_1);
command.Parameters.Add(prm_2);
command.Parameters.Add(prm_3);
command.Parameters.Add(prm_4);
command.Parameters.Add(prm_5);
command.Parameters.Add(prm_6);
command.Parameters.Add(prm_7);
string strWhere = "";
if (DropDownList23.SelectedIndex == 0)//if no sort field selected
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location = @paramLoc" +
" AND m.[District] = @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")";
}
else
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location= @paramLoc" +
" AND m.[District]= @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")" +
" ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue;
}
string sqlSalesData = @"SELECT * FROM Main m" + strWhere;
command.CommandText = sqlSalesData;
SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}
View 7 Replies
View Related
Mar 14, 2002
I am creating a package within which I have a query step that needs to accept
a value from a variable step that has already been run, but I cant get it to work and the books online dont help!
The query is simple
insert into table1
select col1 = cola,
col2 = colb,
col3 = ?
from tableA
Even though the variable that I want use has been set, I cant get the query to recognise there are any parameters. If I click the parameter button in the query set-up, I get a message saying that col3 is an invalid column name and then a box saying that an error occured when parsing the statement fro parameters !
Can anybody help ?
Thanks
Tom
View 1 Replies
View Related
Oct 19, 2006
I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted....
I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...
ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...
Any ideas?!
Thanks!!! :)
View 9 Replies
View Related
Jul 25, 2007
Hi there,
I'm developing one MDX query to place in my report, but I'm having a lot of troubles when using one of my report parameters.
I hope I can explain me good enough.
I have a report parameter that's used in the WHERE clause to choose the value of one of my dimensions, the problem is that I'm also trying to compare the value of this parameter to decide the value of another dimension, but the comparison isn't working at all.
Let me show one example:
The parameter name in question is: DimFolderWalletDesciption.
Code Snippet
IIF(
@DimFolderWalletDesciption = [Dim Folder].[Wallet Desciption].&[Investimento],
[Dim Indexation].[Indexation Group].[Rendimento Variável],
[Dim Indexation].[Indexation Group].[Indexation Group].ALLMEMBERS
)
This is suppost to do the following: If my parameter as the selected value "Investimento", then the "Indexation Group" selected should be "Rendimento Fixo" if not, there shouldn't be any "filter" in the Dimension "Indexation Group".
I've tryed a lot of combinations, but none works, what am I doing wrong?
Thanks in advance.
Regards.
View 1 Replies
View Related
Apr 18, 2007
I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement -
strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id"
I have the parameters defined as
cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName))
cmd.Parameters.Add(New SqlParameter("@id", _Id))
_FirstName & _Id are the private variables whose values a set thru set and get methods. I expect _FirstName to have the new value I am keying in the form.
Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values.
Please help.
Thanks,Shaly
View 23 Replies
View Related
Feb 8, 2007
I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.Possible?
View 4 Replies
View Related
Mar 6, 2007
Hi all. I have a problem on my query. let me explain first the scenario.
I have a windows application project that views a report through report viewer(Report of all employees that have a religion of adventist, born again, catholic, baptist, etc....). In my windows application, I have checkboxes for a specific religion. So, if I checked baptist and catholic, report generated is all employee that has religion of baptist and catholic.
My concern is, How could i make a query of this?
sample tables.
table - employee
dcno name religion
1 jon baptist
2 rose catholic
3 joy baptist
4 mike adventist
5 soy born again
6 rich protestant
you can supply additional table if needed.
thanks
-Ron-
View 7 Replies
View Related
Jul 18, 2006
guys i'm trying to use a Lookup in a dataflow that looksup stuff in the results of a query.
Problem I have is that the query needs to take two parameters.. (Source
and BaseCurrency in the code below) and i can't figure out how to
supply the parameters..
Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...
PJ
SELECT ForeignCurrency, RateFromFile AS YesterdaysRate
FROM inputrates IR
WHERE fileheaderid in (
SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)
View 10 Replies
View Related
Dec 13, 2007
I'm adding parameters to an already working query for a standard report I'm building. The object I am connecting to is an Oracle object (I have SP2 installed in all the appropriate places). I'm changing two date comparisons from hard coded date values to two query parameters. Based upon what MS's Step by Step book on Reporting Services is telling me, I have my query parameters correctly pointing to my Parameters collection. Thing is, when I click the preview button, I get subject error message -- "illegal variable name/number". Here's my query:
SELECT S.COVER_DATE, S.AD_NBR, S.APP_NBR, S.PRODUCT, S.EDITION, S."SECTION", S.PROGRAM_RATE, S.NET_AMT, S.ACCOUNT_ID,
ACT.SALES_ID
FROM MDTI.SCHEDULE S INNER JOIN
MDTI.ACCTSALES ACT ON S.ACCOUNT_ID = ACT.ACCOUNT_ID AND S.PRODUCT = ACT.PRODUCT
WHERE (S.COVER_DATE BETWEEN "@StartCoverDate" AND "@EndCoverDate") AND
(ACT.EXPIRE_DATE > '30-NOV-07') AND
(ACT.EFFECTIVE_DATE < '01-DEC-07')
The query parm names start with "@" in the WHERE clause. Nothing to it, right?
Any ideas why I am getting this error? SBS is telling me that I must select the Oracle data provider instead of the generic ODBC data provider in the Data Link Properties of my data source. I can't get anyone to show me where the Data Link properties are.
I'm looking for a solution to this error.
Respectfully Submitted,
Dave Matthews
Atlanta, GA
aka FlooseMan Dave
View 5 Replies
View Related
Mar 23, 2008
I am developing a summary report that will have multiple tables, charts and matrixes, all using the same set of data. However, one table may only show one month of data while another will show three months. If they all use the same Dataset with the same data parameters, is the data only pulled once? And then each component can use Filters to further refine the data? If this is true, this would seem to be the best option.
Or does each report component execute the query independantly?
Before I get too far down the road developing this report, I'd like to know the best way to do it from the beginning.
Thank you for your input.
Rob
View 3 Replies
View Related
Mar 17, 2008
I am trying to build a report that has two parameters that are entered by the user. They will be a simple string that is basically a six digit unique identifier number called DEALID. I have my report set so that I can enter one of these Deal ID's and it will pull in all of the fields I need. However, within the same report I want to have the user be able to enter a second Deal ID and have it pull in effectively the same values, yet associated with that new ID.
The problem seems to be that I am trying to have multiple parameters querying the same field in the database. Is this something that can be done? I have tried naming them all uniquely with a new alias and that does not seem to help.
Thank you
View 16 Replies
View Related
Oct 9, 2006
DELETE T1
WHERE EXISTS
(SELECT *
FROM T2 A
WHERE A.C1= T1.C1
AND A.C3 >= ?)
Results in below error (OLEDB SQL TASK):
" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any resolution?
View 9 Replies
View Related
Apr 20, 2001
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL
SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
Thanks in advance for your help.
View 1 Replies
View Related
Jun 12, 2006
I need to create a stored procedure that will search some tables.
The stored procedure will be passed some parameters that may or may not have a value.
I have googled the best way to do this.
I found this post as an example: Optional Search Parameters
and also found this example : Optional Parameters in T-sQL
I am trying to figure out the best way to do this.
In the past I would build a dynamic query like the following.
SQL Code:
Original
- SQL Code
CREATE PROCEDURE [dbo].[Search_Results]
@SUBCITY VarChar(100) = 'Any'
AS
------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------
Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*
FROMTable1
WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '
ORDER BY Column ASC'
Execute (@SQLString)
GO
CREATE PROCEDURE [dbo].[Search_Results] @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN SET @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = ' SELECT * FROM Table1 WHERE Table1.ID IS NOT NULL ' + @SUBCITYString + ' ORDER BY Column ASC' Execute (@SQLString) GO
However this is really cumbersome to create and is not fun debugging!
Does one of these ways have an advantage over the other? Or is there another way to do this?
Thank you!
View 2 Replies
View Related
Apr 23, 2012
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1
id - autonumeric
id_user - id from user
dates - date of register
table2
id - user id
name - user name
query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;
subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
View 3 Replies
View Related
Mar 13, 2014
I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.
Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.
For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.
SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName
[Code] ....
View 3 Replies
View Related
Jun 1, 2008
Is anybody here knowing how to create a dynamic query based on a multi-value parameter?
e.g. there is a multi-value report parameter called names. For a static query, the where clause of a select statement likes the following
select * from students where name=@names
For the dynamic one, I tried something like the below, but it did not work.
="select * from students where name=(" & Join(Parameters!names ,',') & ")"
Any suggestion would be great appreciated.
Thanks,
KY
View 2 Replies
View Related
Mar 19, 2008
I havea 2 part issue with a query I'm trying to run for a report.
I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?
Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?
Here’s the query (thanks!):
SELECT incident.incid_id,
incident.incid_short_desc,
incident.incid_received_date_time,
incident.incid_closed_date_time,
security_users.description,
incident.incid_assigned_to,
incident.tagged_delete_flag,
activity_result_master.result_desc,
incident_priority_master.priority_desc,
activities.result_id
FROM activities AS activities INNER JOIN
incident AS incident ON
activities.incid_id = incident.incid_id INNER JOIN
activity_result_master AS activity_result_master ON
activities.result_id =
activity_result_master.result_id
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN
incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id
WHERE incident.tagged_delete_flag = 'N' AND
activities.result_id = '6' AND
(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND
incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR
CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate
ORDER BY incident.incid_assigned_to, incident.incid_id
View 2 Replies
View Related
Aug 10, 2007
Hi
I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:
TITLE: Microsoft Visual Studio
------------------------------
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
------------------------------
ADDITIONAL INFORMATION:
El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)
------------------------------
I would really appreciatte any help.
Thanks.
View 5 Replies
View Related
Mar 23, 2008
Hi, could someone explain to me with sample code how to write a sql query using parameters to update a record, as I am new to this.
thanks
View 4 Replies
View Related
Nov 27, 2007
Hello everyone,
I'm having trouble designing a query for an ASP.NET project I'm working on. The main portion of the query is done and works, but I need to modify it to accept 5 parameters. Below is the main query:
Code Block
@LocID as nvarchar(10)
SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID
The changes I need to make to this are confusing me because I've had to do something like this before. There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information. The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns.
The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment. I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this. A co-worker suggested doing something like this:
Code Block
@LocID as nvarchar(10),
@Gate as nvarchar(50),
@Location as nvarchar(50),
@OffLZ as nvarchar(50),
@OnLZ as nvarchar(50)
SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate)
AND (dbo.ProfileNames.Location = @LocID)
AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location)
AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ)
AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ)
At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project). If anyone can help, I'd really appreciate it, thanks.
View 3 Replies
View Related
Dec 5, 2007
Hello,
I'm using
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
to extract data from AS400 and copy them to SQLServer2005. It's my first experience with exptracting data from AS400.
I need to pass in SSIS a parameter to the extract query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > ?
I'm naming the parameter in the mapping as 1 but geetting an error
[as400 [1]] Error: The SQL command requires a parameter named ""00001"", which is not found in the parameter mapping.
I have tried 00001, '00001', ("00001" is not accepted from SSIS) but getting still this error.
A query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > '1071205'
works OK.
Can anybody help me ?
thanks in advance
View 7 Replies
View Related
Aug 2, 2007
Hi Gurus,
I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.
In the 'Use Results of an SQL Query' pane of Lookup Transform:
Code SnippetEXEC GetMonthlyDataExtract 4, 2007
( I am passing month and year values) this works ok.
But when I chage to
Code SnippetEXEC GetMonthlyDataExtract ?, ?
It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.
Also I am interested to map parameters to variables not to input columns.
If mention if that is not possible or any other alternative.
Your help will be appreciated.
Thanks,
Paraclete
View 3 Replies
View Related
May 9, 2008
When I retrieve data using an OLE DB Source I can create a SQL query and pass parameters to filter the data I get back. I'd like to do the same thing with the Lookup Transform but the parameters button isn't there. Am I missing something or do I have to use some special text format to insert my parameters into the query?
Thanks in advance.
View 5 Replies
View Related
Sep 25, 2015
I need to filter my select statement with 2 parameters, each of which defaults to the "All" member. Some of the members may have spaces in the name.
So I need to handle something like these:
FROM MySalesCube
WHERE ( ... expression here ...)
The expression above needs to resolve to a set using both parameters
STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")
Running the query in SSMS, Im using something like this to test
WHERE ( [SalesPerson].[SalesPerson].&[17] , [Department].[Department].[All] )
I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.
[update]
Here is what I have now:
WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"] , [Department].[Department].[" + @pDept + "])") ) )
If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error
View 2 Replies
View Related
Apr 2, 2008
Hello All,
I have 2 MultiValued Parameters in my reports, our of which first is dependent on the other.
Eg. I have a SubProductGroup Parameter and depending upon the value selected in SubProductGroup, the other parameter which is Product gets filled in combo box.
Both these parameters are MultiValued and I am having "Select ALL" option in both the combo box.
Now, whenever you select any SubProductGroup values, the Dataset for Product gets executed to fill the Product combo box.
The MultiValued Parameters works using the "IN" Clause. All those products are retrieved which are "IN" the selected SubProductGroup.
Whenever user selects "Select All" option for SubProductGroup, there is no need for this "IN" clause, because we actually need all the records for Product.
Similarly, I have the Main Dataset which retrieves the Sales data for the Products selected from the Combo Box. So, the same question arises there also.
Right now I am using STRTOSET( ) and Join( ) function and this is working fine for me.
But when User Selects "Select All" in SubProductGroup, it takes a long time to fill the combo box for Product as well as when user selects "Select All" for Product, it takes a long time for the actual Dataset to retrieve records. But its working fine.
In order to improve the Performance, I am trying to eliminate this IN clause when "Select All" is selected.
How can I do this ?
Any Inputs ?
Thanks & Regards,
Kapadia Shalin P.
View 4 Replies
View Related
Nov 21, 2007
How do I create value from the dropdown list of each parameter within the "Define Query Parameters" box, which appears when I run a query/dataset from the "Data" tab of the SSRS Report Designer ?
It usually list all the "Parameter Name"s I defined, and has another column for "Parameter Value", but it always shows only <Null> or <Blank> even though I have assigned the report parameters with data (from a Query, not hard coded).
The parameter values work fine when in "Preview" mode, but it would be of convenience to have them during design time.
Thanks in advance.
View 7 Replies
View Related
Feb 27, 2008
Hello,
I would like to know if it is possible to make a MDX query on a cube with parameters in Management Studio and if yes, how to define the parameters?
In fact, I have a quite big query which of course does not work and in Reporting Services, I get an error message which is not very clear :
Cannot set the command text for data set "Cube"
Error during processing of the CommandText expression of dataset "Cube"
So I would like to test my query in SSMS and correct directly.
Edit :
Forget the query but my question concerning the parameters definition in SSMS is still asked.
Thanks
View 5 Replies
View Related
