SQL Server 2012 :: Where Clause In Dynamic Query?

Jul 2, 2015

I am creating a dynamic query where i am appending a where clause something like -

IF (@CurCaptureDate IS NOT NULL)
SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate

PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime

but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.

i am not able to use convert here with these quotes.

i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )

but it makes it to -

AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: Using Parameterized Query With Like In Where Clause

Feb 4, 2014

From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:

where [Field1] like @p1 and [Field1] < @p2.
Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause.
@p1 is always a "Starts-With"-value (something like N'abc%').

The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.

Depending on the table size I see 1M or more reads for these querries.

If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.

I was able to reproduce the issue with a temp table. See code below.

Is there a way to make SQL Server use another strategy when using the parameterized query?

SQL Server Version is 11.0.3401.
if object_id('tempdb..#tbl') is not null
drop table #tbl;
create table #tbl
(
[No] nvarchar(20)
,[Description1] nvarchar(250)

[Code] ....

View 9 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

SQL Server 2012 :: Dynamic Query On DB Table Names

Mar 9, 2015

I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.

declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Table Variable In Dynamic Query?

Jul 2, 2015

I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.

For Eg- @TableVariable

SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '

BUt it gives an error that @TableVariable must be declared

View 8 Replies View Related

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

SQL Server 2012 :: Dynamic Query To Print Out Resultset From A Table?

Sep 9, 2015

I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation

Is there any way to print all the 3 INSERT stmt.

IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'

[code]....

View 4 Replies View Related

Transact SQL :: Dynamic Query And Formulating IN Clause

May 7, 2015

I have to build a dynamic query to go over to Oracle and get some data and bring it back via a SQL Server Stored Procedure. How I can create an IN clause passing values and ","s from a SQL Server Temp Table that I have already built.

SET @SQLQuery = @SQLQuery + 'WHERE MEMBER.MEMBER_ID IN (''''' + SELECT MEMBER.MEMBER_ID + ',' FROM #TempTable + ''''' '

View 6 Replies View Related

SQL Server 2014 :: Pivot IN Clause - Dynamic Columns

May 12, 2015

The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.

Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;

Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.

adding or moving ')' or '(' are not working.......

select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........

versus

select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value

select * from #temp order by .....

View 3 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

SQL Server 2012 :: Curious Use Of Percentage On Where Clause

May 5, 2015

I found a code snippet that use the curious following sintax on the creation of View:

CREATE VIEW [dbo].[vw_EvenValues]
AS
SELECT [TestColumn]
FROM [dbo].[TestTable]
WHERE [TestColumn] % 2 = 1

[code]....

IF [TestColumn] on the Select is varchar, then error occurs and say:"Conversion failed when converting for the varchar value 'A001' to data type int"

View 7 Replies View Related

SQL Server 2012 :: Combine Functionality Of IN And LIKE In WHERE Clause

May 8, 2015

I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'

What I would like to do is something like this, which doesn't work:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')

I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.

View 7 Replies View Related

SQL Server 2012 :: Running Totals With OVER Clause?

May 28, 2015

A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.

How does the new OVER clause compare in terms of performance ?

DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl

View 9 Replies View Related

SQL Server 2012 :: Using WHERE Clause For Report Generation

Sep 11, 2015

Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.

The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.

I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.

The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0
OR RefOnly IS NULL

[code]...

View 5 Replies View Related

SQL Server 2012 :: OVER Clause With Insert Very Slow

Sep 29, 2015

I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.

if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,

[code]....

View 2 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related

SQL Server 2012 :: How To Append Go Clause To 10k Lines Of Code

Dec 28, 2014

I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line so it will continue on error messages?

Once I have the syntax I can do a find and replace function in Notepad++

USE [AdventureWorks2014] + char(13) + char(10) + GO
ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
************** Truncate ***********

View 3 Replies View Related

SQL Server 2012 :: How To Modify ORDER BY Clause At Runtime Using Parameter

Jun 4, 2015

the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?

In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:

'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'

The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.

So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.

The code is:

DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'

[code]....

View 9 Replies View Related

SQL Server 2012 :: How To Use Unique Identifiers In Where Clause Not In Select Statement

Jun 8, 2015

I have a two tables each having a uniqueidentifier column person_id

I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.

-- insert into wch_needed those who need checked

insert into #wch_needed (person_id, rendered_by )
select distinct e.person_id, e.rendered_by
from #wch_who o, encounter e
where o.person_id not in (select distinct person_id from #wch_have )
and o.person_id = e.person_id

the where conditional

where o.person_id not in (select distinct person_id from #wch_have )

does not work.

How can I do this?

View 4 Replies View Related

SQL Server 2012 :: Dynamic Value On Pivot

Sep 21, 2015

Can we pass dynamic values while pivoting?

Here is example

Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'

[Code] ......

View 3 Replies View Related

SQL Server 2012 :: Error Message - Aggregate Function And Group By Clause

Feb 19, 2014

I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?

Here is my code so far:

selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,

[Code] ....

View 8 Replies View Related

SQL Server 2012 :: Producing Running Total Column In Select Clause

Jul 27, 2014

I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:

EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek

101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26

What I have tried so far:

Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a

I got this to work one time before, but now I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 2 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'

Below is Query 1:

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar

Out put of this query is 'Noname'

BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar

View 4 Replies View Related

SQL Server 2012 :: Dynamic Pivot With Subgrouping

Nov 25, 2013

I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.

Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.

I have created two tables to illustrate the base data and the required output.

How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.

Please find code below. It should be quite self explanatory as to what I am trying to do.

IMPORTANT:

1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.

create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)

[Code] .....

View 4 Replies View Related

SQL 2012 :: Dynamic Partitioning Of Table Server

Dec 3, 2014

I need Dynamic Partition of SQL Table.

1. What is the best practice for partitioning (on date column)

2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data

3. Is creating partition occupies more disk space?

4. Is every partition would need index?

View 7 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

SQL Server 2012 :: Dynamic String Replacement

Sep 16, 2015

I have a table with the following data;

CREATE TABLE #Tab (Data Varchar(100))

INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab

How do I replace every value before the '=' but leave the comma.

Here is what the final output should look like

CREATE TABLE #TabFinal (Data Varchar(100))

INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'

Select * from #TabFinal

View 9 Replies View Related

Using Dynamic IN Clause

May 9, 2007

I am looking to do a dynamic IN clause, such as the following.create procedured usp_SPTest

@variable varchar(150)
AS

select * from table
where ID IN (@variable)
GO

Then execuuting this withusp_SPTest '''1'',''2'''
Basically this won't work and gives an error
Syntax error converting the varchar value ''1','2'' to a column of data type int.
which makes sense. Is there anyway to do this outside of a dynamic SQL statement?
According to http://www.sommarskog.se/dynamic_sql.html#List it doesn't look promising but unpacking the list into a table is a bit over board for this in my opinion. Any help is appreciated.

View 3 Replies View Related

Need Help With Dynamic Where Clause

Jul 19, 2007

Hi. I can't seem to get my dynamic where clause right.

The pseudo code is such:

when gender is not null and is not 'show all' then create the condition "when tab1.gender = @gender".
and, when country is not null and is not 'show all' then create the condition "when tab1.country = @country".
and, finally, create the condition where tab1.bday_year is
between @toage AND @fromage (this will never be empty or null).

I have tried many variations, the most recent being below.
But, the resulting data does not act as it should (or it won't compile) :


Code:


WHERECASEWHEN @gender <> 'show all' THEN tab1.gender = @gender
WHEN @country <> 'show all' THEN tab1.country = @country
tab1.bday_year BETWEEN @toage AND @fromage END



thanks for any suggestions provided.

View 6 Replies View Related

Dynamic 'where' Clause

Nov 13, 2006

HiI need some advice on which direction to take!Consider this statement:SELECT business_name FROM myTable WHERE town = @town AND county = @countyMy problem is that i will not always have the @county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @county variable is not available, it has a value of 0.thanks againPs, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.

View 5 Replies View Related

Dynamic WHERE Clause

Dec 6, 2007

Hi all,

I pass a WHERE clause to a sp and use EXEC to execute it. it works fine if it does not include datetime Type in WHERE clause. however, if it did, the an error will be shown up "Unclosed quotation mark after the character string '2'.". I try to copy that clause and run as query in SQL server. there is no problem.

the Where clause is (Status='Open' or Status='On Going' or Status='Hold' or Status='Close') And (Found_Date BETWEEN ' '2007-11-01'' And ''2007-11-30'')

View 20 Replies View Related

Dynamic Where Clause

Sep 18, 2007

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@parameter1 if @parameter1 is not null) / (anything if @parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

View 5 Replies View Related

Dynamic Where Clause

Aug 23, 2007

I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -

Method 1 -
exec usp_select @whereCondition='col1 like ''abc%'' and col2 = ''xyz'''
In usp_select, I'll build and execute the sql like -
set @sql = N'select * from table ' + @whereConition
exec sp_executesql @sql
(basically @sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')

Method 2 -
exec usp_select @whereCondition='col1 like @p1 and col2 = @p2', @WhereParams='@p1=abc%,@p2=xyz'

In usp_select, I'll parse out the values in @WhereParams and then build and execute the sql like -

set @sql = N'declare @p1 nvarchar(10),
@p2 nvarchar(10);
set @p1 = ''' + @parsedValue1 + ''', @p2 = ''' + @parsedValue2 + '''; ' +

N'select col1 from table1 ' + @whereCondition

exec (@sql)

(basically @sql becomes - declare @p1 nvarchar(10), @p2 nvarchar(10);
set @pt = 'abc%', @p2 = 'xyz';
select col1 from table1 where col1 like @p1 and col2 = @p2)


Method 3 -

similar to Method 2 but exec(@sql) will be structured to become -
exec(declare @vparam nvarchar(100), @p1 nvarchar(10), @p2 nvarchar(10);

set @vparam='@p1 nvarchar(10), @p2 nvarchar(10)'

set @p1 = 'abc%', @p2 = 'xyz';

execute sp_executesql N''select col1 from table1 where col1 like @p1 and col2 = @p2', @vparam, @p, @p2)

When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure.
On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.

Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?

I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.

Any advice would be appreciated.

View 5 Replies View Related







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