Using Conditional Statement In Stored Prcodure To Build Select Statement
hi
I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
SQLStmt=SQLStmt+ field1
end if
if param2 then
SQLStmt=SQLStmt+ field2
end if
View Complete Forum Thread with Replies
Related Forum Messages:
Cannot Build This Custom Select Statement...
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this: SELECT filenameFROM pictableWHERE (@searchby LIKE @searchwords) It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works: SELECT filenameFROM pictableWHERE (keywords LIKE @searchwords) Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail. Thanks in advance.
View Replies !
Conditional Select Statement
Hello dbForumers, Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ? Thank You!
View Replies !
MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem
Hi All, I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number. My BASIC Select Statement is: SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1, Address2, Suburb, State, Postcode, Phone FROM Customers WHERE Customer_No IN (SELECT Customer_No FROM temp_Customers) Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below: SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No FROM Customers GROUP BY Customer_No ORDER BY First(Cust_Ref_No), Customer_No; There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)? P.S. I really need to run the select statement as one step rather than splitting it up into parts. Regards, Trog28
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
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 Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Need To Run Stored Procedure In Select Statement
I am trying to execute a stored procedure in a select statement. I have a stored procedure that returns the next number in a sequence (much like an identity column) I am trying to do an insert statment like the following insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar Is there any way I can do this? I do not want to use a trigger or an identity column.
View Replies !
Stored Proc Within A Select Statement
I know that you can use Case to selectively put a value in a column in a Select statement. Is there any way you can invoke a stored procedure to get a value to a column (either an existing or ‘ad-hoc’ column?) within the Select statement? I saw something half-way there in the following: Insert into myTable exec master..xp_fileexist ‘c:autoexec.bat’ (myTable was previously created in this example) Thanks, Judith
View Replies !
Trying To Use Stored Procedure In A Select Statement
Hi there, I am trying to return data from a select statement that is running a stored procedure. I am running on SQL 2000 SP4. I have created a 'loopback' linked server as described in a technet article. (It's pointing at itself) I can then use the following select statement to get data back in the select statement from a stored procedure. select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging') I am trying to get data back from my own Stored Procedure, but continue to get the following error: Server: Msg 7357, Level 16, State 1, Line 1 Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging']. If I try the same syntax with the sp_who stored procedure, it works and I get data back fine. But, If I try it with the sp_who2 stored procedure I get the same error as above. select * from openquery(loopback,'exec sp_who') --> Works fine select * from openquery(loopback,'exec sp_who2') --> Doesn't work Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't? Is there a way I can change my stored procedure such that it would cause it to work? PS: The following code was used to create the linked server, and then a security pass though as a certain account was addedDECLARE @provstr varchar (2000) SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL', @provider = 'SQLOLEDB', @provstr = @provstr
View Replies !
Can A Stored Procedure Contain More Than One Select Statement
Presently I am implementing search engine.Here i need to check a KEYWORD in more than one table. I have 15 tables in my database. I have to search this KEYWORD in all tables in the database. So i want to write 15 select statements in a single stored procedure. Can I catch these 15 tables through output parameters. If it is possible can any one tell me. If this not the right way can you tell me the correct approach. Thanks in advance.
View Replies !
Stored Procedure In SELECT Statement ?
Hello, I want to recover rows returned by a stocked procedure into a select statement, something like this: select * from (exec sp_helpuser) us where UserName like 'dbo' This syntax is false. Is there a way to do this? Thank you
View Replies !
Stored Procedure - Using Value From A Select Statement
Hello, I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy. Is this possible? If so, how do I do it? Thanks.
View Replies !
Stored Procedure In A Select Statement
Hi All, Can i run a stored procedure with in a SELECT statement like below? Select * from EmployeeDetails where EmployeeName in (.. Some Stored procedure to return me some set of Employee Names) Is there a better way of doing it? Thanks in advance vishu Bangalore
View Replies !
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
How To Build SQL Statement
these are my databases 1. project project_code| project_name | 20201 |Tanaman Padi | 2. account_name an_account_code | an_account_name |an_project_code | K6101 | PADI 1 |022020105 | K6102 |PADI 2 |022020105 | K6103 |PADI 3 |022020103 | 3. account_record ar_account_record | ar_current_debit | ar_current_credit| K6101 | 0 | 1000 | K6102 | 0 | 2000 | K6103 | 0 | 3000 | the agregate query bussiness volume = ar_current_credit - ar_current_debit i would like to get this output no| name | bussiness_volume| 1. | Tanaman Padi | 6000 | this is what ive done 1. 'get name sSQL = "select project_name, an_project_code from project, account_name where project_code = substring(an_project_code,3,5) and substring(an_project_code,3,2) = '20' " 2. 'get bussiness volume select sum(ar_current_creddit-ar_current_debit) as bussiness_volume from account_record where ar_account_code = rs1("an_acc_code") this is the results no | name | bussiness volume 1. |Tanaman Padi | 1000 2. | Tanaman Padi | 2000 3. | Tanaman Padi | 3000 what should i do?
View Replies !
How To Put A Select Statement And Get Results In A Stored Procedure
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction. CREATE PROCEDURE crm_contact_frequency_report @TheYear varchar(4),@TheMonth varchar(2) AS SELECT /* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails, /* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls, /* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes FROM CampaignResponse AGO
View Replies !
Call A Stored Procedure From A SELECT Statement
Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
View Replies !
Multiple Select Statement In A Stored-proc?
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 Replies !
Results Of SQL SELECT Statement Into Stored Procedure
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
View Replies !
Can A Stored Procedure Be Executed From Within A Select Statement?
Can a stored procedure be executed from within a select statement? Given a store procedure named: sp_proc I wish to do something like this: For each row in the table execute sp_proc 'parameter1', parameter2'... end for ...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
View Replies !
Calling Stored Procedures In A Select Statement
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View Replies !
Call Stored Procedure Within SELECT Statement
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it. USE NORTHWIND GO CREATE TABLE tbA ( Item int NOT NULL, Value int NOT NULL ) ON [PRIMARY] GO INSERT INTO tbA (Item, Value) SELECT 1, 10 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 2 GO CREATE PROCEDURE usp_SquareIt @iItem int AS declare @iValue int SELECT @iValue = Value FROM tbA SELECT @iValue * @iValue AS Result GO SELECT Item, EXECUTE usp_SquareIt Item AS Squared ---- can this be done FROM tbA GO DROP TABLE tbA GO DROP PROCEDURE usp_SquareIt GO Any thoughts? Mike B
View Replies !
Execute Stored Procedure In SELECT Statement
Hi, I created one stored procedure (eg: sp_test) and it returns one value. I am trying to execute this stored procedure using SELECT Statement in Query analyzer. SQL Statement: DECLARE @Y = INT SELECT Column1,Column2, EXEC @Y = sp_test(Column1) FROM TABEL1 It is giving error when I execute this SELECT statement. Can anybody help on this that how to execute the store procedure in SELECT statement. Thanks, Arun.
View Replies !
Stored Procedure In SELECT Statement?? Urgent
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this. SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2 I want to write it in SQLserver. I have tried this syntax but get error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 't'. SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2 Can I use stored proc in this way in SQL-server?
View Replies !
Call A Stored Procedure In The Select Statement
Is it possible to call a stored procedure in a select statement? For example: SELECT Buyer, Country, (exec the sp_test*) as ItemList..... *sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).
View Replies !
How Do You Build A Dynamic WHERE Statement?
I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext. How would I go about building this efficiently? Would I Declare a bit value in the sp called WhereSet = 0 IF @client IS NOT NULL IF @WhereSet = 0 SET @Where = 'WHERE ClientID=@client' SET @SetWhere = 1 ELSE SET @Where = @Where + ' AND CleintID=@client' . . .... Or would this be a lot easier using adhoc SQL instead of a Stored Procedure? (note: I am using a SQL DataSource) Please help, I am going bald from pulling my hair our...
View Replies !
Select Statement Returns Null In Stored Proc
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View Replies !
Build SQL Statement To Show Some Tiers
in my relational sql db structure I have some items and its children, grandchildren itemsto print these values do i need to build one sql statement for each tier?into my sproc i tried something as following but it returns me only the last tierSELECT * FROM AINNER JOINB ON A.field1 = B.field2INNER JOINC ON A.filed3 = C.field4WHERE A.filed1 = @myParam thanks a lot
View Replies !
Problem With The SELECT Statement Of The Stored Procedure In String Comparison.
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard code it, it gives me the right result. The I check if the field value of 'Email' is exactly equal to the parameter '@Email'. The field 'Email' is varchar, and CID is integer. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com' return Can anyone tell me the reason for it. Thanking you in advance Tomy
View Replies !
Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't: <asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma" Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px"> </asp:DropDownList> <asp:Button ID="btnImport" runat="server" Text="Import" /> <asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" /> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>" SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber"> <SelectParameters> <asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
View Replies !
Stored Procedure - SELECT INSERT Statement - Good Practice?
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package. I would like to only import data since the last import so I was thinking of my logic to be like this: INSERT INTO Table_2 (Field1, Field2, ... , FieldN) VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate)) Does this make sense? Or do you all suggest a different mannger of accomplishing this?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
SQL Conditional WHERE Statement
Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement? Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test] ( @startRef int, @endRef int ) AS BEGIN SELECT Count(*) FROM myTable WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef))) END RETURN
View Replies !
Conditional Where Statement
I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View Replies !
Conditional WHERE Statement?
Hi all,I have one for all the blackbelters out there: is there a way i canmake a stored procedure where i can control the where statement withvariables? I have to do some complex transformations to get compose afact table for MSAS and there a a lot of similarities between thequeries and a few differences because of different account methodsetc. (booking in starting date, booking stuff on order entry datesetc) I want to put a combination of different rules in differentmembers of dimensions.An example of what i mean:CREATE STORED PROCEDURE dbo.FILLFACT (@PAR1, @PAR2)ASINSERT INTO FactTable (blah blah)SELECTIF @PAR1 = 'OrderDate'SourceView.OrderdateELSESourceView.StartDate,etc etc...FROMSourceViewWHEREIF @PAR2 = 'WholeTable'1=1IF @PAR2 = 'Incomplete'EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE...., etc)This way i could fill my fact table withEXEC dbo.FillFact 'beginDate','Wholetable'EXEC dbo.FillFact 'begindate', 'Rulebook1'EXEC dbo.FillFact 'BeginDate', 'Exceptions'etcetera.This is not an actual SQL script i use, just an example of what i'mtalking about. Or maybe i could pass the where statement entirley as avariable? But i can't use SET @PAR1 = 'EndDate IS NULL' and then useWHERE @PAR1 can I?I hope i'm making sense. Does anyone know if this is possible? Rightnow i have a procedure that is composed of a dozen of sql scripts thatare mostly the same, but i have to copy it for every combination ofsituations and then, of course, new stuff has to be added on 12different places. Again and again.Any thoughts?TIA,Gert-Jan van der Kamp
View Replies !
Conditional Statement! HELP!
Hi, I've been looling around, but was unable to correctly use a conditional statement in a T-SQL Query. I have this SQL query and in it how I would do if it was C#. If someone could please help me get what I need I would appreciate it a lot. Here is the query: Code Block SELECT Ficha, Almoxarifado, [Código do Item], Descrição, Unidades.Unidade, [1ª Contagem], [2ª Contagem], Recontagem, Observações, Cancelar FROM Fichas INNER JOIN Itens ON [Código do Item] = Código INNER JOIN Unidades ON Itens.Unidade = Unidades.ID WHERE Ficha BETWEEN 01 AND 5000 AND Recontagem IS NULL AND /* What I would do in C#, but need in T-SQL*/ if ([1ª Contagem] > [2ª Contagem]) { if ([2ª Contagem] / [1ª Contagem] < 0.99) return true; else { if ([1ª Contagem] / [2ª Contagem] < 0.99) return true; } I really need a help in this. Does anyone know how to accomplish this? Thanks. Regars, Fábio
View Replies !
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
SQL Help: Conditional Statement Using Inserted
I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table. Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time. So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got: 1 CREATE TRIGGER [NewSoftwareTask] 2 ON [dbo].[Software] 3 AFTER INSERT 4 AS 5 BEGIN 6 7 -- If a software task already exists for this request 8 -- then update it. Otherwise create a new task. 9 10 if exists(select TasksID 11 from Tasks 12 where Tasks.RequestsID = inserted.RequestsID and 13 TasksType = 'Software') 14 BEGIN 15 UPDATE [BGHelpdesk].[dbo].[Tasks] 16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments 17 WHERE Tasks.RequestsID = inserted.RequestsID and 18 TasksType = 'Software' 19 END 20 21 else 22 23 BEGIN 24 INSERT INTO [BGHelpdesk].[dbo].[Tasks] 25 ([RequestsID] 26 ,[TasksType] 27 ,[TasksSubType] 28 ,[TasksTitle] 29 ,[TasksDescription]) 30 SELECT 31 s.RequestsID 32 ,'Software' 33 ,s.SoftwareType 34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar) 35 ,s.SoftwareComments 36 FROM Software s join 37 inserted ON s.SoftwareID = inserted.SoftwareID 38 END 39 END 40 GO It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...
View Replies !
IIf ANY Value In Table Conditional Statement
This is probably quite simple, but it isn't sticking out at me. In a simple table with two groups, I want to conditionally set the color of the first group to red or green based upon whether ANY value for this field equals a particular string. I know how to conditionally set the color of the field. However, the below code appears to only compare the FIRST value in Fields!myField.Value to the string "bad". I want it to return "Red" if ANY of the values for Fields!myField.Value = "bad". Code Block=IIf(Fields!myField.Value = "bad", "Red", "Green") myField is ouput in the detail scope if that makes any difference. Is this possible?
View Replies !
Adding Conditional Statement To A Sum
currently I am creating a total if the date is between two entered dates: select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount, I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?
View Replies !
Case Conditional In SQL Statement 2000
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.
View Replies !
TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View Replies !
Summing Items From A Conditional Statement
What I need to do in seperate a group of numbers into two different categories based on a phase code. I have acheived this through two conditional statements, but when I try to total the numbers that were returned for each group I receive an #error. This is an example of the switch statement I used in order to return the correct values for the Implemenataion. =Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "") I've tried several different methods for aggregating the numbers that are returned. =SUM(Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "")) I've tried substituting a 0 in for the "" at the end of each statement. I've also tried to take the first statement and put it into its own table field named ImplementationLedger, and them summing it. ie. =SUM(Fields!ImplementationLedger.Value) Please Help!
View Replies !
Conditional Where Clause W/ Case Statement Possible?
Greetings, After many hours search many forums and many failed experiments, I figure it's time to turn to the experts. I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls. SELECT uniqueID, lob, xdate FROM mytable WHERE CASE WHEN @myparam = 'ALL' THEN xdate >= '2007-09-01' ELSE xdate >= '2007-09-01' or lob = @myparm END I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank. I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much. Any ideas? Rob
View Replies !
Conditional Statement In View Design
Have a View where I need to enter a conditional IF_THEN_ELSE statement in a new field (field has an alias). If this were an Access query I would do following: IIf([dbo.AR1_CustomerMaster.EmailAddress] Is Null, "A", "B") How can I accomplish same in View design??
View Replies !
|
TRACKER
