Sp_executesql And Speed

Jul 12, 2006

Hello,
 
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?

View 1 Replies


ADVERTISEMENT

Sp_executesql And Speed

Jul 12, 2006

Hello,

I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?

View 1 Replies View Related

¿What Improves SQL Server Performance? HD Speed, Processor Speed Or Ram?

Oct 18, 2007



Hi,

I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...

Thanks, Luis Luevano

View 1 Replies View Related

Exec Sp_executesql Vs. Sp_executesql And Performance

Jul 23, 2005

This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?

View 3 Replies View Related

Sp_executesql

Jul 31, 2006

I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL,  N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT',  @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.

View 2 Replies View Related

Sp_executeSql

Aug 8, 2005

What is wrong in this query..how can I make it to work


DECLARE @strSQL nVarchar(4000)
DECLARE @Name VArchar(100)

--SET @Name = '''sysdatabase'',''sysindexes'''

SET @Name = ''sysdatabase''
SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK) WHERE Name IN (@prmName)'

EXECUTE dbo.sp_executesql @strSQL,
N'@prmName varchar(100)',
@prmName= @Name


Note : I do not want to replace the query as

SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK)
WHERE Name IN ' + @Name + ')' , because my queryplan changes if I do this.

Any work around or anything you guys suggest ..

Thanks.

View 2 Replies View Related

Sp_executesql ¿qué?

Feb 27, 2008

I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.

Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.

I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.

Snippet that is not working:

declare @db_name varchar(80)

declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.

fetch

next

from

settings_cursor

into

@db_name



while

@@fetch_status = 0

begin

print 'CHECKING DBOPTIONS FOR ' + @db_name + ' - ( CHECKSUM, CREATE & UPDATE STATS, FULLRECOVERY)'



set @sql_command ='select'

set @sql_command = @sql_command + 'count(*)'

set @sql_command = @sql_command + 'from'

set @sql_command = @sql_command + 'sys.databases'

set @sql_command = @sql_command + 'where'

set @sql_command = @sql_command + 'name = ''@db_name'''

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'page_verify_option_desc = ''checksum'''

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_auto_create_stats_on = 1'

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_auto_update_stats_on =1'

set @sql_command = @sql_command + 'and'



-- select recovery model based upon database name.

if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

set @sql_command = @sql_command + 'recovery_model_desc = ''simple'''

end

else

begin

set @sql_command = @sql_command + 'recovery_model_desc = ''full'''

end



-- include db chaining for Master database

if @db_name = 'Master'

begin

set @sql_command = @sql_command + 'and'

set @sql_command = @sql_command + 'is_db_chaining_on = 1'

end



-- execute sql command.

--print @sql_command



declare @count int

execute @count = sp_executesql @sql_command, N'@db_name nvarchar(20)',@db_name=@db_name



if @count = 0-- no records were returned as the settings were wrong.

begin

select 'Issue with settings. altering now'



if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

alter database [@db_name] set recovery simple

alter database [@db_name] set page_verify checksum

end

else

begin

alter database [@db_name] set recovery full

alter database [@db_name] set page_verify checksum



end



if @db_name = 'msdb'

begin

alter database [@db_name] set db_chaining on

end



-- all databases get these switched on

alter database [@db_name] set auto_create_statistics on

alter database [@db_name] set auto_update_statistics on

end

else

begin

select 'all settings for ' + @db_name + ' are good'

end



fetch next from settings_cursor into @db_name

end



-- clean up

close settings_cursor

deallocate settings_cursor

View 3 Replies View Related

Sp_executesql

Jan 18, 2002

Hi
I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using
EXEC sp_execute sqlstmt.
Here is piece of code.

DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN c1
FETCH NEXT FROM C1 INTO @v_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @v_SQL= 'DROP TABLE ' + @v_TableName
--EXEC @v_SQL
PRINT @v_SQL
IF @v_Error<>0
BEGIN
SELECT @ErrorCount=@ErrorCount+1
PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName
--GOTO ErrorHandler
END
FETCH NEXT FROM C1 INTO @v_TableName
END
CLOSE c1
DEALLOCATE C1

Please let me know where I am doing wrong.

Thanks,
Rau

View 1 Replies View Related

Sp_executesql

Sep 3, 2004

hi everybody
How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server.
ie a string can contain insert into a table statement,delete a row from a table statement, update etc.
Thanks all of You

View 2 Replies View Related

Sp_executeSQL

Jan 7, 2004

Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.

I'm using sp_ExecuteSQL and have hit the 4000 character wall

View 1 Replies View Related

Sp_executesql

Apr 10, 2008

I have a string which I want to pass into an IF statement. Here's what I have:

declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''

IF cast(@sumclause as int)>0
BEGIN
...
END

I'm first putting it in a string because I'm passing another string into it which can't be seen above.

As you can see, I tried casting it since it had a problem with computing a string as an int, but it still doesn't work. Help?

View 9 Replies View Related

Sp_executesql

Jul 23, 2005

Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson

View 3 Replies View Related

Help With Sp_executesql

Jul 20, 2005

I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.

View 2 Replies View Related

Sp_executesql

Oct 2, 2006

Hi There

Is a table variable invalid for sp_executesql ?

I am trying the following:

SELECT @SQL = N'WAITFOR

(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '

+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'

EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out

I get the following message:

Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92

Must declare the scalar variable "@msgTable".

I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.

Can this be done?

Thanx

View 7 Replies View Related

Avoid Sp_executesql With ADO.NET

Jan 30, 2008

 In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.

View 1 Replies View Related

Question About Sp_executesql

Jul 18, 2005

Hi all,     I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN   FETCH NEXT FROM keyword_cursor into @1
  SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%'''
  SELECT @paramlist = '@x1 nvarchar(200)'  print @sql  EXEC sp_executesql @sql, @paramlist, @1
  RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN   FETCH NEXT FROM keyword_cursor into @1
  SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%'''
  SELECT @paramlist = '@x1 nvarchar(200)'  print @sql  EXEC sp_executesql @sql, @paramlist, @1
  RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?

View 2 Replies View Related

Problem With Sp_executesql

Jul 19, 2004

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @au_lname
',
N'@au_lname nVarChar',
@au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

Thanks

View 2 Replies View Related

Problem With Sp_executesql

Jul 19, 2004

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @au_lname
',
N'@au_lname nVarChar',
@au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

Thanks

View 2 Replies View Related

Sp_executesql Question

May 18, 2006

I have this code:
declare @a nvarchar(300), @camp nvarchar(15)

select @camp = camp from ref_activtot_paracnet where tip = 16011

set @a = 'select top 1 '+@camp+' from paracnet where datacalc = ''1/1/2005'''

exec sp_executesql @a

How can I store the value returned by the exec sp_executesql statement into a variable?

View 2 Replies View Related

Sp_executesql For Dummies

Jul 5, 2006

I have a stored procedure using dynamic SQL and I've been told I can no longer use 'exec (@strsql)' and have to use sp_executesql instead.

Fiiiiiiine...except I don't know how to use it and make it work with the parameter I'm passing in.

The original sproc that I have to change reads as:


CREATE PROCEDURE dbo.sp_LetsGetSomeData(@Filter nvarchar(200) = NULL)
AS

DECLARE @strSQL nvarchar(200)

IF @Filter IS NOT NULL
BEGIN
SET @strSQL = N'SELECT * FROM vwRandomViewName ' + @Filter
END

IF @Filter IS NULL
BEGIN
SET @strSQL = N'SELECT * FROM vwRandomViewName'
END

EXEC(@strSQL)


GO


And is called thusly:

EXEC sp_LetsGetSomeData @Filter = ' WHERE Team = ''RandomTeam'''

When I do this it never adds the filter. I tried to write a sproc like this:

CREATE PROC dbo.LetsGetSomeData2(@Filter nvarchar(200) = NULL)
AS

DECLARE @strSQL nvarchar(200)

SET @strSQL = N'SELECT * FROM vwRandomView'

EXECUTE sp_executesql @strSQL, @Filter
GO


and call it the same way as the original flavor sproc, it's as if I'm asking it to just select all from vwRandomView.

I know I'm doing something massively wrong but I'm just having a brain-dead day and can't make sense of the books online.

View 8 Replies View Related

Sp_executesql Help Needed

Jun 12, 2008

Hi,
I want to use the output of the sp_executesql to update a coulmn in the table.
example
-first i run the below to get output
execute sp_executesql @Query, @returnedCount output

-then I want to use that output to update another coulmn in the table
update tableName set coulmn=@returnedCount

I am new to this and cannot figure out how. Can someone please guide me?
thank you!!

View 1 Replies View Related

I Can't Use Sp_executesql Within Functions

Apr 9, 2007

Hi all
i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right?
Apparently this is true because for example create the following Function..

Create Function Test(@Input int)
Returns int
AS
Begin
Exec sp_who -- only for Test purpose
Return @Input
End

Now Execute this --> Select dbo.test(12).....
Sql Server will return the following Error

Server: Msg 557, Level 16, State 2, Procedure Test, Line 6
Only functions and extended stored procedures can be executed from within a function.

Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!

Any help greatly would be appreciated.
Kind Regards.

View 5 Replies View Related

How Can I Use Sp_executesql Make This.

Jan 31, 2008

I have this code:

USE BDPrincipal
GO

IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO

CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO

exec sp_executesql aquery;



I created that code to prove if it works.
But the result doesn´t appear, and the message is:

Command(s) completed successfully.

I need it to work.

Because I need to create a very dinamic query.

Please help me!

View 4 Replies View Related

Sp_executesql Question

Feb 13, 2006

I have not used this sp. We have a dynamic SQL statement generated by a sp.For performance reasons I would like to use it to reduce the number ofexplain plans created. I would like to understand its usage and pitfalls(if any) to its use. Any comments from the user community?

View 2 Replies View Related

Sp_executesql Vs. EXECUTE

Dec 20, 2006

please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?

View 3 Replies View Related

EXEC Sp_executesql

Dec 7, 2007

Hi, I just had a quick question about this code and if I know what it is doing: Is it putting what is in variable @x18 into @Lost_Alumni?

Declare @Lost_Alumin datetime


Set @ParmDef_Ls = '@x18 DateTime OUTPUT, '


EXEC sp_executeSQL @ParmDef_Ls,
@x18 = @Lost_Alumni OUTPUT,



thanks,

View 5 Replies View Related

Sp_executesql With Parameters

Feb 18, 2008



I'm trying to build a stored procedure with parameters and sp_executesql. I can't seem to get the types correct. I have two parameters I want to pass: @ADDIVNumber which will be a bigint and @Where which can be varchar(500). I can't seem to figure out how to get the varible types right.


ALTER PROCEDURE [dbo].[AMTRANHDRPaidTranHistAP]

@CharVariable varchar(500),

@IntVariable bigint

as

Declare

@SQLHolder nvarchar(4000)

set @SQLHolder = 'SELECT T1.SMBNKNumber, T1.AMACTNumber, T1.AMALTNumber,

POORDERHDR.POORHNumber, T1.AMTRHNumber, T1.AMTRHType, T1.AMTRHSubType,

T1.AMTRHCode, T1.AMTRHDate, T1.AMTRHAmt, T1.AMTRHDueDate,

T1.AMTRHDiscAllowed, T1.AMTRHDiscDate, T1.SMBCHNumber, T1.AMTRHMasterCode,

T3.SMBCHStatus, T2.AMALTCode, T2.AMALTName, T1.AMTRHAmt - isnull(TotalBalance.BalDue,0) as BalDue, isnull(TotalBalance.PaidAmt,0) as PaidAmt,

isnull(TotalBalance.DiscTaken,0) as DiscTaken, isnull(TotalBalance.AdjTaken,0) as AdjTaken,

T1.AMTRHRecvShip, CASE WHEN T1.AMTRHStatus = ''P'' THEN ''Paid'' WHEN T1.AMTRHStatus = '' '' THEN ''Open''

WHEN T1.AMTRHStatus = ''S'' THEN ''Select'' WHEN T1.AMTRHStatus = ''H'' THEN ''Hold'' end as AMTRHStatus,

T1.POORRNumber, POORDERREL.POORDNumber, POORDERDTL.POORHNumber, POORDERHDR.POORHCode, POORDERHDR.POORHCode + ''-''

+ CAST(POORDERDTL.POORDSeq as Varchar) + ''-'' + CAST(POORDERREL.POORRSeq as Varchar) AS ReleaseNumber, TotalBalance.LastPaymentDate,

TotalBalance.PaymentCount

FROM

(

SELECT AMTRANHDR.SMBNKNumber, AMTRANHDR.AMACTNumber, AMTRANHDR.AMALTNumber, AMTRANHDR.AMTRHStatus,

AMTRANHDR.AMTRHNumber, AMTRANHDR.AMTRHType, AMTRANHDR.AMTRHSubType,

AMTRANHDR.AMTRHCode, AMTRANHDR.AMTRHDate, AMTRANHDR.AMTRHAmt, AMTRANHDR.AMTRHDueDate,

AMTRANHDR.AMTRHDiscAllowed, AMTRANHDR.AMTRHDiscDate, AMTRANHDR.SMBCHNumber, AMTRANHDR.AMTRHMasterCode,

AMTRANHDR.AMTRHRecvShip, AMTRANHDR.POORRNumber

FROM AMTRANHDR

WHERE AMTRANHDR.AMTRHDeletedOn is null and AMTRANHDR.ADDIVNumber = @ADDIVNumber and @Where

) AS T1

LEFT JOIN

(

SELECT AMALTERNATE.AMALTCode, AMALTERNATE.AMALTName, AMALTERNATE.AMALTNumber

FROM AMALTERNATE

) AS T2 ON T1.AMALTNumber = T2.AMALTNumber

LEFT JOIN

(

SELECT SMCODEBCH.SMBCHStatus, SMCODEBCH.SMBCHNumber

FROM SMCODEBCH

) AS T3 ON T1.SMBCHNumber = T3.SMBCHNumber

LEFT OUTER JOIN [dbo].POORDERREL ON T1.POORRNumber = POORDERREL.POORRNumber

LEFT OUTER JOIN [dbo].POORDERDTL ON POORDERREL.POORDNumber = POORDERDTL.POORDNumber

LEFT OUTER JOIN [dbo].POORDERHDR ON POORDERDTL.POORHNumber = POORDERHDR.POORHNumber

LEFT OUTER JOIN

(

SELECT AMPMTCROSS.AMPMCItem,

sum(isnull(AMPMTCROSS.AMPMCAmount,0) +

isnull(AMPMTCROSS.AMPMCDiscount,0) + isnull(AMPMTCROSS.AMPMCAdjust,0)) as BalDue,

sum(isnull(AMPMTCROSS.AMPMCAmount,0)) as PaidAmt,

Count(0) AS PaymentCount, MAX(AMTRHDate) AS LastPaymentDate,

sum(isnull(AMPMTCROSS.AMPMCDiscount,0)) as DiscTaken,

sum(isnull(AMPMTCROSS.AMPMCAdjust,0)) as AdjTaken

FROM [dbo].AMPMTCROSS

INNER JOIN [dbo].AMTRANHDR ON AMPMTCROSS.AMPMCCheck = AMTRANHDR.AMTRHNumber

GROUP BY AMPMTCROSS.AMPMCItem

) AS TotalBalance ON T1.AMTRHNumber = TotalBalance.AMPMCItem'

SET @CharVariable = N'@Where, varchar(500)'

SET @IntVariable = N'@ADDIVNumber, bigint'

exec sp_executesql @SQLHolder, @IntVariable, @CharVariable



View 5 Replies View Related

Question About Sp_executesql

May 28, 2008

I'm not very familiar with Dynamic SQL, so you may find this question dumb. Sorry if this is the case ;-)
I've been reading Raul Garcia's blog about SQL injection and I would like to be able to do something like this:

DECLARE @tab varchar(50)
SET @tab = 'aTableName'
DECLARE @sql nvarchar(200)
DECLARE @param_def nvarchar(100)
DECLARE @p_tab varchar(50)

SET @sql = N'SELECT * FROM @p_tab;'
SET @param_def = N'@p_tab varchar(50)'
EXECUTE sp_executesql @sql, @param_def, @p_tab=@tab;


instead of

SET @sql = N'SELECT * FROM' + @tab

directly.
But this does not work in a Stored Proc. with SQL Server 2005. My question is : how to do it ?

Thanks for reading me.

View 4 Replies View Related

Sp_executesql Performance

Sep 20, 2006

Hi All,

The following is cut from SQL profiler, database is SQL 2000 SP4. Query 1 takes almost zero time and 26 reads. Query 2 takes 16 millsecs and 2862 reads and the only difference is that Query2 has parameters. I have run the query's multiple times and in different order and the results are the same. My reading of the documentation says that Query 2 should be faster due to not having to recreate the execution plan. If the execution plan is a bad one and is cached how do I remove it. Is there anyway to force a recompile or am I know in stored procedure territory.

Can anyone give me an explanation as it looks like we should be changing our code to use literal's where ever possible rather than parameter substitution.

TIA

SQL:BatchCompleted exec sp_executesql N'select *

from PA_REC_RECEIPT_ALLOCATIONS

where RECEIPT_PREFIX = ''LMH''

and RECEIPT_SUFFIX = 10652

' Microsoft SQL Server Management Studio - Query 0 26 0 0 3256 51 2006-09-20 13:15:32.843

-- Query 2

SQL:BatchCompleted exec sp_executesql N'select *

from PA_REC_RECEIPT_ALLOCATIONS

where RECEIPT_PREFIX = @P1

and RECEIPT_SUFFIX = @P2

', N'@P1 varchar(3),@P2 int', 'LMH', 10652

Microsoft SQL Server Management Studio - Query 16 2862 0 13 3256 51 2006-09-20 13:15:35.830

View 5 Replies View Related

What Exactly Can Sp_executesql Parametrize?

Jun 15, 2006

I am attempting to create dynamic SQL with sp_executesql.

However, it seems that parameters can't be created arbitrarily in the query string. In particular, the name of the table apparently can't be parametrized, or I am doing something wrong.

I use SQL Server 2000 with the latest service pack.

The table task has a few columns of which TaskID is Primary Key and INT.


This works fine:

DECLARE @pot nvarchar(200)
SET @pot = 1
DECLARE @pot2 nvarchar(200)
SET @pot2 = 'task'
EXEC sp_executesql N'SELECT * FROM task WHERE TaskID = @pot', N'@pot varchar(128), @pot2 varchar(128)', @pot, @pot2


However, this does not work:

DECLARE @pot nvarchar(200)
SET @pot = 1
DECLARE @pot2 nvarchar(200)
SET @pot2 = 'task'
EXEC sp_executesql N'SELECT * FROM @pot2 WHERE TaskID = @pot', N'@pot varchar(128), @pot2 varchar(128)', @pot, @pot2

Note that the only change in the second case here is to pass the same table name as a parameter.

The error message is: Must declare the variable '@pot2'.
However, @pot2 is clearly declared already!


1. Is there any way to parametrize the table name as above?
2. Is there any general guideline on what in the query can be parametrized?

Note: I know that I could parametrize the table name by concatenating the first parameter of sp_executesql but that would assume my question (1) was answered 'no' already.

Thankful for any advice.

View 6 Replies View Related

Tracing The Sp_executesql

Oct 17, 2006

Hi,

I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.

SET @SQLString = N'SELECT Top(1) @Passenger_OUT = Name, @Ticket_OUT = TicketNumber , @TAN_OUT= [TAN] '

+ N'FROM dbo.Table1 WHERE 1 = 1 '

+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End

+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End

+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End

+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;

SET @ParmDefinition = N'@BillingAmount decimal, @TicketNumber varchar(15), @RemoveRight tinyint, @RemoveLeft tinyint, @BillingDate datetime, @DaysDiff tinyint, @CharsToMatch tinyint, @PassengerName varchar(35), @Passenger_OUT varchar(35) OUTPUT, @Ticket_OUT varchar(15) OUTPUT, @TAN_OUT varchar(25) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition,@BillingAmount=@BillingAmount, @TicketNumber=@TicketNumber, @RemoveRight=@RemoveRight, @RemoveLeft=@RemoveLeft, @BillingDate=@BillingDate, @DaysDiff=@DaysDiff , @CharsToMatch=@CharsToMatch, @PassengerName=@PassengerName, @Passenger_OUT=@Passenger_new OUTPUT, @Ticket_OUT=@Ticket_new OUTPUT, @TAN_OUT=@TAN_new OUTPUT;

Print @SQLString will gives the statement with variables and I need to see the actual data plugged in during runtime.

If I take the ouput of @SQLString and run it myself by adding variables it works fine

Thanks

Shafiq

View 2 Replies View Related

Using Sp_executesql In Cursor

Aug 2, 2007

hi,

I try to use a sql statmenttext as varaible in cursor function with sp_executesql or sp_execsql.

But these Stored Procedures can't use in cursor function.


Q : How can I use a sql statmenttext in corsur funtion as using sp_executesql or sp_execsql ?

Sample Code :


declare @sql nvarchar(500)

declare @table nvarchar(50)

declare @Field1 nvarchar(50)

declare @Field2 nvarchar(50)

declare @FieldValue nvarchar(50)

set @table = 'dbo.kisi_bilgileri'

set @Field1 = 'TcKimlikNo'

set @Field2 = 'KisiNo1'

set @Fieldvalue = '9000050715'

set @sql = 'select ' +@Field1+ ', ' + @Field2+ ' from ' +@table + ' where ' +@Field1+ ' = ' +@Fieldvalue



declare TabloOku cursor for


execute sp_sqlexec @sql -- isn't work, what can I use instead of ?



open TabloOku

fetch next from TabloOku into @Tablo

WHILE @@FETCH_STATUS = 0

begin ...

View 3 Replies View Related

USE [dbname] In Sp_executesql

Sep 14, 2007

I am trying to get file space usage information for all of the databases in my server and store it in a table to collect a history of spce usage. I know that I can get this on a per database basis if I use DBCC(SHOWFILESTATS). So, I would issue a USE statement and then a DBCC statement inserting the rows into a temp table for later processing. Since databases can come and go, I would like to loop through the records in sysdatabases and for each one, issue the USE and then the DBCC via an sp_executesql.
This is all working fine except, the sp_executesql of the USE statement seems to be ignored.

Is there some way to get this statement to take effect?
Is there a better way to get this information - ie is this stored in a table or view that I can't seem to find?

Thanks in advance for your help.
Bob

View 4 Replies View Related







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