Transact SQL :: Placement Of Option (Recompile) In Dynamic For XML Select Statement?

Apr 18, 2015

I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .

USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ...

-- Generated code - DO NOT MODIFY

-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema

-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe

Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],

[code]....

View 7 Replies


ADVERTISEMENT

No Recompile After Sp_recompile Or WITH RECOMPILE Option

Aug 30, 2007

I know that all the documentation always tells you that sp_recompile will force a stored procedure to recompile the next time it is executed. However, I am not seeing the recompiles in a SQL Trace, when capturing SP: Recompile events. I have tried this on many different database servers, using sp_recompile and also the WITH RECOMPILE option when creating the proc.

Can anyone explain this?

View 4 Replies View Related

Option (RECOMPILE)

Oct 25, 2007

Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.  
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)

View 1 Replies View Related

OPTION(RECOMPILE) And Execution Plan

Jan 23, 2008



Hi,


I use recompile option in SQL query to dynamic pass variable to optimizer.

I verify explain plan with SET STATISTICS PROFILE ON

and optimizer chose nested lookup ,ok. But if use Display Estimated Execution Plan (CTR+L) I€™ve get merge join. It€™s very confusing, some suggestion €¦?


Use AdventureWorks

go

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId

AND h.OrderDate >= @StartOrderDate

OPTION(RECOMPILE);

SQL2005SP2


djedgar

View 4 Replies View Related

Select More Than Two Option In SQL Statement

Nov 9, 2004

hi, is there any way to select more than two option in the select case sql statement?

i try the sql below, but it give me an error 'Incorrect syntax near the keyword 'if'.'

Thanks in advance.


(SELECT stay = CASE WHEN stay >= 10 THEN '10 stay' ELSE if stay >7 then '7 - 9 stay' else 'no stay' END) AS stay

View 2 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Dynamic Select/Update Statement Possible?

Dec 3, 2004

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable

Just curious.

Thanks,
Steve Hanzelman

View 6 Replies View Related

Dynamic Column Names In Select Statement

Oct 13, 2001

I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.

declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.

The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.

View 1 Replies View Related

Dynamic CREATE TABLE Or SELECT INTO Statement

Jul 27, 2004

In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View 1 Replies View Related

Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts

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

Transact SQL :: With CTE N Multiple Select Statement

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

Transact SQL :: SELECT STATEMENT With Increment

Jun 23, 2015

How I can get the following Desired SELECT STATEMENT with the increment of Max(serial_no)+1. Date will be in quotation in desired SELECT STATEMENT. All data is same. Just changing the serial_no which will be Max(serial_no)+1.

Create table #seq (serial_no numeric, Scode numeric, Sdate datetime, SErr char(10))
insert into #seq values (1,1002,'10/10/2015','SEDT')
insert into #seq values (2,1005,'12/10/2015','PEFT')
insert into #seq values (3,1004,'1/1/2015','QEGT')
insert into #seq values (4,1008,'1/2/2015','TEWT')
insert into #seq values (5,1007,'3/10/2015','REDT')

Result from the above query

serial_no  Scode  Sdate  SErr
1 1002  10/10/2015 SEDT
2 1005 12/10/2015 PEFT
3 1004 1/1/2015 QEGT
4 1008 1/2/2015 TEWT
5 1007 3/10/2015 REDT

Desired SELECT STATEMENT Result with increment of serial_no  (Max(serial_no+1)

Select 6, 1002, '10/10/2015', 'SEDT'
Select 7, 1005, '12/10/2015', 'PEFT'
Select 8, 1004, '1/1/2015', 'QEGT'
Select 9, 1008, '1/2/2015' ,'TEWT'
Select 10, 1007, '3/10/2015', 'REDT'

View 3 Replies View Related

Views Containing SELECT * Do Not Recompile When Table Is Modified

Mar 17, 2008

If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.

Is there a work around for this? An "auto-recompile when tables are modified" kind of option?

Thanks
Nick

PS: This is the script I used for testing:

create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2

View 9 Replies View Related

Transact SQL :: Select Statement To Split Data

Mar 28, 2012

I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?

View 10 Replies View Related

Transact SQL :: Run Select Statement That Has (Text Already Placed Into Columns)

Sep 10, 2015

Created a new scheduled job using Transact-SQL.

Running a basic script SELECT FROM WHERE

As of now, it places it into one column, for all the fields. 

I need the fields to be placed in their respective columns.  Tried saving it as .xls and .csv.  Same issue.  Running SQL Server 2008R2.

View 2 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Transact SQL :: Custom Column In Select Statement

Aug 12, 2015

I want to add a custom column in a select statement that has a value to true or false based on other criteria.

SELECT [ID], [Name], [Description], [EmpID], [Employed] FROM [Employees]

Now, in the above example there is no [Employed] Column in my table but I want it to show true or false based on whether or not [EmpID] equals a certain value.

View 6 Replies View Related

Transact SQL :: Passing Variable In Select Statement

Apr 21, 2015

Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.

My error is Invalid column name 'A113', etc.  However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary

-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID

[Code] .....

View 4 Replies View Related

Transact SQL :: Nested Select Case Statement

May 18, 2015

I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.

Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else
WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'

In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.

View 2 Replies View Related

Transact SQL :: Delete Statement From Select Results

Jul 24, 2015

I'm trying to delete the selected data from a table colum from a select statement.

This is the select statement

SELECT  RFC822  FROM  SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification
& invitation%')

What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.

View 9 Replies View Related

Transact SQL :: Using WITH Statement Rather Than Putting A SELECT In A JOIN?

Jun 18, 2015

Is there any reason to use the WITH statement rather than putting a SELECT in a JOIN? Does one method perform better or is it just a matter of preference?

View 3 Replies View Related

SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement

Jul 23, 2014

I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.

Now i want to call stored procedure in select statement because stored procedure return a single value.

I search on google and i find openrowset but this generate a meta data error

So how i can resolve it ???

View 7 Replies View Related

MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem

Dec 13, 2004

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 3 Replies View Related

Transact SQL :: Where Clause Based On Select Statement From Another Table

Jul 9, 2015

I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:

SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT

[code]....

View 6 Replies View Related

Transact SQL :: Carriage Return In Email Body Within A Select Statement

Jul 2, 2015

I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:

ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[code]....

View 2 Replies View Related

Transact SQL :: Case Statement In Where Clause - Select First / Default Value If Two Different Value Exists

Nov 16, 2015

I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.

Store     Type    Sales
11           A        1000
23       A      1980
23       B       50
5         B      560

I want to filter the store in "where clause"  where

1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.

Select Store, sum(sales), Type
from table1
where (TYPE]=  (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]

The above statement is not working for when store has only Type B associated with it.

View 7 Replies View Related

Transact SQL :: Returning Multiple Values From Column In Select Statement?

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

Transact SQL :: Select Statement Not Working In User Defined Function

Nov 3, 2015

The select statement:

SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(Date AS DATETIME) + CAST(Time AS DATETIME))
   FROM [DataGapTest]

Gives the right output:

NULL
1
1
3548
0

However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).

CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time)
RETURNS INT
AS
  BEGIN
 DECLARE @timeInterval INT
   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))
   FROM dbo.[DataGapTest]
  RETURN @timeInterval
  END

View 5 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

Transact SQL :: Transpose Part Of Rows To Columns In Single Select Statement

Aug 31, 2015

Below. I have also pasted the current result of this query and the desired result. 

Query can be updated to get the desired result as given below?

Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'

Current Result which I am getting with this query

OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01

Desired/Expected Result for which I need updated query

OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3

5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01

Using DB2.

View 2 Replies View Related

Why Optimizer Is Not Smart? Is Dynamic SQL My Only Option...

Feb 8, 2007

declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdOR @ContactId = -1If you run this in SQL 2005 on the AdventureWorks database,why the logical reads is 561Table 'Contact'. Scan count 1, logical reads 56and not 2 when you run without the second OR condition:declare @ContactId as integerset @ContactId = 5select *from Person.Contactwhere ContactId = @ContactIdHow can i use the same SP and either get one record returnedby passing the ID of the field, or pass a dummy parameter like-1 in order to get ALL the records returned.In this case even when i pass a parameter like ContactID = 5there is still a table scan (clustered index scan in this case)happening for the other OR condition.There's no method to tell SQL to start checking the first conditionwhether or not it is true then if it is false then check the second ORconditon. On the same topic does this mean all OR conditions areALWAYS verified regardless if one of them has already been determinedto be True?Thank you

View 2 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved