Can You Have Multiple Output Parameters? Difference Between A Select Statement?
Mar 9, 2004
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Thanks in advance.
View 1 Replies
ADVERTISEMENT
Mar 17, 2004
Hi,
thanks for reading!
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter
i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)
AS
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null)
begin
while patindex('%,%' , @productIds) <> 0
begin
select @separator_position = patindex('%,%' , @productIds)
select @objectId= left(@productIds, @separator_position - 1)
INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)
select @productIds = stuff(@productIds, 1, @separator_position, '')
end
end
set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in
(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
GO
View 3 Replies
View Related
Sep 8, 2006
hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky
View 3 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
Feb 23, 2007
In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.
Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.
The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.
Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?
Thanks,
Mike
View 15 Replies
View Related
Mar 7, 2008
If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?
View 1 Replies
View Related
Oct 7, 2007
Hello Guys,
Please could you help me formulate a SELECT statement to output a date in the format (Month, Date Year), eg
October, 07 2007. Thanks.
View 5 Replies
View Related
Nov 26, 2007
Hi,
If I query like this, am getting the following Output.. Why is the part before '_ ' gets truncated and gets displayed as result ?
SELECT 10_to_100
_to_100
-----------
10
SELECT 25_from
_from
-----------
25
If anybody is familiar with the reason,pls share...
cheers !
ash
View 5 Replies
View Related
Aug 21, 2007
hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
....
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
anyone have any suggestions ?
View 2 Replies
View Related
May 30, 2006
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View 1 Replies
View Related
May 7, 2008
In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?
View 7 Replies
View Related
Feb 22, 2005
I'm executing the following...
select COL1, min(COL2) from TABLE group by COL1
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
tia
a
View 2 Replies
View Related
May 28, 2014
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
View 2 Replies
View Related
Sep 29, 2005
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View 2 Replies
View Related
Apr 27, 2008
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View 2 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Aug 12, 2005
When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.
View 5 Replies
View Related
Apr 6, 2007
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
Regards,
Bill
View 23 Replies
View Related
Jul 22, 2015
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',
[code]....
View 2 Replies
View Related
Sep 13, 2006
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
View 1 Replies
View Related
May 8, 2014
I am trying to apply the logic from the following resource: URL....but cannot get it to work with my logic for some reason.For example, the following query:
;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1),
CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Produces the following output:
Col1 | Col2
2014-05-08 10:55:54 | 2014/05/08
But, as soon as I try to do something else like:
;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1),
CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
I receive the following errors:
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'CTE1'.
Msg 8155, Level 16, State 2, Line 2
No column name was specified for column 1 of 'CTE2'.
Are there limitations when trying to use multiple CTE in a single query?
View 5 Replies
View Related
Apr 29, 2015
SELECT FirstSet.Country,FirstSet.[Month]
,ABC.ABC1
,DEF.DEF1
FROM (
SELECT [Answer Text]'Country',interview_start 'Month',[ID respondent],
[Code] ....
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 351
Incorrect syntax near the keyword 'as'.
View 8 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 :Â @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 :Â @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 :Â @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutputÂ
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Mar 1, 2005
I am using SQL sever 2k and C#.
There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?
What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?
I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.
stored procedure example: NorthWind database:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
C# code example:
//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());
Thanks for your help.
View 2 Replies
View Related
Aug 29, 2006
hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor
View 5 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
Aug 9, 2006
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...
I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.
SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)
The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.
Has anyone got a good tip to help me resolve this puzzle?
View 6 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Jul 30, 2007
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View 4 Replies
View Related
May 18, 2006
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you
View 2 Replies
View Related
Nov 15, 2012
I am in doubt if its possible to make a select statement which enables me to consolidate multiple lines in the same table.
I have a table with a lot of companies and figures for each company.
Some of the companies owns some of the other companies in the table and in reverse, some of the companies are owned by some of the companies in the table.
I have a lot of columns, but basically the most important columns are:
Company Name, Company Mother , Company Daughter.
Each company has also a revenue column.
What I want to do is to consolidate all figures for absolute mothers e.i. companies which are not owned by another company in the list.
I therefore need a select statement which says something like:
Get the revenue of companies not owned by another company (e.i. absolute mother). Add to this, the revenue of all its daughters. Add to this the revenue of all the daughters daughters etc. until there are no daughters left.
In other words - aggregate the revenue for all the companies in the group under the name of the ultimate parent company.
I can easily select and add the revenue for the first level of direct daughters, but I dont know how many more daughters the daughters has etc.
View 2 Replies
View Related
Nov 26, 2015
I am writing a query and have the bulk of it already written.Â
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like thisÂ
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
View 3 Replies
View Related
Aug 18, 2015
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....
View 7 Replies
View Related
