Is There Any Way To Use A Single Parameter For An IN Clause?
Jan 18, 2008
I have an application that needs user parameters for some reports, and it allows them to enter any amount of said parameter, so the query is built like this:
"Select ... Where ParameterColumn IN (" + txtParameters.Text + ")";
the parameters textbox only allows numbers and commas to be on the safe side of SQL injection. now, due to this the query has to be built dynamically in the code behind page. is there any way to use a single parameter to add all of those values? and set the parameter to the text in the control during design mode to the datasource control?
something like:
Select ... Where ParameterColumn IN (@Parameters)
<asp:ControlParameter ControlID="txtParameters" Name="Parameters" PropertyName="Text " />
whenever I try that I get something along the lines of "Error converting nvarchar '1,2,3' to datatype int"
Thanks!
View 3 Replies
ADVERTISEMENT
Feb 4, 2008
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
View 4 Replies
View Related
Jan 4, 2008
Hello,
I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value).
I'm storing id with comma (,) separator. Below is my asp.net code:
string sId=string.Empty;
for(int a=0;a<Id.Count;a++)
{
Id += Id;
if(a!=Id.Count-1)
sId +=",";
}
In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar.
Plz help me create Stored Procedure in SQL 2005 based on above scenario.
Thanks
View 7 Replies
View Related
Jun 25, 2004
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
View 2 Replies
View Related
Oct 20, 2006
I am trying to do a 'WHERE IN' clause by taking the value of a paremeter and using it as the value in the WHERE IN clause such as: DECLARE @parm NVARCHAR(1000) = 'IBM, MOT, MSFT'SELECT * FROM SomeStockTable WHERE TickerSymbol IN (@parm)I would like to avoid using an EXEC statement and string-building this whole query, since that is ugle and hard to maintain.ANy ideas? TYVM
View 1 Replies
View Related
Aug 22, 2007
I have posted this question in the MSDN SSRS forum, but have had no luck. I'm hoping someone can give me a definite yes or no if this is possible...
I want to be able to define a parameter for a report that will represent the entire where clause.
E.g. the report's dataset will be:
SELECT name, phone FROM person WHERE @whereclause
Has anyone accomplished this?
View 4 Replies
View Related
Apr 13, 2008
I have the following query:
Code Snippet
create PROCEDURE [dbo].[usp_GetSearchedEvents]
(
@SpecialityID varchar(400),
@MinistryID varchar(400)
)
AS
SELECT Distinct Events2.EventName
FROM Events2
Where ((Events2.specialityID in (@SpecialityID)) or (Events2.MinistryID in (@MinistryID)))
Now if I fill in the the variables manually, i.e, in ('1','2') it returns data. But if I execute it and enter the values of the variables as so: '1',2' it doesnt return any results. How do I accomplish this? Thanks for any input.
View 14 Replies
View Related
Jun 19, 2000
I'm trying to write an SP which takes a string parameter like '1,3,6,9' and then uses it in an IN clause of a SELECT statement to return a recordset, along the lines of:
SELECT
custname
FROM
mytable
WHERE theid IN @myparameter
...any ideas how to get this kind of thing to work. In the example, theid is a numeric value. What it needs to do, I guess, is do a string replacement of the parameter value, but the SP naturally enough attempts to compare the numeric theid value with the string @myparameter value and fails. It works OK if there's only one value in the string, because the server auto-converts the paremeter into a numeric, but if there are 2 or more values in the string then the server obviously can't do the conversion. How can I make this work?
View 1 Replies
View Related
Sep 29, 2005
HI
I want to Run a Dynamic SQL in which i need to pass IN parameters.
DECLARE @QUERY varchar(100)
DECLARE @Paremeter varchar(10)
SET @Parameter = '1,2'
SET @QUERY='SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (' + @parameter + ')'
But this causes an error that cant convert varchar string to int. Please dont suggest to avoid Dynamic Sql. I have to use it.
View 1 Replies
View Related
Jul 19, 2009
I have an Epicor ERP system that enables writing 'queries' in its internal format, that can see Views in the MSSQL database. Very useful, as I can write view definitions with much more flexibility than the ERP tools. As far as I can tell the ERP DB interface can't call SQL procedures.
What I want to to is to pass a value from the ERP engine that the View can use to restrict record selection. The View query produces a field that describes how far down into a nested parts list structure it has got, so the data is not in the database, it is calculated by the query.
A user may wish to restrict the depth of descent to, say 3 levels. I can filter the results set returned by the view in today's solution to only show levels up to 3, but the View still processes all levels (some 12 million records possible return) before the filtering can be applied. SLOW...
So I want to pass a parameter (if that is the right jargon) into the View to limit how much work it will do.
View 2 Replies
View Related
Sep 20, 2014
In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?
So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?
I do not want to use dynamic SQL string concatenation...
View 5 Replies
View Related
Sep 15, 2005
Hi,
I've tried that but it does not work:
SELECT Field FROM Table WHERE Field LIKE @parameter
thanks
View 4 Replies
View Related
Aug 21, 2007
Hello
Is it possible to construct a dataset where the parameter of the report is the where clause?
I have tried setting the dataset of the report to be a variable to execute, but any time I introduce the parameter into the dataset, the report will not run.
View 12 Replies
View Related
Dec 18, 2006
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
View 1 Replies
View Related
Jul 19, 2007
Table-Account0
column EAIndex �BillerCode�ChainCode�PostDate�PostAmount�BalanceDate�BillBegDate�BillEndDate�BillCount�ChAmount
Table-Biller
CompanyCode�BilerCode�BillerName�UniformNo�BusCode�AcctNo�BillerInfoList
Table-DataBackup
column CompanyCode�Keepmonth
in Table-Account0 , I use this sql query to retrieve data by column PostDate-3
Select * From ZT_EAccount0 Where isNumeric(PostDate) = 1 And PostDate < Convert(varchar(10),DateAdd(Month,-3,GetDate()),112)
now , I don't won't to lock the value "-3" , the subtract value will depend on the value of colum keepmonth from table DataBackup
can you please tell me how to do ?
thank you very much
View 4 Replies
View Related
Dec 9, 2003
This works:
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005'))
This doesn't:
WHERE
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears))
@strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
View 4 Replies
View Related
Aug 1, 2005
What i want is to add by parameter a Where clause and i can not find how to do it!CREATE PROCEDURE [ProcNavigate]( @id as int, @whereClause as char(100))ASSelect field1, field2 from table1 Where fieldId = @id /*and @WhereClause */GOany suggestion?
View 1 Replies
View Related
Mar 20, 2014
I want to bring a string parameter to where clause like this:
select .. from ...
Where Code IN (@Code)
When @Code is 'ABC' it works but when @code is 'ABC, XYZ' it won't work. I know for IN statement we should use IN ('ABC', 'XYZ') and I tried make @code = '''ABC'', ''XYZ''' still not working. I tried
Print '''ABC'', ''XYZ'''
I got 'ABC', 'XYZ' but why it does not work in @Code?
View 4 Replies
View Related
Jan 24, 2008
Hi,
I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.
Below is my procedure..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),
as
BEGIN
SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END
The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.
I am not able to figure out why.
Any help in this will be appriciated.
Regards,
Salim.
View 16 Replies
View Related
Jul 30, 2007
Hi all,
I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.
Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.
DECLARE @strSearchType varchar(2)
DECLARE @strSearchValue varchar(15)
DECLARE @SearchField varchar(15)
set @strSearchType = 'PN'
set @strSearchValue = '1234567'
IF @strSearchType = 'PN'
begin
set @SearchField = 'PART_NUMBER'
end
ELSE
begin
set @SearchField = 'INTERNAL_SKU'
end
SELECT
SKU as 'SKU',
PART_NUMBER as 'PartNumber',
DESCRIPTION as 'Description'
FROM SKUs
WHERE @SearchField = @strSearchValue
FOR XML PATH('SKU'), ROOT('Response')
Thanks,
ST
View 4 Replies
View Related
Mar 12, 2014
I am looking for a simple way to do multiple values in one single parameter in my simple Stored Procedures. Let's say for example I have a column called RoomNumber and the value data type is INT. Here is my Stored Procedures:
CREATE PROC ROOMVACANCY
@RoomNumber int,
SELECT vacancy, roomnumber
FROM hoteldb
WHERE Vacancy IN (@RoomNumber)
END
The value for roomnumber has 100 records. I want to be able to select for more than selection when I execute this stored procedures. How do I do that in the simple way?
View 10 Replies
View Related
Nov 25, 2007
Hi everyone,I am passing parameter @Market, which has 3 values(okc,nfl,La). In BM_Markets table there is a column bm_name, it has cities.
Oklahoma City, tulsa cities comes under OKC Market,
'daytona','jacksonville','melbourne','north florida','orlando','west florida' cities comes under NFL Market and
'atlanta','dallas','houston cellular','pac los angeles' cities comes under LA Market.
By default, All Markets should Select. IF Client select only two markets, then report should display only two markets data.
I wrote query like this,
select transaction_status_desc,count(transaction_id)
from psc
where (creation_date between @startdate and @enddate) and @Market IN
(select distinct
case
when bm_name in ('oklahoma city','tulsa') then 'okc'
when bm_name in ('daytona','jacksonville','melbourne','north florida','orlando','west florida') then 'nfl'
when bm_name in ('atlanta','dallas','houston cellular','pac los angeles') then 'others'
end
from bm_markets
)
group by transaction_status_desc
In Report Parameters Properties window, i selected Multi-Value check box, but when i preview the report, it is giving following error
"An expression of non-boolean type specified in a context where a condition is expected,near','
Incorrect syntax near the keyword 'group'."
Could you please give me some advice, how to write this query for multivalue parameters.
Thanks
Santosh
View 4 Replies
View Related
Jan 13, 2007
Here is my procedure:
ALTER PROCEDURE dbo.SelectMeds
@RX int
AS
SELECT RX FROM tblMeds WHERE RX= @RX
RETURN
but it return no rows, how do I fix this?
View 4 Replies
View Related
Feb 1, 2008
Hello,
I want to execute a sproc where the query statement goes something along these lines:
SELECT * FROM myTable WHERE @aVarCharVariable
@aVarCharVariable contains column names and their possible values
How do I achieve this?
Cheers!
/Eskil
View 7 Replies
View Related
Sep 13, 2004
Hi,
I use a DataGrid to show the data, and I want it has a sorting and Paging function,
so I use dataset to collect the data from middle tier function and stored procedure.
I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
CREATE Procedure Ruying_AutoSearch10
(
@Make varchar(50),
@Model varchar(50) = NULL,
@Condition varchar(20) = NULL,
@Miles float,
@Zipcode varchar(5),
@SortExpr varchar(100)
)
AS
DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND a.Model = IsNull(@Model,a.Model)
AND a.Condition = IsNull(@Condition, a.Condition)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
ORDER BY @SortExpr
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO
but I got the error as "variables are only allowed when ordering by an expression referenceing
a column name". How I fix this error? Please help.
Thanks.
Lin
View 9 Replies
View Related
Jul 30, 2007
Hi All :)
I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.
I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.
If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.
I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:
KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)
and
'KA1001', 'KA1002'
Both fail :(
Any ideas?
Regards
Xo
View 11 Replies
View Related
Jun 3, 2008
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END
View 3 Replies
View Related
May 5, 2008
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
My simple QueryA
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
My simple QueryB
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
I know it can be done using dynamic SQL construction. But any other ways ?
Also can someone also give in the solution in PL/SQL.
Thanks a bunch.
Jaffery.
View 7 Replies
View Related
Aug 29, 2007
Hello
I created a table adapter with the following query
select * from building where buildingname in (@param1)
I used the query builder to the test the query and it works ok if I pass a single value
ex: name: @param1 value: Building1
but when I use the value: Building1, Building2 then it doesn't return anything
If i do a direct sql: select * from building where buildingname in ('Buiding1','Buiding2') returns many rows.
Now I have a class that has the following function<System.ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Select, False)> _
Public Function getBuilding(ByVal building As String) As myDataSet.buildingDataTableReturn adapter.getBuilding(building)
End Function
When I call this function I try to pass the building value sorta like
dim building as string = 'Building1,Building2'
and it doesn't work. I tried a whole bunch of variations.
How are you supposed to format the parameter string?
Thanks
Peter
View 4 Replies
View Related
Mar 6, 2014
I am writting the T-SQL using some parameters. One of the parameter is to display the ZoneID which has about 2100 records. So I declare the parameter (called it @ZoneID varchar(max)).
Now, how do I make all the value available for multiple selection? For example, if I execute the query, I can put the ZoneID (for example:) YH09, TR45, TTS0.
View 2 Replies
View Related
Mar 9, 2014
How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):
SELECT *
FROM TestParamOne
Here is the output:
ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR
Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.
CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO
View 7 Replies
View Related
Dec 13, 2007
it looks like a multi value parameter is the only kind of parameter that displays a checkbox.
A single checkbox for yes/no parameters are more intuitive to my users than 2 choices from a query.
I tried to set the multi value attribute on such a param but at run time get errors from a couple of places 1) expressions that reference such a parameter, seemingly confused about the value field being allowed to be either an object or an integer 2) unchecking the checkbox causes a prompt demanding that a value be set.
Does anybody have a trick or see what I am missing in trying to make a parameter act like a single yes/no checkbox in just one line of a dropdown parameter list?
View 3 Replies
View Related
Sep 21, 2004
Is it possible to pass a report parameter that is defined as a string to the following SQL statement that is using an "IN" clause ?
WHERE (ANALYST.User_Bemsid IN (@Report_Parameter_Bemsid))
If I pass a single value (I.E. A) it works okay, but once I try to pass multiple values (I.E. A,B or 'A','B') it returns no data.
Using Crystal reports I can pass multiple values via a report prompt into the SQL "IN" clause and seems that SQL Reporting Services should also have this feature. What do I need to do to get it working ?
Thanks for any help...
View 4 Replies
View Related
