Sp_executeSQL Syntax Question
Jul 20, 2005Does anyone know the technical reason for why sp_executesql uses the N
prefix before the string that is passed?
For example:
sp_executesql N'USE Northwind'
Thanks!
ADVERTISEMENT
Problems With Sp_executesql - Syntax Errors
Jun 7, 2005I tried doing a search on the forums, but it kept giving me the message: XXX is used too often and is excluded from your search
Anyway, here is the code:
Code:
DECLARE @Test nVarchar(10)
DECLARE @Test1 nVarchar(40)
SET @Test = N'''1'',''3'',''4'''
SET @Test1 = N'select * from table1 where column1 in (' + @Test + ')'
EXEC sp_executesql @Test1
If I put it in a "Begin Transaction" block
there is a syntax error near the ( in the set @Test1 statement.
If I leave it out of a "Begin Transaction" Block,
The syntax error is:
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.
'Syntax Error Converting Datetime From Character String' With Sp_executesql
Jul 20, 2005CREATE PROCEDURE dbo.Synchronization_GetNewRecords(@item varchar(50),@last datetime)ASSET NOCOUNT ONDECLARE @sql nvarchar(4000)SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @lastEXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @lastThis is my SP. Very simple. But it is throwing the error in the subject line.Any help would be greatly appreciated.
View 1 Replies View RelatedExec Sp_executesql Vs. Sp_executesql And Performance
Jul 23, 2005This 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 RelatedIncorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?
May 20, 2008Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
Sp_executesql
Jul 31, 2006I 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 RelatedSp_executeSql
Aug 8, 2005What 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.
Sp_executesql ¿qué?
Feb 27, 2008I'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
Sp_executesql
Jan 18, 2002Hi
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
Sp_executesql
Sep 3, 2004hi 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
Sp_executeSQL
Jan 7, 2004Is 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
Sp_executesql
Apr 10, 2008I 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?
Sp_executesql
Jul 23, 2005Hi 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 RelatedHelp With Sp_executesql
Jul 20, 2005I 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 RelatedSp_executesql
Oct 2, 2006Hi 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
Incorrect Syntax When There Appears To Be No Syntax Errors.
Dec 14, 2003I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?
Mar 31, 2008Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Converting Rrom Access Syntax To Sql Syntax
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
Sp_executesql And Speed
Jul 12, 2006Hello,
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?
Avoid Sp_executesql With ADO.NET
Jan 30, 2008In 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 RelatedQuestion About Sp_executesql
Jul 18, 2005Hi 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?
Problem With Sp_executesql
Jul 19, 2004I 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
Problem With Sp_executesql
Jul 19, 2004I 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
Sp_executesql Question
May 18, 2006I 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?
Sp_executesql For Dummies
Jul 5, 2006I 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.
Sp_executesql Help Needed
Jun 12, 2008Hi,
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!!
I Can't Use Sp_executesql Within Functions
Apr 9, 2007Hi 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.
How Can I Use Sp_executesql Make This.
Jan 31, 2008I 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!
Sp_executesql Question
Feb 13, 2006I 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 RelatedSp_executesql Vs. EXECUTE
Dec 20, 2006please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?
View 3 Replies View RelatedEXEC Sp_executesql
Dec 7, 2007Hi, 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,
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
Question About Sp_executesql
May 28, 2008I'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.