I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
Hi,
This question has been asked probably million times, but it sems that I cannod find right answer on search engines.
I need to send parameters to my stored procedure, but not only parameters. For example in where clause I have something like:
where myID = 12
I need to be able to filter results on myID, but one time I need it to be eqal to 12 and other time I need it to be different (<>) from 12. Some time I even need to add another condition like myID2 = 1. Can I solve this without additional procedures?
I believe that I saw solution in 3-Tier ArchitectureTutorial Series few weeks ago but it seems that something changed, and I cannot find it anymore. Can anyone help?
I am trying to get the sum of two dynamic queriesThat is:EXEC('SELECT COUNT(id) as subtotal1...) + EXEC('SELECT COUNT(id) as subtotal2...)If I assign to a variable, say @total, I get errors or NULLs. Is there a workaround?Thanks in advance for your comments
View 1 Replies View RelatedHi,
I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A
if Param1=A, then I need to use query1 for the report
if param1 = B, then I need to use query2 for the report.
I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.
thanks
I'm creating a search function right now and I'm running into a problem where my search function doesn't know what fields the user will search for, and therefore doesn't know whether or not to put quote marks around specific values or not.
For example, somebody might search for somebody else with year of birth in which case I might have a query:
SELECT userid FROM users WHERE yob = 1970
but somebody else might search for a name, in which case I need
SELECT userid FROM users WHERE first_name = 'Andrew'
or somebody else might search for both and need
SELECT userid FROM users WHERE yob = 1970 AND first_name = 'Andrew'
I'm accomplishing this by having the function (this is in PHP) take an array as an argument, where the key is the MySQL column and the value is the required value for that column. So in the 3rd example, it would be a 2-item array:
yob => 1970
first_name => 'Andrew'
And then cycling through that and dynamically creating a MySQL query based on the received array.
So... my problem is finding a way to specify when quote marks are required and when they're not. Is there any way to just have MySQL default to the format of the column? Also, if anybody thinks this isn't the right way to create a search function, let me know because I'm new at this .
Thanks!
PS: Right now what I'm doing is I'm creating arrays that include names of columns that do and don't need quote marks. Then in construcing the MySQL statement I'm checking to see which array a column is in, and making the quote decision based on that.
I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,
"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:
They can choose a Hamburger, with every topping
They can choose a Hamburger with a single topping.
They can choose a Hamburger with multiple toppings.
They can choose a Salad with the same combinations as above.
They cannot choose both a Hamburger and a Salad - mutually exclusive items.
Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:
Code Snippet
CREATE PROCEDURE GetFoodInfo
@from datetime,
@to datetime,
@FoodType varchar(20),
@toppings varchar(20)
AS
BEGIN
DECLARE @query varchar(300)
SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '
IF @FoodType = 'Hamburger'
SET @query = @query + 'FROM Hamburgers '
ELSE
SET @query = @query + 'FROM Salads '
IF @toppings <> 'ALL'
SET @query = @query ' WHERE Toppings = ' + @toppings
EXEC (@query)
Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:
GetHamburgers <-- would get Hamburgers with all toppings
GetHamburgersWithToppings
GetSalads
GetSaladsWithToppings
What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.
Again, this is a small example, but I hope someone can help.
The dynamic sql is used for link server. Can someone help. Im getting an error
CREATE PROCEDURE GSCLink
( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )
AS
SET NOCOUNT ON
--Create temp table
CREATE TABLE #TempTable
( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit )
INSERT INTO #TempTable (Name, AccountID, Active)
--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'
EXEC sp_executesql @sql
--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
Error:
Msg 156, Level 15, State 1, Procedure GSCLink, Line 22
Incorrect syntax near the keyword 'DECLARE'.
Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies View RelatedHello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
How to Gererating Dynamic Queries During Run Time and execute the results
Thanks in advance
Suresh
Hello, i have dynamic queries for example:
DECLARE @query nvarchar(250);
DECLARE @table nvarchar(100);
SET @table = 'table_name';
SET @query=
N'update '+@table+'
set column = 0
where column is null';
EXECUTE sp_executesql @query
SET @query=
N'update '+@table+'
set column2 = 0
where column2 is null';
EXECUTE sp_executesql @query
It is very simple example. My question is: it is possible somehow put these queries in SQL Tasks?
Because if i do like this i get error "The Declare cursor SQL construct or statement is not supported."
Maybe there is other way to solve my problem? Or maybe syntax should be changed?
Thanks for advice
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:
CREATE TABLE dbo.tblsapParentCust (
Parent char (10) NOT NULL ,
Name varchar (40) NULL,
IsSoldTo bit NOT NULL DEFAULT 0,
CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED
(Parent)
)
GO
<load in some data>
SELECT TOP 10 * FROM tblsapParentCust
The select statement results in a syntax error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.
I can switch over to pubs and change the query to reference the Authors table, and it runs fine.
If anyone can explain this behavior to me, I would appreciate it.
Thanks,
Buddy
Hi,
In my database I have a field with the type decimal. In the select query I want to return true if this field is smaller than 1 and false if this field is 1. How can I do this?
I need something like that:
Select id, name, (mydecimalField < 1) from mytable
KaaN
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
Hi,
I wanted to know a query which will create a final result table from a combination of select queries.
The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.
null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.
Thanks in advance.
I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:
ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25
What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance
-Lance
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
Hi
Will somebody please explain how to combine asp.net dropdown lists to write
a SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have 3 dropdownlists on my webpage as below,
List 1, Cities, London, Rome, Barcelona etc
List 2, Restaurants by Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20, 20- 40, 50 -100
I want someone to be able to search for a restaurant by selecting an item from each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats.
I would also like the select query to work even if one of the dropdownlists items is not selected.
Hope somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
View 1 Replies View RelatedI am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedBackground
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies View RelatedI have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Thank you all very much for the information.
I want to get output of below query in single row.
Select 'Name'
Select 'Surname'
Expected output is Name,Surname
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
I found out how to select the top 10 used stored procedures. But how do you select the top 10 select statements that are used on the system or database? I had a DBA show me this one time but forgot it after that job. Thanks
View 1 Replies View RelatedI have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.
Thank you
I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '?
For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up.
Any ideas?
Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>