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.
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
HiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
I have a problem when writing a dynamic sql. After simplify the problem it looks like below.
Have a table like below consist of 2 columns No and Value. Value contains a sql, that after executing it gives the value.
No Value
1 select name form table1 2 select age form table1 3 select address form table1
I need to generate the table form the above table with the real value of the sql scripts in the value column. I tried the sql script below but didn€™t work. SELECT NO, EXCE(VALUE) AS NEW_VALUE FROM TABLE . Why can''t we use EXEC in side SELECT statement? What is the reason for that? Is there is another way to do that? I want a sql script do the above problem. If you can't get the proble correctly please let me know so i can explain more.Need help regarding this.
I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.
Here's the code:
Code Snippet
public override void ProcessInput(int inputID, PipelineBuffer buffer){
I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.
I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.
is there a possibility to print the value of a variable in the debug console? it seems that Console.WriteLine in a script task doesn't work. or is there a better way in order to debug the value of a variable at a certain point?
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
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.
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:
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'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) )
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
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 ?
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?
For some reason my Stored Procs are not recognizing NULL values within my database:For example:Select *From ResultsWhere(home_Phone IS NULL)All the home_Phone vaules that are NULL are not being picked up by the query. Any ideas are appriciated. Thanks in advance everyone. RB
HiI have the following tables and stored procedure. I need to pass a value tothe stored procedure and have it use the value in a query. After runningthat query it will return an ID which is then used in an insert statement.At present the values in @MovieId int, @UserID int are left empty (see myoriginal code at the bottom of posting). I think this is due to an issuewith when the sql is executed (?).I get the feeling I should have something like this instead, but not sure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieName@MovieId = exec MovieName2Id @MovieName-- Find UserID from UserEmail@UserEmail = exec Email2UserId @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)Thanks in advance.========MY CODE=============Tables:CREATE TABLE [dbo].[movies] ([movieID] [int] IDENTITY (1, 1) NOT NULL ,[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[movieDateAdded] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[transactions] ([userID] [int] NULL ,[movieID] [int] NULL ,[FrameNumber] [int] NOT NULL ,[transDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[users] ([userID] [int] IDENTITY (1, 1) NOT NULL ,[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[userDateRegistered] [datetime] NULL) ON [PRIMARY]GOStored Procedure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieNameSELECT @MovieId = MovieIdFROM MoviesWHERE MovieName = @MovieName-- Find UserID from UserEmailSELECT @UserID = UserIDFROM UsersWHERE UserEmail = @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.
select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh where luis_id in ('I','E') and aseld.whse_id = 1 and ( (aseld.batch_id between goal_beg_batch and goal_end_batch and monitor_group = 'YK')
[code]....
is it possible to get the numbers from keys 1 & 2 on the same row in a new query?
Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.
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
Help, please. I am trying to update atable with this structre:CREATE TABLE Queue (PropID int, EffDate smalldatetime,TxnAmt int)INSERT Queue (PropID) SELECT 1INSERT Queue (PropID) SELECT 2INSERT Queue (PropID) SELECT 3....from this table...CREATE TABLE Txns (PropID int, TxnDate smalldatetime,TxnType char(1), TxnAmt int)INSERT Txns SELECT 1 '20000201', 'B', 100000INSERT Txns SELECT 1 '20020515', 'B', 110000INSERT Txns SELECT 1 '20020515', 'A', 120000INSERT Txns SELECT 1 '20020615', 'c', 130000....only certain txn types are okay, and they have an orderof preference...CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)INSERT GoodTxnTypes SELECT 'A', 1INSERT GoodTxnTypes SELECT 'B', 2The idea is to fill in the NULL fields in the Queue table,according to a rule -- the transaction must be the latesttransaction within a date window, it must be one of the goodtxn types, and if there are two txns on that date, choosethe txn by the preferred txn type (A is preferred over B,according to the field Pref).If the time window were 20020101 to 20030101, the txnselected to update the Queue table would be this one:INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there aretwo in the time window that are type A or B; they areboth on the same day, so the 'A' is preferred.If the time window were 20000101 to 20010101, this wouldbe selected because it is the only A or B type txn inthe interval:INSERT Txns SELECT 1 '20000201', 'B', 100000I'm looking for a statement that starts...UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,in this table, is the same as TxnDate in the Txn table).Assume we have @FirstDate and @LastDate available.Help, please. I'm getting stuck with (a) a sub-query tofind the relevant Txn records, and (b) another sub-querywithin that to find the MAX(TxnDate) within the timewindow. Filtering the Txn records on the basis of theGoodTxnTypes table is easy, as is ordering what is returned.But I'm having trouble joining the sub-queries back to theQueue table on the basis of PropId.
hi, i have this sql statement: @uname varchar(20) select@sql = 'select user_name, password, role_code, expiry_date,effective_from,active from usermaster where userid='1' and username like'@uname%'
it is not worrking for me.i don't know how to pass values in dynamicsql,can any one answer for me please.
I have an accounting database which contains data from various years.The frontend is a VB.Net program. At the year end, the program createsnew voucher and transaction tables and creates new stored procedures forthem.I just append the 'new year' at the end and create themie, Vouchers2001, Vouchers2002, Vouchers2003Similarly Transactions2001, Transactions2002.The data for all the years is in the same database.Also, I maintain a table called 'Books' which contains the Years forwhich data is present in the Database. The Structure of the Books tableisBookID BookYear1 20012 20023 20034 2004My Problem is that i need to know the current balance of any ledger forany year. The method to calculate the balance for any year is to startfrom the Minimum year in the Books table and continue upto the requiredyear. The SQL is as follows.DECLARE @iLedgerID AS INT --will be passed as parameterDECLARE @iYear as INT --will be passed as parameterDECLARE @CurrentBalance as MONEYSET @iLedgerID =1DECLARE @MinBook as INTEGERDECLARE @String nVarchar(4000)SELECT @MinBook = Min(BookYear)FROM BooksWHILE @MinBook <= @iYearBEGINSET @String = ' DECLARE @TT Money ' + char(13) +' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOINdbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +'WHERE (LedgerID = @iLedgerID)'EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */SET @MinBook = @MinBook + 1ENDNow this is just a sample code. It may have a few glitches. My questionisa) Do I have to create a dynamic sql if the name of the database is notknown ahead of time. If No thenb) I need to add the balance of each year to the grand total. How do ireturn a value from a dynamic sql.TIA*** Sent via Developersdex http://www.developersdex.com ***
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus: I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting: My idea is best represented in psuedo-code: - Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash". - The function would look something like: CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN DECLARE TABLE @t1 (account INT) INSERT INTO @t1 VALUES (100) INSERT INTO @t1 VALUES (101) INSERT INTO @t1 VALUES (102) INSERT INTO @t1 VALUES (407) RETURN @t1 END
Then I could call this function by doing something such as:
SELECT * FROM Accounts WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is: CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN INSERT INTO @t1 VALUES (100) RETURN END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
I have been trying to add in date functions for parameters in a report subscription and they are not accepted. I have a report with 2 parameters, @start and @end. I have default parameters set wtihin the report of Now - month for @start and Now for @end. Can my users create a subscriotion using something like "=DateAdd("D", -1, Now)" for the parameters rather than the default?
