Sql Help - Passing An Operator As A Parameter Maybe?
Sep 14, 2006
My data table contains a column (a foreign key) with numbers from 0 and up. (it could be null or a number. I used ISNULL to convert it.) I want to be able to select in three ways.
1) equals zero (or is null)
2) greater than zero (or is not null)
3) equal to a specific number.
How do I do this?
My application setup is Presentation Layer, BLL, DAL and Sql Server.
I don't want to create another Query because this is only part of a larger query with other parameters.
If I could just do something like @IDOperator @IDValue in the Filter of the Sql it would be great.
I'm trying to get string data from a database using this bit of code: string SQL1 = "SELECT Title, Type, MID FROM Movies WHERE Title LIKE '%@Title%'"; SqlCommand MyCommand1 = new SqlCommand(SQL1, Conn1); MyCommand1.Parameters.AddWithValue("@Title", TXTTitle.Text); It doesn't seem to be returning anything when I try this. I've never thought to try and parameterize a LIKE before and I'm not sure on the proper syntax. Thanks in advance!
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
I have a view created in Sql database by name vw_CETARIS_Suppliers.
CWS_fn_MVParam: This function takes a parameter for cities (xx,yy,zz) and returns a table with the values as individual cities. - If I execute this from t-sql, it is working perfectly. Also I created a dataset with the query SELECT Param FROM CWS_fn_MVParam(@pCity,', ' ) This is executing properly and the dataset is getting all the vlaues from the concatenated parameter (for example 'xx,yy,zz' is returning a table of values xx yy zz.
My SRS report has a multi-value parameter called @pCity. The following query is given under the data tab in SRS report.
ELSE begin SELECT CetarisSupplierCode, CetarisSupplierName, CetarisAddress1, CetarisAddress2, CetarisCity, CetarisProvState, CetarisPostalZip, CetarisCountry, CetarisCurrency, CetarisCompany FROM vw_CETARIS_Suppliers WHERE CetarisCity IN (SELECT Param FROM CWS_fn_MVParam(@pCity,', ' )) end
When I refresh the dataset and run for a All,single city or multiple cities, it returns data. No problem.
When I preview the report for single city and 'All' value it returns data perfectly. But when I select two or more cities I am getting an error. An expression of non-boolean type is specified in a context where a condition is expected, near ',' Incorrect syntax near the keyword ELSE
Can anybody help me please?. I tried different ways but was not successful in finding out the problem. Thank you for all your help in advance,
Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks
I have couple of paramters with operator 'range(inclusive)' in my report(Agains datacube), and I always get failure when I choose value with them. here's a example( 3 such parameters on my report).
...
From date To Date
...
From age To Age
...
From Salary To Salary
@salary is the last one.
when i choose a value for 'From Salary' or 'To Salary' from the dropdown list, it runs correctly. But if I choose value for 'from age' or 'To age' or 'From Date' or 'To Date', it fails due to
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DimTimesheetTimesheetNumber'. (rsErrorExecutingCommand)
Range operator ( : ) operands have different levels; they must be the same.
BTW, all the default values for these parameters are 'all', since it allows only 'all' to select when I set up the parameters, and I tried to change the default value, but it caused errors about the parameter dependency.
In VB.NET this doesn't work either: dim s as string = "'cn01','cn02','cn03','cn04'" cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000)) cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done: 1) Use SQL task to include "Select Count(*) from TableA" 2) In parameter mapping tab of SQL Task, define the following Parameter name : Table::Count Direction: Input Data Type: Numeric Parameter Name : @Count 3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out] 4) The constraints has been set as Evaluation Operation : Expression and Constraints Value: Success Expression : @Count >"50000"
In SSIS tab > Variable I have set the following Variable Name : Count DataType : Int32
Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
I don't understand how to pass a parameter to a dts package from another. In SQL 2000 you can map a variable with a variable of the package you will call.
As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report...
Hello, Can someone kindly point out what is wrong with the following code file. I'm trying to: - fill a dropdown from a db on page load (this works!) - when user selects from the list and hits a button, pass the dropdown value to a second query - use the second query to make another call to the db and fill a data grid
In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.
Thanks
PS comments about my coding approach are welcome - I'm new to aspx...
<%@ Language="VBScript" Debug="true"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.data.SqlClient" %> <html> <head> <script language="vb" runat="server"> Dim dbConnection As SqlConnection Dim ds_Teams,ds_Agents As DataSet Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter
Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"
Dim sql_select_agents = "" & _ "SELECT last_name + ', ' + first_name AS name, agent_no " & _ "FROM dbo.users " & _ "WHERE (team_no = @Team) AND (NOT (agent_no = '1029')) " & _ " AND (NOT (last_name IS NULL)) " & _ " AND (NOT (first_name IS NULL)) " & _ " AND (NOT (first_name = 'FirstName')) " & _ "ORDER BY last_name"
Dim teamList = "teamList" Dim agentList = "agentList"
Sub Page_Load(Sender As Object, E As EventArgs) if not (IsPostBack) ds_Teams = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection) sqlCmd_Teams.Fill(ds_Teams, teamList) dbConnection.close()
dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView dropdownlist_Teams.DataBind() end if End Sub
sub Get_Agents(Sender As Object, E As EventArgs) ds_Agents = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)
Interesting problem, selecting and updating (with reference to a Form View) works great. Whenever I try to delete I get this error:Must declare the variable '@template_id'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@template_id'. Here is my code:<asp:SqlDataSource ID="SqlTemplateDS" runat="server"SelectCommand="SELECT [template_id], [client_id], [language], [label], [email_default], [email_template], [event_template] FROM [CMT] ORDER BY [template_id]"DeleteCommand="DELETE FROM CMT WHERE (template_id = @template_id)" ConnectionString="<%$ ConnectionStrings:BrandMSConnectionString %>"> </asp:SqlDataSource><asp:GridView ID="grdTemplates" runat="server" AutoGenerateColumns="False" DataKeyNames="template_id" DataSourceID="SqlTemplateDS" Font-Names="Verdana" Font-Size="Small" ForeColor="Black" Width="100%" OnSelectedIndexChanged="grdTemplates_SelectedIndexChanged" CellPadding="2"> <Columns> <asp:BoundField DataField="template_id" HeaderText="ID" ReadOnly="True" SortExpression="template_id" /><asp:BoundField DataField="client_id" HeaderText="Client" SortExpression="client_id" /><asp:BoundField DataField="language" HeaderText="Language" SortExpression="language" /><asp:BoundField DataField="label" HeaderText="Label" SortExpression="label" /><asp:CheckBoxField DataField="email_default" HeaderText="Default" SortExpression="email_default" /><asp:CheckBoxField DataField="email_template" HeaderText="Email" SortExpression="email_template" /><asp:CheckBoxField DataField="event_template" HeaderText="Event" SortExpression="event_template" /> <asp:TemplateField HeaderText="Options"><ItemTemplate> <asp:LinkButton ID="btnModify" CommandName="Select" runat="server" ForeColor="Blue">Edit</asp:LinkButton> | <asp:LinkButton ID="btnDelete" CommandName="Delete" runat="server" ForeColor="Blue">Delete</asp:LinkButton></ItemTemplate></asp:TemplateField> </Columns><HeaderStyle BackColor="Khaki" /><AlternatingRowStyle BackColor="Beige" /></asp:GridView>Any ideas?Chris
possible to pass in a query as a parameter to a stored proc?
Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.
Code:
CREATE PROCEDURE [dbo].[s_DeletePeople]
@PeopleID int /* single id to be deleted, tried just passing * in a query that resembled a string and using it * but it didn't work either. */
AS
Deletefrom tProjectManager whereManager_ID in (@PeopleID)
Deletefrom tMerchandiser whereID in (@PeopleID)
Deletefrom tProjectCall wheremerchandiser_id in (@PeopleID)
Delete from tManager whereID in (@PeopleID)
Deletefrom tDistrictManager whereID in (@PeopleID)
I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....
I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name. MS SQL is not accepting it because it is asking me to declare it... can someone help me??
Thankyou
"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0) AS BEGIN DECLARE @TB varchar(30) SET @TB = @tablename DECLARE @SR decimal(9,0)
I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.
eg: procedurename @fieldvalue varchar(100) as begin ...
WHERE fieldname IN(@fieldvalue)
while executing the procedure how to pass the value... or procedure itself has problem...? Help me...
How do i pass parameter to trigger.iam using trigger to delete from multiple tables here is the code
Create trigger DeletePartyName on PartyMaster_Gen (@PartyName varchar(50))
for delete
as DELETE FROM PartyMaster_Address WHERE PartyName=@PartyName DELETE FROM PartyMaster_DEPB WHERE Party_Name=@PartyName DELETE FROM PartyMaster_Gen WHERE Party_Name=@PartyName
I want to pass @partyname parameter to this query. how do i do it.
Hi I am getting error while passing parameter in SSIS package
I am writing following query in sql command
select * from Mytable where empid in (select empid from testtable where empid = ?)
Error: Parameter 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.
I have to pass 3parameters in function, @begindate,@enddate and @group_type.. but in @group_type should be - state,zipcode and country from salestable
inview :vwstzipcont create view vwstzipcont as select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3 left outer join (select distinct stype,docdate from salesdisttable) s2 on s2.stype = s3.stype where s2.soptype = 2 go
RETURNS TABLE AS RETURN (Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from ( select distinct vs.docdate,vs.itemnmbr,
p.index From Pubs P
inner join vwstzipcont vs
on vs.index = p.index
Where (vs.docdate between @begindate and @enddate) and @group_type ) as t
order by t.itemnmbr,t.docdate
end
how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country can anyone tell me? what condition should be in where clause for this variable?
say I have a table with field1,field2,field3,....and have a SP with a parameter @myfieldis there a way I can doSelect @myfield from Table1thanks for your help
I can't figure out how to pass values from report parameters to a variable in custom code. I have a Report Parameter called Parm1 of type string. It contains the following:
Label: Value: Labell1 Value1 Label2 Value2
In custom code I have declared a variable thus: Public Dim ParmValue As string = Parameters!Parm1.Value
What I want is this: When a user select a report, they will have to choose a parameter from a dropdown-list (created automatically when creating a paramete). The chosen parameter will be passed to the variable ParmValue which I use as one of the parametes in a text-box like this: =code.InstanceName.MethodName(ParmValue)
InstanceName is the instance of a class from a dll I reference. It works fine if I hard-code the paramter like this: Public Dim ParmValue As string = "SomeString" instead of: Public Dim ParmValue As string = Parameters!Parm1.Value
The error I get is this: [rsCompilerErrorInCode] There is an error on line 0 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
I don't know if I'm at all on the right track here. Can someone tell me what I'm doing wrong?
Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses
I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works
The cutdown version off the query is...
Select Top @Param_TopNo SaleDate, Consultant, Productname
From tblSales
where product = 2000
Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.
Parameter Mapping Variable Name Direction Data Type Parameter Name User::Variable2 Input LONG 0
When executing the SSIS Package I get the Following Error
SSIS package "Test.dtsx" starting.
Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query "Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
