Update Statement To Include Multiple Parameterised Input
Oct 18, 2006
I want to do an update query like the following:
UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)
Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:
UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102
WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));
Help appreciated.
Many thanks.
View 5 Replies
ADVERTISEMENT
Apr 20, 2015
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update
mklopt
set
FRMDAT =
'12/31/2014'
where
JOBCOD =
'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
View 9 Replies
View Related
Sep 7, 2006
Hi.
I have an update statement that accepts
a user's text input. problem is that the string
terminates the sql update statement if there is
an apostrophe in it. so, headline='i like my brother's car"
willl terminate after "brother". "s car" is read as sql.
how is this resolved?
Code:
Dim MyCmd2 As New Data.SqlClient.SqlCommand("SET ANSI_WARNINGS OFF " & _
"UPDATE EditProfile " & _
"SET headline='"+ Me.tb_headline.Value+"', about_me='"+ Me.ta_aboutme.Value+"', edit_date='"+System.DateTime.Now.ToString+"' WHERE email_address='"+Context.User.Identity.Name.ToString+"' " & _
"SET ANSI_WARNINGS ON ", MyConn)
View 4 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Nov 14, 2000
If I want to update 2 columns at 1 time with a where clause
like
update table a
set column1 = 8 and
set column2 = 9
where id = 10
I know you can't use the and statement, what is the correct syntax?
View 1 Replies
View Related
Apr 5, 2006
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF
Any ideas?
View 1 Replies
View Related
Aug 25, 2006
Hi,
I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:
UPDATE TableX M
SET (M.column1, M.column2, M.column3)=
(SELECT T.column1, T.column2, G.column3)
FROM Table_Y T,
Table_Z G
WHERE join condition))
WHERE EXISTS (join condition for TableX)
Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.
What would be the equivalent on SQL Server?
Thanks for the help,
Laszlo M
View 4 Replies
View Related
Oct 12, 2007
Hi,
I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:
IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.
I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??
Thanks
View 7 Replies
View Related
Nov 14, 2013
i have a table named masterlist wherein the columns are :
name-----age------sex
andrew---19-------male
trisha---23------female
and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..
View 3 Replies
View Related
Jan 16, 2007
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)
'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
View 1 Replies
View Related
Nov 15, 2013
I want to update multiple column in one table using with case statement. i need query pls..
stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL
View 2 Replies
View Related
Jun 11, 2014
I have this update statement that I need to have joined by MSA and spec.
I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.
Here is my statement below. How can I change this?
UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])
View 3 Replies
View Related
Jul 23, 2005
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0
View 8 Replies
View Related
Mar 21, 2006
I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000
View 2 Replies
View Related
May 15, 2007
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName CostApple 0.63Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost2 Apple 0.634 Pear 0.89
Thanks.
View 9 Replies
View Related
Jul 17, 2013
How do you include a word with an apostrophe in an SQL in statement without getting an error msg? For example this syntax:
where provname1 IN ('Children's')
With that syntax above, I would get an error message.
View 4 Replies
View Related
Nov 15, 2007
Re: KB 940287 - Will it be in a Cumulative Update?
SYMPTOMS:
You use Service Broker in SQL Server 2005. When the initiating service starts a conversation and then sends a message, the initiating service ends the conversation immediately. However, after the target service processes the message, the endpoint on the target server is not deleted when the target service ends the conversation. Therefore, when the initiating service starts a new conversation, the number of the conversation endpoints on the target server keeps increasing.
Additionally, you receive the following error message on the initiator server:
An error occurred while receiving data: '64(The specified network name is no longer available.)'
This message could not be delivered because the conversation endpoint has already been closed.
It has several workarounds. I've not been affected by this yet, but KB says it has a hotfix that has been out since July 2007, so it is curious that it has not been put into a Cumulative Update yet, at least as far as documentation in Knowledge Base is concerned. Does anyone know any more about this?
Paul
View 1 Replies
View Related
Jun 16, 2004
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
View 1 Replies
View Related
Apr 7, 2006
Hi:
I'm trying to write a transformation script to write multiple rows from 1 input row. In SQL Server 2000 DTS I could do this by using DTSTransformStat_SkipFetch and DTSTransformStat_SkipRow. In SQL server 2005 it looks like I need to use EndOfRowset but I do not know how to use it for sure.
If this correct can someone show me a code example and if I am incorrect can someone tell me how to do this?
Thanks
View 19 Replies
View Related
Jun 2, 2008
Hi I have a table(tblA) as follows
Col1--------------col2---------col3-----col4
London------------1131---------299------Barking
Didicot-----------3451---------429------Dansdon
Barking/ASton-----1131---------345------Singleton
Vander/ADon/cam---3907---------299------derby
Null or Blank ---1131---------423------Addington
Expecting the Data should display look like below
London------------1131---------299------Barking
Didicot-----------3451---------429------Dansdon
Barking/ASton-----1131---------345------Singleton
Vander/ADon/cam---3907---------299------derby
But when user change the col2 value (1131) to 113999, this should be changed to all values where col2 is 1131. Please Help
View 2 Replies
View Related
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Oct 20, 2007
I am using the lookup transform with the following settings:
Reference table: Use results of an SQL query. The query retrieves the surrogate key and four business key columns from a dimension table which contains a few thousand rows.
Columns: business keys in the incoming data are mapped to the business keys in the reference table, and the surrogate key is looked up from the reference table.
Advanced: Enable memory restriction is OFF (and the other items on the Advanced tab are greyed out).
I assumed that this means that the lookup transform would cache all of the rows in the SQL query, and then perform the lookups against this cache. This is the behaviour that I saw when I was running the package in my local environment in the BIDS debugger.
However, a colleague was doing some profiling on our production database server, and noticed that the lookup transform is instead issuing a single SQL query for each row of input. Our production ETL server has many GBs of free RAM available (way more than enough to cache the contents of the lookup table in memory), and given that memory restriction is disabled, I don't understand why the lookup transform is behaving in this fashion. Does anyone have an explanation for this? I'm probably misunderstanding a key concept here.
Thanks,
Greg
View 8 Replies
View Related
Jul 20, 2005
Hello!We have a set of individual .SQL scripts which we would like toexecute against a MS SQL Server 2000. Is there any way to have ISQLutility (or any other means) to execute all of them without having toestablish a separate database connection for each script:isql -Ux -Py -Ss -i script1.sqlisql -Ux -Py -Ss -i script2.sqlisql -Ux -Py -Ss -i script3.sql.....isql -Ux -Py -Ss -i scriptN.sqlAll scripts are in the same location, which could be made visible toby the SQL Server itself (so either a client-based or a server-basedinvokation is ok)TIA
View 6 Replies
View Related
May 28, 2007
hi ,
i am new to this.
how should i update the user input values into sql server database?
i am using asp.net and c#
FIELDS are-
userId,
name,
description,
startTime,
endTime,
audiencePassword,
presenterPassword
i know it must be simple...but i haven't worked on this before.
Jaimin
View 3 Replies
View Related
Feb 6, 2007
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
View 6 Replies
View Related
Feb 5, 2007
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
HELP!!
View 5 Replies
View Related
Apr 24, 2007
Hi
I am a very beginner in SQL and know a little bit VB.
I created a database table with two columns, one for key and the other for data. I'd like to update the data column using excel sheet which contains the same columns, key and data.
Does anyone have a sample VB code to share.
Thanks
Al
View 3 Replies
View Related
Jun 20, 2014
How can I input parameter for Store Procedure like In ('Value1','Value2')
Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)
View 6 Replies
View Related
Jan 23, 2008
I want to write an Query for a report that has 7 parameters. I want to allow Null for these parameters but at the same time in the Query i want to put an AND condition for all the parameters. I cannot put an OR condition.
For Example;
If i have a report with 2 parameters then the script will have 2* 2 If conditions
something like:-
if( @parameter1 is not Null and @parameter is not Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1 and parameter2 = @prameter2
if( @parameter1 is not Null AND @parameter2 is Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1
If( @parameter1 is Null AND @parameter2 is Not Null)
select A, B, C
From Table 1
Where parameter2 = @parameter2
If( @parameter1 is Null AND @parameter2 is Null)
select A, B, C
From Table 1
For 7 parameter there will be way too many If conditions. Is there an better approch to write a Query for such a situation?
View 11 Replies
View Related
Jul 14, 2015
The Input parameters will be supplied like below for a procedure.
DECLARE @Subject NVARCHAR(100) = 'Math, Physics, Science'
,@ClassNumber NVARCHAR(MAX) = '102,103|415,206|712,876'
/*
I need to select subject with classnumber's supplied from the input screen and output a result like this.Let's say I have a temporary table dbo.TableA
--For Math
INSERT INTO dbo.TableA
SELECT @Subject FROM Class
WHERE @ClassNumber in (102,103)
[code]....
View 6 Replies
View Related
May 25, 2007
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID". Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you.
Web Page 1:
Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
Web Page2:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @snCoDeptRowID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue" />
<asp:QueryStringParameter Name="User_ID" Type="Int32" QueryStringField ="@snCoDeptRowID" DefaultValue ="@snCoDeptRowID" />
</updateparameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Jun 26, 2004
Edited by SomeNewKid. Please post code between <code> and </code> tags.
Right now i'm just trying to get the page to update SQL, but its not working no errors or anything but still not updating?!?!
Any ideas? Thanks in advance
Ben
<%
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")
cnnSimple.Open Blah Blah Blah
Set rstSimple = cnnSimple.Execute("SELECT * FROM WK_DATA WHERE WEEK_NUM = 2")
%>
<input name="Submit" type="button" value="Add">
<%
If Request.Form("Submit") = "Add" Then
cnnSimple.Execute("UPDATE WK_DATA SET PRICE = (PRICE + 1) WHERE WEEK_NUM = 2")
rstSimple.update
End If
cnnSimple.Close
Set cnnSimple = Nothing
%>
View 1 Replies
View Related
Dec 4, 2005
How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you
View 7 Replies
View Related
