MS SQL Server :: Building Dynamic Query Based On Dropdownlist Contents

Building Dynamic SQL Query Strings

Jan 17, 2008

Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET. Thanks for any help!

View 14 Replies View Related

Query Building Based On User Selections

Sep 22, 2006

I currently have a form that has different options in a list box such as:Status Codes

View 3 Replies View Related

Problem In Saving Page Contents I.e; Textbox And Dropdownlist Values Please Help

Feb 29, 2008

Hi all,please have a look of code i am unable to perform save operation onthe asp.net web page.I ahve written a stored procedure. the same code works if all aretextboxes, but some of textbox replaced with dropdownlist box thenthis save operation doesn't occurs. please let me know where is themistake in coding .vb.net code :- Protected Sub btnSave_Click(ByVal sender As Object, ByVal e AsEventArgs) Dim employmentID As Integer =Request.QueryString("employmentID") Dim resourceID As Integer = Request.QueryString("resourceID") Dim projectID As Integer = Request.QueryString("ProjectID") Dim dbconsave As SqlConnection dbconsave = New SqlConnection(HRISDBConnectionString) Dim dbcomsave As New SqlCommand("sp_save_NewHireEmailnotify",dbconsave) dbcomsave.CommandType = CommandType.StoredProcedure dbcomsave.Parameters.Add(New SqlParameter("employmentID",SqlDbType.Int)) dbcomsave.Parameters("employmentID").Value = employmentID dbcomsave.Parameters.Add(New SqlParameter("resourceID",SqlDbType.Int)) dbcomsave.Parameters("resourceID").Value = resourceID dbcomsave.Parameters.Add(New SqlParameter("ProjectID",SqlDbType.Int)) dbcomsave.Parameters("ProjectID").Value = projectID dbconsave.Open() dbcomsave.Parameters.Add("@PreferredFirstName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredFirstName").Value =txtPreferredFirstName.Text.ToString() dbcomsave.Parameters.Add("@PreferredLastName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredLastName").Value =txtPreferredLastName.Text.ToString() dbcomsave.Parameters.Add("@CellPhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@CellPhone").Value =txtCellPhone.Text.ToString() dbcomsave.Parameters.Add("@HomePhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomePhone").Value =txtHomePhone.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine1",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine1").Value =txtHomeAddressLine1.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine2",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine2").Value =txtHomeAddressLine2.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressState",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressState").Value =txtHomeAddressState.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCity",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCity").Value =txtHomeAddressCity.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressZIP", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressZIP").Value =txtHomeAddressZIP.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCountry",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCountry").Value =txtHomeAddressCountry.Text.ToString() dbcomsave.Parameters.Add("@ArrangementType",SqlDbType.VarChar) dbcomsave.Parameters.Item("@ArrangementType").Value =ddlArrangementType.SelectedItem.ToString() dbcomsave.Parameters.Add("@PracticeGroup", SqlDbType.VarChar) dbcomsave.Parameters.Item("@PracticeGroup").Value =ddlPracticeGroup.SelectedItem.ToString() dbcomsave.Parameters.AddWithValue("@EquipmentNeeds",txtEquipmentNeeds.Text.ToString()) Try dbcomsave.ExecuteNonQuery() lblMessage.Text = "Record saved successfully" Catch ex As Exception End Try dbconsave.Close() End Sub==============================================================================.aspx code :-<table> <tr> <td > <asp:LabelID="lblPreferredFirstName" runat="server" Text="Name(Preferred FirstLast) :" ForeColor="Blue"></asp:Label> </td> <td <asp:TextBoxID="txtPreferredFirstName" runat="server" Text="" BorderStyle="None"></asp:TextBox>   <asp:TextBoxID="txtPreferredLastName" runat="server" Text="" BorderStyle="none" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblCellPhone"runat="server" Text="CellPhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtCellPhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblHomePhone"runat="server" Text="HomePhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtHomePhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine1" runat="server" Text="HomeAddressLine1 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine1" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine2" runat="server" Text="HomeAddressLine2 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine2" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressState" runat="server" Text="HomeAddressState :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressState" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCity" runat="server" Text="HomeAddressCity :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCity" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressZIP" runat="server" Text="HomeAddressZIP :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressZIP" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCountry" runat="server" Text="HomeAddressCountry :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCountry" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblArrangement"runat="server" Text="Arrangement :" ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlArrangementType" runat="server" Width="160px"DataSourceID="ObjectDataSourceArrangementType"DataTextField="ArrangementType" DataValueField="ArrangementType"OnDataBound="ddlArrangementType_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtArrangement" runat="server" Text="" ></asp:TextBox>--%> </td> </tr> <tr> <td > <asp:LabelID="lblPracticeGroup" runat="server" Text="Practice Group :"ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlPracticeGroup" runat="server" Width="160px"DataSourceID="ObjectDataSourcePracticeGroup"DataTextField="PracticeGroup" DataValueField="PracticeGroup"OnDataBound="ddlPracticeGroup_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtPracticeGroup" runat="server" Text="" ></asp:TextBox> --%> </td> </tr></table>please help me to solve the issue.Thanks in advance

View 3 Replies View Related

Building A Dynamic Query Into A Stored Procedure

Apr 19, 2008

Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;

Code:

ALTER PROCEDURE [dbo].[stream_UserFind]

(

@userName varchar(100),

@subCategoryID INT,

@regionID INT

)
AS

declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'

if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '

exec sp_executesql @StaticStr

)

View 2 Replies View Related

Declare Cursor Based On Dynamic Query

Sep 18, 2006

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

This code is not working.

please suggest

Nitin

View 12 Replies View Related

Building A Dynamic Stored Procedure

Mar 30, 2005

Hi

I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success

For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)

My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),

@smoker tinyint),
@keyword
varchar(250))

AS

DECLARE @SQL Varchar
(4000)

SELECT @SQL =   'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM         dbo.user_accounts INNER
JOIN

dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE     (dbo.user_accounts.profileComplete =
1)'

IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'

IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'

IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'

IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'

IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'

EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need

Am I Missing something glaringly obvious?
Any help or advice gratefully received

Thanks

View 3 Replies View Related

Building Dynamic SQL In Stored Procs

Oct 24, 2000

We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?

' This is some VB code that shows how the query differs based on a parameter.
If Me![Sorting] = 1 Then
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table] __
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;"
Else
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table]__
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));"
End If

This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.

CREATE PROCEDURE [procDVDProjectsList]
@SortBy as bit,
@ActiveOnly as bit,
@SysFilter as integer
AS
SELECT
CASE @SortBy
/* Display Title first */
WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date]
/* Display Number first */
WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date]
END
FROM
[DVD_Projects_Table]
WHERE
CASE @SysFilter
/* List all */
WHEN 0 THEN
/* List only NTSC */
WHEN 1 THEN [DVD_Projects_Table].[System] = "525"
/* List only PAL */
WHEN 2 THEN [DVD_Projects_Table].[System] = "625"
END
AND
CASE @ActiveOnly
/* List All */
WHEN 0 THEN
/* List only active */
WHEN 1 THEN [DVD_Projects_Table].Active = True
END
ORDER BY
CASE @Sortby
/* Sort Alpha */
WHEN 0 THEN [DVD_Projects_Table].Title
/* Sort Numeric */
WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4)
END

Thanks for your help,

-Sumit Malik

View 1 Replies View Related

SQL 2012 :: Matching Column IDs Based On Field Contents

Apr 2, 2015

I have tables in my database, tblNames1, tblNames2, tblNames3, and a main addresses table (currently empty). Once I've imported the address data I need to match the addressIDs in the names tables to the Primary ID in the address table based on the values of a field CompanyName (which is common to all the tables) My issue is that I have a huge CSV file with the master address information but obviously SQL server needs to assign foreign keys so the names tables can linked to corresponding rows in address table. It's a a many to 1 relationship as their will be one address with multiple name entries. All the names are normalized so everything can be matched up...

View 9 Replies View Related

Building A Dynamic Sql Statement Into Stored Procedure

Apr 19, 2008

Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)

View 10 Replies View Related

Building Dynamic Sql In Stored Proc Issue

Oct 14, 2004

Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!

Thanks

View 2 Replies View Related

Building Dynamic Tsql Statements In A Loop

Jul 20, 2005

Hi;I would like to read a list of tables from a temp table and then do asql statement on each table name retrieved in a loop, ie:-- snip cursor loop where cursor contains a list of tablesdeclare @rec_count intset @rec_count = 0exec('select @rec_count = count(myfield) from ' + @retrievedTableName)This does not work. SQLSERVER tells me @rec_count is not declared.How can I get the @rec_count populated....or can I?Thanks in advanceSteve

View 3 Replies View Related

Analysis :: Building A Cube Based On Stored Procedure

Jul 29, 2015

I am new to SSAS. I have requirement to build a cube based on SQL Stored procedure. This Stored Procedure contains lot of temp tables, which are aggregated as measure columns.

Initially I have done creating views on each temp table, finally I created a view which calls like 15 views. when I try to execute the view, it is taking long time to execute the view.

I tried building cube on this view, when I try to deploy, even it is taking long time to deply..I have waited for 2 hours, still the deployement process going..

What I wonder is, is there any other way I can build cube based on SQL stored Procedure.

View 2 Replies View Related

Create A Table Of Contents Based On Report Items From A List Control

Apr 28, 2008

What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.

Any help is much appreciated. Thanks.

View 1 Replies View Related

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

Nov 4, 2015

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)

Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

View 2 Replies View Related

Checking Contents Of Column And Replacing Contents If First Character Is A Letter

Jun 25, 2007

Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!

View 7 Replies View Related

Select Query Issue (dropdownlist Too)

Sep 12, 2007

Hi All,
In my scenario, I am using one sqldatasource with two columns
1. Code_ID
2. Code + ' - ' + CompanyName As Display

Now I bind ddlCompany.datatextfield = Display
ddlCompany.datavaluefield = Code_ID

But the problem is when my CompanyName is NULL value, it does not show my Code too....for example
Code = 1098 CompanyName = NULL

Sql query returns me NULL for this combination....and so my dropdownlist shows blank......can anyone show me the alternative.

Thanking in advance,

View 3 Replies View Related

Power Pivot :: Building A Model Based On Multinational Model With Different Languages?

Oct 19, 2015

I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.

Productnr Productdesc Language
1 product EN
1 produkt DE

One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..

I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).

View 2 Replies View Related

Help With Building Query

Feb 14, 2007

Hello,

This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:

Two tables:

Table 1:
Orders
Some of the fields:
ID
PropID
WorkOrderNum
OrderDesc
DateCompleted

Table 2:
OrderDetail
ID
OrderID
TenantName

As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.

Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):

SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'

Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.

thanks.

View 9 Replies View Related

Query Hightest Value Contents Of Each Folder?

Jul 20, 2005

Hi,Can anyone tell me how to select the "most recent" date values from agrouped query? Consider the following:CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOTNULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]This is a simplified adjacency list. What I want to do is find the highestvalued item by date for each sub-tree. In other words, the single highestdate item when This.ID_Parent = That.ID_Parent. I think I first need togroup by ID_Parent, then select the TOP 1 from this query, but how toaggregate them so I get the TOP 1 for each ID_Parent?Thanks for any help you can give me,Robin

View 3 Replies View Related

A Query Building Question

Jan 30, 2006

Hi there,
i have a query building question and was hoping that one of you would know the answer.
Here is what i need to do :(i am using asp.net and ado.net)
I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date.
I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set.
But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date.
How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date.
thanks in advance

View 1 Replies View Related

Building A Table From SQL Query

Apr 21, 2006

I am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:==============================Column Name || Column Value-----------------||-----------------Column Name || Column Value
-----------------||-----------------
I hope I have explained myself properly. Any help would be greatly appreciated.

View 4 Replies View Related

ASP - Building A Query - Question

Feb 12, 2007

Good Afternoon!

I have a (hopefully) simple question.
I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited.

I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is.

Afterwards, we want to update the status, so I find the saved form, make my edits, and save it.

This is where my problem starts.

When I first save the letter, I have no problems whatsoever.
When I save it a second time after making my edits, I get an error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'yearID'.
/AuditFlowChart/UpdateAudit.asp, line 206

What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line

Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog)

I print out spStoredProcedure and I get the following

UpdateCorrespondence @p_idCorr = 416,
@p_dtOfLetter = '8/7/2008',
@p_dtRcvdByRespAtty = '2/19/2007',
@p_dtRcvdByAdmin = '2/23/2007',
@p_descOfEntity = 'This is a test 2009 letter. Dated 2/8/2007',
@p_ClientCode = '01074',
@p_MatterCode = '0055',
@p_dtDue = '3/15/2007',
@p_dtEffective = '4/18/2007',
@p_dtYE = '4/8/2007',
@p_respAtty = '0330',
@p_respAttyFull = 'Abelson, H. Edward',
@p_prepParalegal = 'Aldous, Anne Marie',
@p_prepAtty = 'Abromowitz, David',
@p_prepLitParalegal = 'Allegrini, J. Samuel',
@p_dtGivenToParalegal = '4/20/2007',
@p_dtGivenToAtty = '2/28/2007',
@p_dtMemoCirculated = '2/18/2007',
@p_dtRespSentToLitParalegal = '2/10/2007',
@p_dtRespSentToPrepAtty = '2/26/2007',
@p_dtSentToAccountant = '2/15/2007',
@p_memoDSid = 1111111,
@p_respLetterDSid = 2222222,
@p_sideLetterDSid = 3333333,
@p_comments = NULL,
@p_yearID = rsLetterDetail1('yearID'),
@p_opinion = 1,
@p_complete = 0,
@p_userID = 'GSSUSO1517'

When I run the above thru Query Analyzer, I get the following:

Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near 'yearID'.

So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query?

Any help would be wonderful and much appreciated.

View 2 Replies View Related

Building Query Graphically

Sep 13, 2007

Hi,

I'm using 2005 management studio express.

Can I use the graphical query builder to specify tables from more than one database?

I know I can do this in sql in the format database..table.column but in the query builder it only shows tables from the current database. Can you get around this?

Thanks

View 1 Replies View Related

Assistance Building A Query...

Jun 15, 2006

I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.

View 4 Replies View Related

Building An Alias From Query

May 15, 2008

Hi,
Does anybody know if it is possible to obtain an alias from a Query without using Dynamic SQL.

My Problem Looks like this:

CREATE TABLE Source (SourceName varchar(50), SourceAge int)

INSERT INTO Source VALUES ('Mary',41);

INSERT INTO Source VALUES ('John',22);

INSERT INTO Source VALUES ('Tom',15);

INSERT INTO Source VALUES ('Bill',55);

The Statement should look like This:

SELECT A.SourceAge AS A.SourceName FROM Source A;

The Result should look like this:

Mary John Tom Bill
----------------------------------------
41 22 15 55

Thanks in advance
Raimund

View 2 Replies View Related

How To Create INSERT Query For Contents Of Table

Oct 5, 2007

Using Sql Server 2005 Express and Management Studio I need to create
a SQL insert statement for the contents of a table (FullDocuments) so
that I can run the query on another server with that same table schema
(FullDocuments) and the contents will automatically be inserted into
the new instance of the FullDocuments table.In Management Studio
I have used "Script Table as" for the create table query. The
second instance of FullDocuments has been created on the remote
server. Now how do I generate an insert query for the contents of
FullDocuments so that the contents can be moved/inserted to the new
instance of the table?Thanks for any help provided.

View 10 Replies View Related

Perform A Query That Will Merge Contents Of Various Table

Aug 27, 2012

I am trying to perform a query in SQL that will merge the contents of various table, using search criteria to narrow down the results. All of the tables have the exact same fields, and the search criteria is the same for all of them. What I am looking for is the most efficient way to perform this. I am using SQL stored procedures, and passing the results into my code. Note that in my real-world scenario there are more than three tables.

1) Merge all the tables into one and then perform the search criteria:
SELECT a, b, c FROM
(SELECT a, b, c FROM Table1
UNION ALL
SELECT a, b, c FROM Table2
UNION ALL
SELECT a, b, c FROM Table3) AS MY_MERGE
WHERE {SEARCH CRITERIA}

2) Perform the search criteria on each table and merge the results
SELECT a, b, c FROM
(SELECT a, b, c FROM Table1 WHERE {SEARCH CRITERIA}
UNION ALL
SELECT a, b, c FROM Table2 WHERE {SEARCH CRITERIA}
UNION ALL
SELECT a, b, c FROM Table3 AS MY_MERGE WHERE {SEARCH CRITERIA})

View 3 Replies View Related

Help With Parameterized Query Building Dataset

Feb 27, 2007

I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader

public SqlDataReader GetOrgID()
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
SqlConnection con = new SqlConnection(conString);
string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;

con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return dtr;

This is the code trying to accomplish the same thing with a Dataset instead.

public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

adapter.Fill(dataset);
return dataset;

}

Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.

View 2 Replies View Related

How Do I List Contents Of File Directory In The SQL Query Analyzer

Apr 13, 2006

how do I list contents of file directory in the SQL Query Analyzer

View 1 Replies View Related

Building/Issueing A Query With A Field With A Quote In It?

Sep 8, 2006

Using a language that using A4GL to connect to the database. From within the language(COBOL) we have the ability to add a simple query. All is fine with one exception. Some of the fields might have a quote mark in it.
we have a field in particular that a lot of our clients like to put an apostrophe in. such as 0001A'06, 2'11" and so on. Now from inside our programs and using the simple query we add something like 'where ap_id = '0001A'06' which i know is an error but is there a way around this or a way to make it work?

View 3 Replies View Related

Building A Query In A Stored Proc With String

Feb 27, 2008

This should be an easy enough answer to find if I just knew what to search on!

I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says:
select * from tblNames where FirstName = 'Hannah'
or
select * from tblNames where LastName = 'Montana'
or
select * from tblNames where FirstName = 'Hannah' and LastName='Montana'

My problem is putting the single quotes around the variable value.
SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it

Thanks

View 3 Replies View Related

Assistance Requested Building View Query

Apr 4, 2008

The View SQL below takes 1:50 minutes to execute. I am looking for a way to improve processing time and keep the view select statements as simple as possible.

One part of the view that could be restructured is the lookup for the current term (TERM_TBL - bolded). When I hard code the current term my execution time drops to about 13 seconds

There are three possible values for the current term; one for each of our three acedemic careers (UGRD, CONT, & EXED). Could this information be looked up once and then each enrollment selected based upon the appropriate academic career current term value? If so, how would it be restructured? Currently, the lookup is done for every enrollment record.

There are no common fields between the NAMES table and the TERMS_TBL.

Code Snippet

SELECT A.EMPLID, A.LAST_NAME_SRCH, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME

FROM NAMES A

WHERE A.EMPLID IN (

SELECT DISTINCT B.EMPLID

FROM STDNT_ENRL B

WHERE B.INSTITUTION = 'AAAAA'

AND B.STRM >= (

SELECT DISTINCT T.STRM
FROM TERM_TBL T
WHERE T.INSTITUTION = B.INSTITUTION
AND T.ACAD_CAREER = B.ACAD_CAREER
AND T.TERM_BEGIN_DT <= GETDATE()
AND T.TERM_END_DT >= GETDATE())

AND A.EMPLID NOT IN (

SELECT DISTINCT C.EMPLID
FROM SRVC_IND_DATA C
WHERE C.INSTITUTION = 'AAAAA'
AND C.SRVC_IND_CD = 'DPL' )

AND A.NAME_TYPE = 'PRI'

AND A.EFFDT = (

SELECT MAX(D.EFFDT)

FROM NAMES D

WHERE D.EMPLID = A.EMPLID

AND D.NAME_TYPE = 'PRI'

AND D.EFFDT <= GETDATE() )

Any suggestions will be gladly accepted. Keep in mind that the new and much improved must be legal within the context of a view select statement.

Thanks,
Steve

View 3 Replies View Related