<br><br>I obtain
table names from a database and pass them to a dropdownlist. Based on
user selection, I want to pass each table name to a query.Here is an
extract from my code:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="select * from @dDTable">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="dDTable" PropertyName="SelectedValue" DefaultValue="product"
Direction="InputOutput" Size="15" Type="String" />
</SelectParameters>
</asp:SqlDataSource>I keep getting this error: Must declare
the table variable "@dDTable".Please does anyone knows the best way to
go about this?
I have built a report using a Cube (and not a relational database).
I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.
Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.
Hello, I have a report in which I would like to give the user the ability to select how a parameter is filetered. Option 1 would be to filter by a range of value ie: WHERE member.age BETWEEN (@Start) AND (@End) -say everyone between the ages of 50 through 80 Option 2 would be to filter by specific but multiple values ie: WHERE member.age IN (@Age) -say anyone who is 25, 50 and 75. How can this be done? Thanks
I am using two drop downs, like so: <asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList> <asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList> I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource> <asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource> Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
consider a stored procedure with a parameter @OrderID, i want to perform the following query :select * from Orders where OrderID = @OrderIDi want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.
Hello, I am trying to use a boolean parameter to filter data in a table, but there is something I am missing.
Basically I want something like this: I have a boolean parameter, "EP", and I have a filter set on my table as:
Expression Operator Value =Fields!REFERRAL_SOURCE.Value = IIF (Parameters!EP.Value, "1297", ????)
Using the filter tab, I can't specify an expression for the 'Operator' so I was trying to work it out using either '=' or 'like'. What needs to go into the ???? in order for the referral source to be "not 1297" (i.e. the inverse of the filter)?
Or, am I completely missing an easy way to do this?
I have gridview and i want to ignor that filtering if that parameter is empty how do i write that query. my query is select * from UserMAster where UserId=@USerID and RoleId=@RoleId if @UserId is blank then i want to select all records without filtering with user Id a nd if i have passed null or anything as parameter in roleId then alsoi want all the records if it contain value then i want to do filetring is it possible to with query. or else send me some alternative so that i don't need to execute more query.
I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
Im trying to create a drop down parameter whereby if i select a certain field, a different dropdown will be filtered off only the relevant selections, is this possible.
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :) I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query. (tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server"> 2 <asp:ListItem Value="0">Open</asp:ListItem> 3 <asp:ListItem Value="1">Completed</asp:ListItem> 4 </asp:DropDownList> 5 6 7 <asp:DropDownList ID="drpdwnCompany" runat="server"> 8 <asp:ListItem Value="">All</asp:ListItem> 9 <asp:ListItem Value="cur">Cur District</asp:ListItem> 10 <asp:ListItem Value="jho">Jho District</asp:ListItem> 11 <asp:ListItem Value="sea">Sea District</asp:ListItem> 12 <asp:ListItem Value="san">Net District</asp:ListItem> 13 <asp:ListItem Value="sr">Research District</asp:ListItem> 14 </asp:DropDownList> 15 16 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>" 18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')"> 19 <SelectParameters> 20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" /> 21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" /> 22 </SelectParameters> 23 </asp:SqlDataSource> 24
Hi Friends, i have a question table and i want to filter this table using the following stored procedure but i cannot take a resultset ; (there is no error)
Stored Procedure is here:
CREATE PROCEDURE [dbo].[SP$AllQuestion] @Session nchar(10), @Class nchar(10), @Unit nchar(10) AS BEGIN SELECT L.QID, L.CLASS, L.SESSION, L.UNIT, L.CONTENT as QUESTION FROM tblQUESTION L WHERE @Session IS NULL OR L.SESSION = @Session) AND (@Unit IS NULL OR L.UNIT = @Unit AND (@Class IS NULL OR L.CLASS = @Class) AND ((@Session IS NULL AND @Unit IS NULL ) OR (L.SESSION = @Session AND L.UNIT = @Unit)) AND ((@Session IS NULL AND @Class IS NULL ) OR (L.SESSION = @Session AND L.CLASS = @Class)) AND ((@Unit IS NULL AND @Class IS NULL ) OR (L.UNIT= @Unit AND L.CLASS = @Class)) AND (L.UNIT = @Unit AND L.SESSION = @Session AND L.CLASS = @Class) END
i dont know what my fault is ; to your opinion what i have to do? thanks from now on...
Tell me please how can I make a query that updates one table using data from another but not all and not filtered by its field but filtered by a field in a linked table. I've tryed to use usual construction but it doesn't work: UPDATE arch_exm SET reg_num = t1.reg_num, dscp = t1.dscp, checktype = t1.contr_type, mark = t1.mark, contr_date = t1.d_kontr, semester = t1.semester, n_register = t1.n_regist FROM (ses_curr INNER JOIN students ON ses_curr.reg_num = students.reg_num INNER JOIN groups ON students.stgroup = groups.stgroup AND students.stgroup = groups.stgroup INNER JOIN specialties ON groups.speciality = specialties.speciality AND groups.speciality = specialties.speciality) t1 WHERE (t1.arch_store = 1) AND (t1.depart = @depart) So I need to use for update source a table is produced by joining several ones and use a filter in one of its fields but in syntax FROM <table> <name> is supported only one table but not a result of joining. Tell me please how can I describe in SQL using after FROM a result of joining with a filter.
I can't find any info on what the 'IN' operator does when filtering data. What's the syntax? I'm hoping this operator will behave similar to the IN operator in sql. I want to pass in 1 or more values that I'd like my data filtered by. If I say <field> IN <single value> it works, but if I say <field> IN <value1>,<value2> I get back nothing at all. Any claification on the use of this operator and the correct syntax for my value list would be greatly appreciated!!
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
Hi. I am working on a procedure which can filter data through three tables. First Table is User --------------------------- UserName UserID ---------------------------
Second Table is Customer --------------------------- UserID Cust_Site_Int_ID ---------------------------
Third Table is l Details --------------------------------------------------------------- Cust_Site_Int_ID Created_Date Status ----------------------------------------------------------------
I want to create a stored procedure which can filter through these tables. I will have four input fields in my application to accept parameters. The four parameters will be UserName, two date fields which specifies a range of dates and status. I want the filter to work like this, if only one parameter is provided it will use only that parameter if additional parameter is given it will use the AND logic.
I tried to do a Proc but it is not working any way here it is. So you will get a better idea of what I am trying to achieve.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= ALTER PROCEDURE [dbo].[FilterServiceTickets] -- Add the parameters for the stored procedure here @userName varchar(20)= NULL, @startDate datetime =NULL , @endDate datetime = NULL, @status int = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT SERVICE_TICKET.SERVICE_TICKET_CODE, SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_NUMBER, SERVICE_TICKET.STATUS FROM aspnet_Users INNER JOIN BuildingAddress ON aspnet_Users.UserId = BuildingAddress.UserID INNER JOIN SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID WHERE ((aspnet_Users.UserName = @userName)OR (@userName=NULL)) AND ((SERVICE_TICKET.STATUS = @status) OR (status=NULL)) AND ((SERVICE_TICKET.CREATED_DATE >=@startDate AND SERVICE_TICKET.CREATED_DATE <=@endDate ) OR (@startDate=NULL)OR (@endDate=NULL)OR(@startDate=NULL AND @endDate=NULL)) END
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView AS WITH ContactCTE AS( SELECT Col1, Col2, TenantId
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
Hello I have aspx page and I need to insert 15 rows to a table so am thinking of passing them as table to my sp but I dont know how to use table as parameter in the sql2005 so PLs help me
I am getting the following error: (even though I have table1 in db).
Server: Msg 208, Level 16, State 1, Procedure AddNewSKUsToExistingStyleTest, Line 6 Invalid object name '@table_nm'.
Any ideas on how to use table name as parameter?? Thanks.
script for sp: ----------------------------------------------------------------------------------------- CREATE Procedure AddNewSKUsToExistingStyle
@table_nm varchar (20)
as Set nocount on /* Step No. 1 */ Update site_product_xref Set Site_Product_End_dt = '12/31/09' --select sk.eb_style_id, sk.eb_sku_id, spx.Site_Product_Id from [@table_nm] c inner join Book b On (c.book_id = b.book_id) Inner Join Size_Color SZ On (SZ.EB_Style_Id = c.EB_Style_Id) Inner Join EB_SKU SK On (C.EB_Style_Id = SK.EB_Style_Id and sz.color_id = sk.color_id and sz.size_id = sk.size_id) Inner Join site_product_xref spx On (sk.eb_sku_id = spx.eb_sku_id and spx.Site_id = 1) where spx.Site_Product_start_dt < b.live_dt
I have to create a tabla with a different name each time. I have a stored procedure to do so like the following. Step 1 works fine but not step 2. The table name cannot include a parameter. How can I solve this? Any help on this will be appreciated.
CREATE PROCEDURE NewTable @UserName as varchar AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE_' + @UserName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: - CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2) AS BEGIN DECLARE @name TABLE (column list) etcPerfect: but I need to be able to have another [recursive] procedure process this table. How do I make it available to the second procedure? If I write EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced. Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'". If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure? Do I pass a cursor to it? Or is it quite impossible?Regards, Robert Barnes.
Im struggling with this, I think its probably quite simple. I have a table that looks like this:
RecordId PictureId KeywordId
111 212 313 421 522 623 725 817 932 1044
I want to be able to run a query for a distinct listing of PictureIds that have KeywordId=1 and Keyword=2. When trying a simple query like:select PictureId from PicturesKeywords where (keywordid=1 and keywordid=2) group by PictureId It obviously returns nothing. What am I doing wrong?
Hi Friends, Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function. Any help would be appreciated. Fazal.
declare @string varchar (5000) select @string='Update product Set Product_End_dt = '12/31/05' from ' + @table_nm + ' c inner join Booktable b On (c.book_id = b.book_id).............
exec (@string) -- I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help! Reddy.
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....
