Click here to Skip to main content
15,902,112 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hello Friends,
I'm trying executing dynamic query using
EXEC SP_EXECUTE


but it gives error saying:
Procedure expects parameter '@handle' of type 'int'.

Here is my procedure
ALTER proc [dbo].[sp_StudioReleaseChallan_sel]  
(
	@Criteria nvarchar(4000)='', 
	@startRowIndex  int=0,    
	@maximumRows  int=0  )  
as  
begin  
set nocount on;  
DECLARE @SQL NVARCHAR(MAX)
IF LEN(@Criteria) <> 0
BEGIN
	SET @Criteria = ' AND ' + @Criteria
END

SET @SQL = 'select A.iterationid [jobid], jobno,firstname+'' ''+lastname [User],clientname,projectname,brandname,awcategoryname,  
 ''Studio Master'' [JobType]  
,variantname,quantity+'' ''+unit [SKU],iterationdate [StartTime],expecteddeleverydate,statusname  
,artworktype,A.statusid from tbl_iteration A  
inner join tbl_user U on U.userid=A.userid  
inner join tbl_client C on C.clientid = A.clientid  
inner join tbl_project P on P.projectid = A.projectid  
inner join tbl_brand B on B.brandid = A.brandid  
inner join tbl_awcategory AW on AW.awcategoryid=A.artworkcategoryid  
inner join tbl_variant V on V.variantid = A.variantid  
inner join tbl_sku on tbl_sku.skuid=A.skuid  
inner join tbl_status S on S.statusid = A.statusid  
where artworktype = 9 and A.statusid not in (30,45,49) '+ @CRITERIA + ' 
order by jobno desc'  

EXEC SP_EXECUTE @SQL
end


Can anyone tell what this error means

Thanks in advance
Posted
Updated 11-Oct-19 3:14am
v2
Comments
Kiran Sonawane 3-Jun-11 7:55am    
Provide us procedure
This could help you
http://forums.asp.net/t/1454533.aspx/1
dhage.prashant01 3-Jun-11 7:58am    
I have added the Procedure in my question
dhage.prashant01 3-Jun-11 8:02am    
its working i used
sp_executesql instead of sp_execute

See here[^].

You should probably use:

exec sp_executesql MyProc
 
Share this answer
 
You can do this:

SQL
exec(@sql)


or this:

SQL
exec sp_executesql @sql
 
Share this answer
 
Comments
phil.o 11-Oct-19 9:24am    
Down-vote countered. 8 years later, finally :thumbsup:
Dear Prashant,

Please check the syntax of sp_executesql. following is the example to get row count from the table using dynamic query


DECLARE @SQLString nvarchar(500);
DECLARE @CountSQLQuery varchar(30);
DECLARE @CountResult varchar(30);

SET @SQLString = N'SELECT @result = COUNT(*) FROM TableName'
	
EXECUTE sp_executesql @SQLString, N'@result varchar(30) OUTPUT',@result=@CountSQLQuery OUTPUT;
 
Share this answer
 
Hello,

You need to user exec SP_EXECUTESQL @sql. Since you are tring to execute dynamic query. sp_executesql is inbuilt stored procedure provided my microsoft which accepts a query in string format and executes the same against the databse.



There are two different way of executing the dynamic query.

1) without using inbuild Store procedure


exec (@sql)


2) Using stored procedure

exec SP_EXECUTESQL @sql
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900