sp_execute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_execute

Author

Topic

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-25 : 04:06:01

What is the problem in below given sp_execute statement?
For the same Exec() statement is working.

--surtest1 table having (da int, name varchar(50)
----- sp_execute example
Declare @str2 varchar(4000)
Select @str2 = 'select top 2 companycontactid as da, firstname as name from companycontact'
Exec sp_execute N'Insert into surtest1 (da,name) @str2 ', '@str2 varchar(4000)', @str2
--It throws error " Procedure expects parameter '@handle' of type 'int'. "

----- exec() example
Declare @str2 varchar(4000)
Select @str2 = 'select top 2 companycontactid as da, firstname as name from companycontact'
exec ('Insert into surtest1 (da,name)' + @str2 )
--This is working

Surendra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-25 : 04:32:49

you can't use parameter in sp_executesql this way.

Refer to the Book Online for exact usage.

-----------------
[KH]

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-25 : 05:58:17

so how to convert the exac() statement (exec()example) into sp_execute?
@str2 is required to store the dynamic sql.

Surendra

Kristen
Test

22859 Posts

Posted - 2005-11-25 : 08:42:03

Why would you want to? There is no parameterization of the query available ...

When you do

SELECT * FROM MyTable WHERE MyColumn = @MyParameterisedValue

and the value in @MyParameterisedValue will be different each time, THAT'S when using sp_ExecuteSQL to allow parameterization of the query will help.

For your example it would be more akin to doing:

Insert into surtest1 (da,name)
select top 2 companycontactid as da, firstname as name
from companycontact
WHERE MyColumn = @MyParameterisedValue

If you want to "fiddle" with the SELECT bit then do that with string handling first - the trick is to present queries that are repeated - hence the parameterization because that enables two queries, actually for different values of "MyColumn", to be presented identically and thus re-used the cached query plan

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources